Inconsistan behavior of Table Variable used in subquery with NEWID()

Question

The following code should obviously only delete 5 rows. No more, no less.

Simple question: Why does it not?

 

DECLARE @t TABLE (nDex INT IDENTITY(1,1), val VARCHAR(9))

-- sample data
INSERT @t
 SELECT 'a' UNION ALL SELECT 'b' UNION ALL SELECT 'c' UNION ALL 
 SELECT 'd' UNION ALL SELECT 'e' UNION ALL SELECT 'f' UNION ALL 
 SELECT 'g' UNION ALL SELECT 'h' UNION ALL SELECT 'i' UNION ALL 
 SELECT 'j'

DELETE t
FROM @t AS t
 INNER JOIN (SELECT TOP 5 nDex FROM @t ORDER BY NEWID()) AS b
  ON b.ndex = t.nDex

-- this should be 5 rows
SELECT * FROM @t ORDER BY ndex

 

*** Sorry, Using SQL 2005 (64bit) w/ SP2

Answer 1

This very recent thread should explain the behavior you're seeing and suggest a workaround (temp table instead of CTE/derived table). 

---

For every expert, there is an equal and opposite expert. - Becker's Law

Naomi Nosonovsky, Sr. Programmer-Analyst

My blog

Answer 2

I am not surprised by your findings..

Remove Order BY NEWID()  and  you should see expected behaviour (instead use order by ndex  ASC/DESC along with your TOP )

Answer 3

You can materialize the derived table, as Naomi suggested, or you can use another method to randomize the result, as you can see in this thread.

http://social.msdn.microsoft.com/Forums/en-GB/transactsql/thread/32674d22-3e42-4361-bbcc-5149eb619886

Example:

-- SS 2008
DECLARE @t TABLE (nDex INT IDENTITY(1,1), val VARCHAR(9))

-- sample data
INSERT @t
 SELECT 'a' UNION ALL SELECT 'b' UNION ALL SELECT 'c' UNION ALL 
 SELECT 'd' UNION ALL SELECT 'e' UNION ALL SELECT 'f' UNION ALL 
 SELECT 'g' UNION ALL SELECT 'h' UNION ALL SELECT 'i' UNION ALL 
 SELECT 'j'

DELETE t
FROM
	@t AS t
	INNER JOIN 
	(
	SELECT TOP 5 nDex 
	FROM @t 
-- Tip from Itzik Ben-Gan
	ORDER BY RAND(1. * DATEPART(microsecond, SYSDATETIME()) * nDex % 2147483648)
	) AS b
 ON b.ndex = t.nDex

-- this should be 5 rows
SELECT * FROM @t ORDER BY ndex
GO

 

 

---

AMB

Some guidelines for posting questions...

Answer 4

AMB,

I wanted to ask you a question, may be little sidetrack than original question but

why query optimizer treats NEWID() differently, why not RAND()...?

for e.g. in the query below

select rand(), rand(), NEWID(), NEWID() from sys.objects 

why rand() is evaluated per column only **once** while NEWID() is always unique for each value returned...

Answer 5

All, thank you for your replies.

I know how to avoid the issue.

My question was more to WHY it was happening, if it was a known issue, and if there are plans to release a hotfix with this in it?

 

Answer 6

In the thread I referenced there is a link to the Connect Item where you can vote. 

---

For every expert, there is an equal and opposite expert. - Becker's Law

Naomi Nosonovsky, Sr. Programmer-Analyst

My blog

Answer 7

Chiraq,

The answer is in the connect entry. Microsoft said that this behavior is due to the non-determinism of the function, but I don't buy that, since GETDATE() is not deterministic and the QO uses different approach.

Feel free to vote if you think that changing the behavior could benefit your business.

 

 

---

AMB

Some guidelines for posting questions...

Answer 8

Thank you Naomi and AMB.