SQL Server Cursor Operators – Part5

Hi Geeks,

I hope you liked Part1 , Part2 , Part3  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.

-- 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.

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