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).
|