SQL Server Cursor Operators – Part7

Hi Geeks,

Yesterday on Part6 of SQL Server Cursor Operators we saw how estimated execution plan changes when we change CURSOR type to KEYSET and today we are going to explore its physical representation. I recommend you to read Part1, Part2, Part3, Part4Part5 and Part6 before today’s post.

-- Database used in example is [AdventureWorks2012]

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

KeysetPhysicalOpExPlan

   

Query 1 section (highlighted in RED) on above figure contains OPEN CURSOR operator and quite similar one that we covered in earlier posts. In Query 2 (highlighted in BLUE) FEXT NEXT statement in the CURSOR activates FETCH CURSOR operation number of times depends on the number of rows to be returned.

In KEYSET cursor, the CURSOR queries the data twice, once to load the key set and a second time to retrieve the row and this may hurt performance. As it depends on the number of rows being retrieved into a work table, this could be a costly operation. Compared to a DYNAMIC cursor this could less costly but it is expensive operation than a STATIC cursor.

Watch out this space for more, 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

   

About Kanchan Bhattacharyya

Kanchan is an astute IT professional, a seasoned SQL Database Administrator with 13+ years of industry experience. A calculated risk taker with deep technical knowledge and has gained proficiency in database consulting and solutions across different environments. Kanchan holds MBA degree in IT and an International Executive MBA in Project Management. He is very passionate about SQL Server and holds MCSE (Data Platform), MCSA – SQL 2012, MCITP – SQL 2008 certifications. Currently he is focusing on cloud and latest releases of SQL Server. When not working, Kanchan likes to spend his time reading on new technical developments specifically on SQL Server and other related technologies.

View all posts by Kanchan Bhattacharyya →

Leave a Reply

Your email address will not be published.