This is a simplification of a real cursor query:
DECLARE @T table (a int NOT NULL INDEX ia UNIQUE); INSERT @T (a) VALUES (1), (5), (25), (75); DECLARE c CURSOR LOCAL SCROLL DYNAMIC OPTIMISTIC TYPE_WARNING FOR SELECT T.a FROM @T AS T ORDER BY T.a ASC FOR UPDATE OF a; OPEN c; FETCH RELATIVE 2 FROM c; CLOSE c; DEALLOCATE c;
The execution plan for the
FETCH shows an RID Lookup:
I am having trouble understanding how this plan produces the result specified by the statement:
FETCH RELATIVE 2 FROM c;
I am most puzzled by the RID Lookup because the table has only one column, and that is returned by the Index Scan.
A detailed explanation of how the plan works would be most appreciated.