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

Question

I often write T-SQL loops that look like this in upgrade scripts:

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.

Answer 1

If you often write loops either you are doing something wrong or your database needs a redesign or you are inserting large batches of data that need to run in batches of 1000 records or so. I'll bet almost everything you are doing could be done in a set-based fashion and improve performance tremendously. I can't show you how without knowing what kind of thing happens inteh do somthing, but likely it can be made set-based by using a case startement.

Answer 2

No, your second example is not safe.

If you are going to use "SELECT TOP ..." (and you care about the order in which the rows are selected) you should really use an "ORDER BY" to explicitly specify the order. You MIGHT get the order you want without "ORDER BY", but SQL Server makes no guarantees about the order of returned rows when ORDER BY is not used.

In the case of "DELETE TOP ..", you can't add an "ORDER BY", so you should use a WHERE clause which uniquely identifies the row(s) to be deleted, as you have done in your first example.