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, Part4, Part5 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
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
