SQLkiwi (6/26/2011)
One can delete 9 rows non-deterministically with this code:
DELETE TOP (9) @tThough in practice you're likely to see the same 9 rows deleted, it's just not guaranteed. To go further and delete 9 rows at random reliably:
DELETE ToDelete
FROM
(
SELECT TOP (9) *
FROM @t AS t
ORDER BY
NEWID()
) AS ToDelete;
Now I know that wasn't really the point of the question, but I wanted to get it out of the way.
The broader question is whether the optimizer should be free to reorder expressions and, in particular, to evaluate those expressions more than once, even when that expression is non-deterministic. On balance, I think I prefer better query plans for a broad class of queries over the alternative.
And the same thing re-written using a CTE - just to show an alternate way to format the code:
;WITH ToDelete AS
(
SELECT TOP (9) *
FROM @t AS t
ORDER BY
NEWID()
)
DELETE ToDelete
OUTPUT deleted.*
Why did I bother posting this?.... well, I like this construct for updates mostly as the visual seperation of the SELECT from the UPDATE or the DELETE makes it entirely clear what you are doing - Here is the set of data I am interested in....now here is what I am going to do to it.
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);