cool hit counter [Handbook] Beware of the big pitfalls of using NEWID in where_Intefrankly

[Handbook] Beware of the big pitfalls of using NEWID in where


This expression.

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

https://stackoverflow.com/questions/38498513/odd-sql-server-tsql-query-results-with-newid-in-the-where-clause

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.

-Bunbi-


Recommended>>
1、Learning Data Analysis in Python I updated version
2、Focusing and concentrating on promoting the development and application of agricultural big data
3、Talking about blockchain always leads to talking about consensual twocharacter
4、Five good original accounts are enough to understand computer vision
5、Details of Thailands digital asset trading guidelines 7 cryptocurrencies including bitcoin allowed for ICOs

    已推荐到看一看 和朋友分享想法
    最多200字,当前共 发送

    已发送

    朋友将在看一看看到

    确定
    分享你的想法...
    取消

    分享想法到看一看

    确定
    最多200字,当前共

    发送中

    网络异常,请稍后重试

    微信扫一扫
    关注该公众号