T-SQL Query Gives Different Results when declaring

Question

So I've been experimenting with some SQL random number generation in a query, and I noticed something odd.

Let's say I run the following query:

declare @Random int = CAST(RAND(CHECKSUM(NEWID())) * 5 as INT) + 1;

select CHOOSE(@Random,'One','Two','Three','Four','Five')

SQL random number gen is a little bulky, but the basic idea here is simple - pick a random number between 1 and 5, then display that number as text to the select window. This works as expected.

However, if I take the SAME query, but paste the random number formula into the method instead of declaring it as an integer, so it's all on one line:

select CHOOSE(CAST(RAND(CHECKSUM(NEWID())) * 5 as INT) +
    1,'One','Two','Three','Four','Five')

I still get values One to Five when I run the query, but sometimes I also get a NULL. The NULL comes up pretty often, about one in 5 times. If I put both queries into ssms and run them next to each other a few times, I often see a null value for the second query, but the first is never NULL.

So why is that? Aren't these exactly the same calculation? I have no idea why these two queries would give different results, but I feel like I can learn something useful about T-SQL by finding out.

Any experts want to enlighten me?

Answer 1

This is a very subtle problem with the choose function in SQL Server (well, Microsoft probably considers it a feature). The function is really shorthand for a case expression. So, your expression:

select CHOOSE(CAST(RAND(CHECKSUM(NEWID())) * 5 as INT) +
1,'One','Two','Three','Four','Five')

is translated into:

select (case when CAST(RAND(CHECKSUM(NEWID())) * 5 as INT) + 1 = 1 then 'One'
             when CAST(RAND(CHECKSUM(NEWID())) * 5 as INT) + 1 = 2 then 'Two'
             when CAST(RAND(CHECKSUM(NEWID())) * 5 as INT) + 1 = 3 then 'Three'
             when CAST(RAND(CHECKSUM(NEWID())) * 5 as INT) + 1 = 4 then 'Four'
             when CAST(RAND(CHECKSUM(NEWID())) * 5 as INT) + 1 = 5 then 'Five'
        end)

That means that the newid() is being called multiple times. This is usually not the behavior that you want.