Why am I getting inconsistent case statement result based on whether value from rand function is stored in variable?

Question

I am writing a loop to build up and insert random strings with 5 characters. In testing which approach is fasted for building up these string I have run into inconsistent results that hopefully someone can explain to me.

The three approaches I am comparing are setting up a table variable with all possible random numbers and the characters that match them, a case statement that looks at random number and decides what character to return based on a formula, and a case statement that has all possible random numbers and the characters that match them.

Where I am getting stuck is the last approach. If I build a case statement with rand output as value and a when/then statement for all 62 possible characters I often get a NULL result, however if I put the rand value in a variable first and then use the variable as the case value then it never comes back with NULL. If I select the rand output it is never outside the 0-61 range but if I put in an else statement then that is getting hit. I am not aware of any special considerations here but must be overlooking or be unaware of something going on.

Non-variable approach, returns NULL around half the time:

select case round(rand()*61, 0)
    when  0  then  'A'
    when  1  then  'B'
    when  2  then  'C'
    when  3  then  'D'
    when  4  then  'E'
    when  5  then  'F'
    when  6  then  'G'
    when  7  then  'H'
    when  8  then  'I'
    when  9  then  'J'
    when  10  then  'K'
    when  11  then  'L'
    when  12  then  'M'
    when  13  then  'N'
    when  14  then  'O'
    when  15  then  'P'
    when  16  then  'Q'
    when  17  then  'R'
    when  18  then  'S'
    when  19  then  'T'
    when  20  then  'U'
    when  21  then  'V'
    when  22  then  'W'
    when  23  then  'X'
    when  24  then  'Y'
    when  25  then  'Z'
    when  26  then  'a'
    when  27  then  'b'
    when  28  then  'c'
    when  29  then  'd'
    when  30  then  'e'
    when  31  then  'f'
    when  32  then  'g'
    when  33  then  'h'
    when  34  then  'i'
    when  35  then  'j'
    when  36  then  'k'
    when  37  then  'l'
    when  38  then  'm'
    when  39  then  'n'
    when  40  then  'o'
    when  41  then  'p'
    when  42  then  'q'
    when  43  then  'r'
    when  44  then  's'
    when  45  then  't'
    when  46  then  'u'
    when  47  then  'v'
    when  48  then  'w'
    when  49  then  'x'
    when  50  then  'y'
    when  51  then  'z'
    when  52  then  '0'
    when  53  then  '1'
    when  54  then  '2'
    when  55  then  '3'
    when  56  then  '4'
    when  57  then  '5'
    when  58  then  '6'
    when  59  then  '7'
    when  60  then  '8'
    when  61  then  '9'
    end

Variable approach, never NULL:

declare @r int
set @r  = round(rand()*61, 0)
select case @i
    when  0  then  'A'
    when  1  then  'B'
    when  2  then  'C'
    when  3  then  'D'
    when  4  then  'E'
    when  5  then  'F'
    when  6  then  'G'
    when  7  then  'H'
    when  8  then  'I'
    when  9  then  'J'
    when  10  then  'K'
    when  11  then  'L'
    when  12  then  'M'
    when  13  then  'N'
    when  14  then  'O'
    when  15  then  'P'
    when  16  then  'Q'
    when  17  then  'R'
    when  18  then  'S'
    when  19  then  'T'
    when  20  then  'U'
    when  21  then  'V'
    when  22  then  'W'
    when  23  then  'X'
    when  24  then  'Y'
    when  25  then  'Z'
    when  26  then  'a'
    when  27  then  'b'
    when  28  then  'c'
    when  29  then  'd'
    when  30  then  'e'
    when  31  then  'f'
    when  32  then  'g'
    when  33  then  'h'
    when  34  then  'i'
    when  35  then  'j'
    when  36  then  'k'
    when  37  then  'l'
    when  38  then  'm'
    when  39  then  'n'
    when  40  then  'o'
    when  41  then  'p'
    when  42  then  'q'
    when  43  then  'r'
    when  44  then  's'
    when  45  then  't'
    when  46  then  'u'
    when  47  then  'v'
    when  48  then  'w'
    when  49  then  'x'
    when  50  then  'y'
    when  51  then  'z'
    when  52  then  '0'
    when  53  then  '1'
    when  54  then  '2'
    when  55  then  '3'
    when  56  then  '4'
    when  57  then  '5'
    when  58  then  '6'
    when  59  then  '7'
    when  60  then  '8'
    when  61  then  '9'
    end

