[Handbook] Beware of the big pitfalls of using NEWID in where
ABS(CHECKSUM(NEWID())) % 3 - make the GUID a positive integer and take the modulus
is randomly returning the numbers 0, 1, and 2, it's impossible to return anything else, but if you use it inside where, amazing things happen, like this query.
--Create a table with only 1 column and 3 rows, holding the values 0,1,2 DECLARE @t TABLE(Col1 int) INSERT @t SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 --And then a random line. SELECT * FROM @t WHERE Col1 = ABS(CHECKSUM(NEWID())) % 3
Each execution will and will only get a number from 0 to 2, as it should, but run it twice more and you get all sorts of magical results, with and without returning two lines, fat and fishy, which for a while made me question my life, until I realized I wasn't alone:.
So it can't be fixed, so I'll just have to plant the flag memo and work around it in the future, hopefully it will give a heads up to those who already use it this way or may do so.