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

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.

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;

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