Is it safe to use Select Top and Delete Top in sequence?
- by Rob Nicholson
I often write T-SQL loops that look like this
While Exists (Select * From #MyTable)
Begin
Declare @ID int, @Word nvarchar(max)
Select Top 1 @ID=ID, @Word=[Word] From #MyTable
-- Do something --
Delete #MyTable Where ID=@ID
End
Works a treat but I noticed the new Delete Top function which would be useful when #MyTable is just a list of strings. In this case, would this work:
While Exists (Select * From #MyTable)
Begin
Declare @Word nvarchar(max)
Select Top 1 @Word=[Word] From #MyTable
-- Do something --
Delete Top(1) #MyTable
End
Well yes, it works in my test script but is this safe? Will Select Top 1 and Delete Top(1) always refer to the same record or is Top a little more vague.
Thanks, Rob.
