TSQL not causing infinite loop

Question

Please see the code below:

declare @crimeurn varchar(20)
DECLARE @finalresults TABLE (crime_urn varchar(20))
DECLARE @potentialresults TABLE (crime_urn varchar(20))

insert into @finalresults values ('1')    

DECLARE finalresults_cursor CURSOR FOR
SELECT crime_urn FROM @finalresults
OPEN finalresults_cursor 
FETCH NEXT FROM finalresults_cursor INTO @crimeurn

WHILE @@FETCH_STATUS = 0 
BEGIN 
    print @crimeurn  

    INSERT INTO @finalresults
    values ('2')

    FETCH NEXT FROM finalresults_cursor INTO @crimeurn
END 

select * from @finalresults --line 16

CLOSE finalresults_cursor 
DEALLOCATE finalresults_cursor 

Line 16 displays 5137 or 12,342 rows in SQL studio manager (it randomly varies). I expected the TSQL to cause an infinite loop because there is an insert into the table variable on every iteration of the cursor.

Why does it not cause an infinite loop? i.e. why are there 5,137 or 12,342 rows returned.

Answer 1

You are inserting into a heap.

A heap is unordered. There is no particular guarantee that the row will be inserted after the current row and picked up on the next fetch.

I made a slight amend to your test framework and added an IDENTITY column. In my case it got to row 592,353 before terminating.

As you can see from the results below this final row happened to be inserted on an earlier page in the file (jumped from 1623 to 184) so an allocation ordered scan starting from the penultimate row wouldn't find it.

Code to reproduce.

declare @crimeurn varchar(20)
DECLARE @finalresults TABLE (crime_urn varchar(20), ID int identity)
DECLARE @potentialresults TABLE (crime_urn varchar(20))

insert into @finalresults values ('1')    

DECLARE finalresults_cursor CURSOR FOR
SELECT crime_urn FROM @finalresults
OPEN finalresults_cursor 
FETCH NEXT FROM finalresults_cursor INTO @crimeurn

WHILE @@FETCH_STATUS = 0 
BEGIN 
    print @crimeurn  

    INSERT INTO @finalresults
    --OUTPUT INSERTED.ID
    values ('2')

    FETCH NEXT FROM finalresults_cursor INTO @crimeurn
END 

select *, sys.fn_PhysLocFormatter(%%physloc%%) from @finalresults --line 16
ORDER BY ID

CLOSE finalresults_cursor 
DEALLOCATE finalresults_cursor