Can I ask what the point of this is? Can you just generate a random GUID with `NEWID()`? — Zane, Mar 27 '14 at 18:51
Related: http://stackoverflow.com/a/21487904/61305 and probably even more so: http://stackoverflow.com/questions/21918536/how-does-this-case-expression-reach-the-else-clause — Aaron Bertrand, Mar 27 '14 at 18:51

score 0 · Answer 1 · answered Mar 27 '14 at 18:53

It has to do with the way the CASE expression is evaluated. I might be wrong, but the round(rand()*61, 0) is evaluated only once when assigned to a variable, but multiple times when used in your first query (for every 'when'). This explains why it returns NULL for about half the time.

Compare to exec plan for this query:

select case (select count(*) from sales.salesorderheader)
    when  0  then  'A'
    when  1  then  'B'
    when  2  then  'C'
    when  3  then  'D'
    when  4  then  'E'
    when  5  then  'F'
    when  6  then  'G'
    when  7  then  'H'
    when  8  then  'I'
    when  9  then  'J'
    when  10  then  'K'
end

score 0 · Answer 2 · edited May 23 '17 at 12:28

dean's answer explains that the rand() function is evaluated once per WHEN clause, and this answer (referenced by Aaron Bertrand in comments) confirms that notion (with respect to the conceptually similar NEWID()).

Itzik Ben-Gan also confirms this in his article "Change in Behavior of RAND and NEWID in SQL Server 2005" from Sep 9, 2007. He also shows a work-around, which I've applied to your situation through the use of a CTE. You could also use a derived table as Itzik does.

;WITH BaseData AS
(
    SELECT
        round(rand()*61, 0) AS RandomValue
)
select
    RandomValue,
    case RandomValue
    when  0  then  'A'
    when  1  then  'B'
    when  2  then  'C'
    when  3  then  'D'
    when  4  then  'E'
    when  5  then  'F'
    when  6  then  'G'
    when  7  then  'H'
    when  8  then  'I'
    when  9  then  'J'
    when  10  then  'K'
    when  11  then  'L'
    when  12  then  'M'
    when  13  then  'N'
    when  14  then  'O'
    when  15  then  'P'
    when  16  then  'Q'
    when  17  then  'R'
    when  18  then  'S'
    when  19  then  'T'
    when  20  then  'U'
    when  21  then  'V'
    when  22  then  'W'
    when  23  then  'X'
    when  24  then  'Y'
    when  25  then  'Z'
    when  26  then  'a'
    when  27  then  'b'
    when  28  then  'c'
    when  29  then  'd'
    when  30  then  'e'
    when  31  then  'f'
    when  32  then  'g'
    when  33  then  'h'
    when  34  then  'i'
    when  35  then  'j'
    when  36  then  'k'
    when  37  then  'l'
    when  38  then  'm'
    when  39  then  'n'
    when  40  then  'o'
    when  41  then  'p'
    when  42  then  'q'
    when  43  then  'r'
    when  44  then  's'
    when  45  then  't'
    when  46  then  'u'
    when  47  then  'v'
    when  48  then  'w'
    when  49  then  'x'
    when  50  then  'y'
    when  51  then  'z'
    when  52  then  '0'
    when  53  then  '1'
    when  54  then  '2'
    when  55  then  '3'
    when  56  then  '4'
    when  57  then  '5'
    when  58  then  '6'
    when  59  then  '7'
    when  60  then  '8'
    when  61  then  '9'
    end AS TranslatedRandomValue
FROM    BaseData

Why am I getting inconsistent case statement result based on whether value from rand function is stored in variable?

2 Answers2