SQL Server Cursor Operators – Part8

Hi Geeks,

Today we will have a look into execution plan for READ_ONLY cursor. You can read my previous posts on SQL Server Cursor Operators by clicking Part1, Part2, Part3, Part4, Part5, Part6 and Part 7 to read them before today’s post.

When we define a CURSOR as READ_ONLY and click on ‘Display Estimated Execution Plan’ icon, we get similar execution plan as shown in following figure based on nature of operations.

-- Database used in example is [AdventureWorks2012]
-- Please click on Display Estimated Execution Plan icon

DECLARE TerritoryName CURSOR READ_ONLY 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

ReadOnlyCursorPlan

We cannot capture the data changes for READ_ONLY cursors. This plan is simple as compared to all other plans we have been seeing for last few days. There are no branch operations and it simply reads the data it needs. In our example, Clustered Index Scan operator reads data it needs.

CIScanToolTip

The number of I/O compared to less as there is no need to populate work tables and single step does the trick. When we execute the query, actual execution plan looks like following;

ReadOnlyCurActualPlan

Difference is we have Fetch Cursor operator except Fast Forward that was displayed while estimated plan and data is returned by only Index scan operator.

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

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 *