sql server - Case statement not correctly matching expected values -

i'm trying generate randomized data, , i've been using newid() seed functions since called once every row , guaranteed return different result each time. i'm getting values somehow not equal integers in expected range.

i've tried few variations, including highly upvoted one, result in same issue. i've put script shows problem:

declare @test table (id uniqueidentifier) insert @test select newid() sys.objects  select      floor(rand(checksum(id)) * 4),     case isnull(floor(rand(checksum(id)) * 4), -1)         when 0 0         when 1 1         when 2 2         when 3 3         when -1 -1         else 999     end,     floor(rand(checksum(newid())) * 4),     case isnull(floor(rand(checksum(newid())) * 4), -1)         when 0 0         when 1 1         when 2 2         when 3 3         when -1 -1         else 999     end @test 

i expect results in range 0 3 4 columns. when unique identifiers retrieved table, results correct (first 2 columns.) similarly, when they're output on fly they're correct (third column.) when they're compared on fly integers in case statement, returns value outside expected range.

here's example, these first 20 rows when ran now. can see there '999' instances in last column shouldn't there:

0   0   3   1 3   3   3   1 0   0   3   3 3   3   2   999 1   1   2   999 3   3   2   1 2   2   0   999 0   0   0   0 3   3   2   0 1   1   3   999 3   3   0   999 2   2   2   2 1   1   3   0 2   2   3   0 3   3   1   999 0   0   1   999 3   3   1   1 0   0   0   3 3   3   0   999 0   0   1   0 

at first thought maybe type coercion different expected, , result of rand() * int float not int. wrapped in floor force int. thought perhaps there's odd null value creeping in, case statement null returned -1, , there none.

i've run 1 2 different sql server 2012 sp1 instances, both give same sort of results.

in fourth column, isnull(floor(rand(checksum(newid())) * 4), -1) being evaluated 5 times for each row. once each branch of case. on each call values can different. can return 2, not match 1, 3 not match 2, 1 not match 3, 3 not match 4 fall else , return 999.

this can seen if execution plan, , @ xml, there line [whitespace added.]:

<scalaroperator scalarstring=" case when isnull(floor(rand(checksum(newid()))*(4.000000000000000e+000)),(-1.000000000000000e+000))=(0.000000000000000e+000) (0)      else case when isnull(floor(rand(checksum(newid()))*(4.000000000000000e+000)),(-1.000000000000000e+000))=(1.000000000000000e+000) (1)          else case when isnull(floor(rand(checksum(newid()))*(4.000000000000000e+000)),(-1.000000000000000e+000))=(2.000000000000000e+000) (2)              else case when isnull(floor(rand(checksum(newid()))*(4.000000000000000e+000)),(-1.000000000000000e+000))=(3.000000000000000e+000) (3)                  else case when isnull(floor(rand(checksum(newid()))*(4.000000000000000e+000)),(-1.000000000000000e+000))=(-1.000000000000000e+000) (-1)                      else (999)                  end              end          end      end  end "> 

placing expression in cte seems keep recomputes happening:

; t (select isnull(floor(rand(checksum(newid())) * 4), -1) c @test) select case c         when 0 0         when 1 1         when 2 2         when 3 3         when -1 -1         else 999 end t