Hi Geeks,

I hope you liked Part1 , Part2Part3  and Part4 of SQL Server Cursor Operators. Today, we are going to see physical operators for a STATIC CURSOR.

Let me use the same query from yesterday.

StaticCursorActualPlan

From above figure, we can see that there are two different plans. The first plan loads data into the work table as can be seen in Clustered index and the second plan is repeated. Further to that, we see series of plans same as to the one shown for Query 2 in the above figure which shows how the cursor is looped using WHILE statement.

CURSOR was loaded when we called up OPEN CURSOR statement and when we look at the ToolTip text of the Clustered Index Seek operator, we can clearly see that a row identifier (highlighted in following figure) is created while populating the cursor.

CISeekRowIDToolTip

And we are going to continue more on cursor operators 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