Is it safe to use Select Top and Delete Top in sequence?

Posted by Rob Nicholson on Stack Overflow See other posts from Stack Overflow or by Rob Nicholson
Published on 2010-04-06T21:32:53Z Indexed on 2010/04/07 0:33 UTC
Read the original article Hit count: 440

Filed under:

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.

© Stack Overflow or respective owner

Related posts about tsql