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.


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!



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