Thursday, August 11, 2005

TOP for INSERT, UPDATE, DELETE Queries?

From the June CTP SQL Server 2005 Books Online:

The rows referenced in the TOP expression used with INSERT, UPDATE, or DELETE are not arranged in any order. TOP n returns n random rows.

Random Rows: WTF does that mean?

So if I do this:

DELETE TOP (5) FROM Table

The rows deleted are any 5 arbitrary rows that it finds? They aren't necessarily the same 5 rows that I can check using this:

SELECT TOP (5) * FROM Table

I'm awaiting some clarification from anyone who replies (to this blog or my email): What good does it do to have TOP for I/U/D queries if the behavior is considered random???


UPDATE:

Drew Robbins (Microsoft DE for Ohio) replied with this:

"John Miller and Luis Gonzalez from MS gave me some great feedback. Here are two scenarios where TOP would make sense:

  • batching: one in which you don’t care about which rows qualify, you just want to break one long transaction into smaller ones
  • The extension is particularly useful when combined with the OUTPUT clause for queuing scenarios"

Pretty much what Aaron and KT came up with (see comments), so nothing new.


UPDATE 2: Click Here to see this blog post for results of my exploring the behavior of UPDATE TOP(x).