T-SQL - what happens if am looping through cursor and at the same time an insert into the table happens?

Question

Consider this. I have a table called dbo.Event.

I declare a cursor for it:

declare myCur cursor for 
    select a from dbo.event

Then I:

open myCur
fetch next from myCur into @temp
while @@fetschstatus = 0
    ...
    do the job using fetched value
    ...
fetch next from myCur into @temp
end

The question is - myCur needs some time to loop through all available values which we had in table at the moment when cursor fired. What happens when cursor is till running BUT the insert happens into dbo.Event. Will already running cursor pick up this new inserted value and iterate over it too? Or by the end of the cursor execution newly inserted values won't be processed by cursor?

Answer 1

I a pretty certain that the default option for a cursor is DYNAMIC therefore modifications to the base data are reflected in the cursor. If you want specific behaviour, explicitly state it rather than relying on default behaviour, e.g.

DECLARE A CURSOR LOCAL STATIC FAST_FORWARD 
FOR... 

You can check the properties after declaration using:

SELECT properties 
FROM sys.dm_exec_cursors(@@spid);

It sounds like you want a snapshot of the data when the cursor opened so you should be using a static cursor.