Let me use the same query from yesterday.
-- Database used in example is [AdventureWorks2012] DECLARE TerritoryName CURSOR STATIC 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
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.
And we are going to continue more on cursor operators tomorrow.