A strange problem found a guy in other forum. And because I hate to not know WHY this happens I ask here just in case someone have explanation of this behavior:

1. Try this:

declare @j table (n varchar(2000))
declare @a int
set @a = 1
while @a <= 10000 begin
   insert @j 
          SELECT IsNull(NullIf(Replicate('1', RAND()*2) +
                               Replicate('7', RAND()*4) +
                               Replicate('8', RAND()*4),''),'999')
    set @a = @a + 1
end
select *, datalength(n) from @j where n = ''

And you WILL get a resultset. The question is WHY??? As you could see there is

ISNULL(NULLIF(query here,''),'999')

You should never get an empty string in the result.


BUT, that query works:

declare @j table (n varchar(2000))
declare @a int
set @a = 1
while @a <= 10000 begin
   insert @j 
          SELECT IsNull(NullIf(Replicate('1', Tbl1.Test1*2) +
                               Replicate('7', Tbl1.test2*4) +
                               Replicate('8', Tbl1.test3*4),''),'999')
          FROM (SELECT RAND() as Test1, RAND() as Test2, RAND() as Test3) Tbl1
    set @a = @a + 1
end
select *, datalength(n) from @j where n = ''