Keyset Cursor Execution Plan
-
I've got a very simple little script for demonstrating basic cursor behavior. In this case, it's doing a keyset cursor. I can account for all the operations in the logical definition except for the second Compute Scalar operation (going from right to left).
Anyone have a clue?
Here's the query:
DECLARE CurrencyList CURSOR KEYSET FOR
SELECT CurrencyCode FROM [Sales].[Currency]
WHERE Name LIKE '%Dollar%'
OPEN CurrencyList
FETCH NEXT FROM CurrencyList
WHILE @@FETCH_STATUS = 0
BEGIN
-- Normally there would be operations here using data from cursor
FETCH NEXT FROM CurrencyList
END
CLOSE CurrencyList
DEALLOCATE CurrencyList
GO
Attached is the estimated plan.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
It looks like it's generating a scalar to serve as a row check for the INSERT into TempDB. If you follow the output of each step, you'll see that generates a column output which gets used in the Clustered Index Insert. For my Adventureworks, that column corresponds to a ROWSTATUS column.
K. Brian Kelley
@kbriankelley -
Great. Thanks for the reply.
I should have looked at the INSERT statement. Missed it. That explains things nicely.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply