Understand While Loop in SQL

Transact-SQL
1

HI Everyone,

I am just wondering or try to understand how this wile loop is working...

here is my while loop

While (@Var >0)
Begin
Begin Transaction Trans
Insert into #Temp1 (Id,sname,fname)
select top (500)
id,
sname,
fname
from table1

Delete table1
from table1
join table1.id = #Temp1.id

Truncate Table #Temp1
Commit Transaction Trans

End

My question to you guys or Please explain me, How the While Loop know everytime top (500) would be different? or it is not grabing same toop (500) rows everytime?

Please advise.

Thank You.

2

Since you do not have an ORDER BY clause, the results returned by TOP (500) may not be in any particular order. SQL is free to return whichever 500 it thinks will result in the lowest execution time/io/storage use etc. If you see them in a particular order, you're just lucky and that result will not be (guaranteed) repeatable

3

Thanks gbritton thank you for your prompt reply. How about if I change my syntax to

select top (500)
id,
sname,
fname
from table1
order by id
it will guarantee I will get different records every time?

4

The code in the while loop breaks down into three activities:

  1. Grab 500 records (The original did this pseudo-randomly) and place them in a temp table
  2. Delete all records that match the ones just placed in the temp table
  3. Empty the temp table

The overall effect is that your Table1 data should be deleted in blocks of 500.

What is conspicuously absent is any update to the value of @Var. The overall effect of this is that you have an infinite loop. You probably want to add "set @Var = @@rowcount;" after the DELETE or something to that effect.

5 
Declare @Var int

Set @Var = 1

While (@Var >0)
Begin
Begin Transaction
Insert into #Temp1 (Id)
select top (500) id
from table1

Delete table1
from table1
inner join #Temp1 temp1 on table1.id = temp1.id

Set @Var = @@ROWCOUNT

Truncate Table #Temp1
Commit Transaction
End --While
6

To answer the original question, the TOP 500 will always be different (in this case) because you've deleted the 500 rows you've just selected.