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.