SQL Server Cursor Operators – Part6

Hi Geeks,

Today we are going to discuss about KEYSET cursor and if you missed out earlier posts on SQL Server Cursor operators, you can click Part1, Part2, Part3, Part4 and Part5 to read them before today’s post.

KEYSET cursor fetches a defined set of  keys as data defined within the cursor however allows the data may be updated during the cursor lifetime. We have a different execution plan for KEYSET cursor than DYNAMIC and STATIC cursors discussed earlier. Let me change the CURSOR definition first.

-- Database used in example is [AdventureWorks2012]
-- Please click on Display Estimated Execution Plan icon

DECLARE TerritoryName CURSOR KEYSET FOR

SELECT SalesTerritory.Name FROM [Sales].[SalesTerritory]
WHERE SalesTerritory.[Group] LIKE '%America%'

OPEN TerritoryName
FETCH NEXT FROM TerritoryName

WHILE @@FETCH_STATUS = 0 
BEGIN
	FETCH NEXT FROM TerritoryName
END

CLOSE TerritoryName
DEALLOCATE TerritoryName

The estimated plan looks like above figure. The top most line (highlighted in RED) in the query plan containing Population Query looks similar to STATIC cursor. Compute Scalar operation (read Part1, Part2) added to check status for the row. It ends with Keyset Cursor operator which guarantees the cursor can see updates but not insert operations.

When we look at the right and top of the FETCH query definition (highlighted in BLUE), we could see that first it retrieves the key from created index in Population Query then to retrieve data it joins it with the help of Nested Loop operation (read Part1, Part2)  to SalesTerriroty table. This is how a KEYSET cursor manages to get updated data into the set returned when cursor is active.

The Constant Scan operator scans internal table containing constants. This feeds data into Clustered Index Update operator in order to change the data stored in case required. This is again joined with first set with the help of a Nested Loop operation and finishes with Compute Scalar.

We are not yet done and going to continue more tomorrow.

Happy Learning.

Regards,

Kanchan

Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook | Follow me on Twitter | Follow me on FaceBook