John Rempel (8/28/2013)
Jeff, moving the (SELECT ABS(CHECKSUM(NEWID()))%3) from the CROSS APPLY into the CASE seems to work as well. When should CROSS APPLY be used, or is it equivalent in this case?
SELECT TOP (1000)
ID = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)),
RandomColor = CASE (SELECT ABS(CHECKSUM(NEWID()))%3)
WHEN 1 THEN 'Red'
WHEN 2 THEN 'Green'
ELSE 'Yellow'
END
FROM sys.all_columns ac1
CROSS JOIN sys.all_columns ac2
;
If you look deep in the properties of some of the compute scalars in the actual execution plan, you see that this has the same problem I spoke of before. Here's where...
Scalar Operator(CASE WHEN [Expr1006]=(1) THEN 'Red' ELSE CASE WHEN [Expr1007]=(2) THEN 'Green' ELSE 'Yellow' END END)
Expr1006 and Expr1007 are separate copies of the Scalar Operator(abs(checksum(newid()))%(3)). What ends up happening is that Yellow will turn up more than the other two colors because if the first random forumula isn't a 1, then it calculates a new random formula. Each random number generator only has a 1 out of 3 chance of finding its mark. That means that 2/3rds of the time, each will miss its mark. I don't know what that works out to odds wise (no coffee in the last 5 hours), but it means that Yellow will always come out with the most hits followed by Red, followed by Green. The CROSS APPLY doesn't have the same problem.
Thanks much. I always enjoy reading your articles and comments. And I always learn or re-learn something.
Thank you very much for the feedback. I'm glad that I can return something to the community that has helped me so much.
--Jeff Moden
Change is inevitable... Change for the better is not.