SQL Server Cursor Operators – Part5

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.

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

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

Data Platform Virtual Summit 2020

Subscribe to SQLServerGeeks YouTube channel. If you want more learning content in your inbox, subscribe to SQLServerGeeks Bulletin.

SQLServerGeeks YouTube | SQLServerGeeks Bulletin | SQLServerGeeks Twitter

Avatar

About Kanchan Bhattacharyya

Kanchan is an astute IT professional, a seasoned SQL Database Administrator with 13+ years of industry experience. A calculated risk taker with deep technical knowledge and has gained proficiency in database consulting and solutions across different environments. Kanchan holds MBA degree in IT and an International Executive MBA in Project Management. He is very passionate about SQL Server and holds MCSE (Data Platform), MCSA – SQL 2012, MCITP – SQL 2008 certifications. Currently he is focusing on cloud and latest releases of SQL Server. When not working, Kanchan likes to spend his time reading on new technical developments specifically on SQL Server and other related technologies.

View all posts by Kanchan Bhattacharyya →

Leave a Reply

Your email address will not be published. Required fields are marked *