Do you know how NULLIF and non-deterministic functions work?

[SQLDenis]

Do you know how NULLIF and non-deterministic functions work?

Run this first

CREATE TABLE #j (n varchar(15))

DECLARE @a int
SET @a = 1
WHILE @a <= 1000 BEGIN
INSERT #j
SELECT NULLIF(REPLICATE('1', RAND()*2) , ' ')
SET @a = @a + 1
END
Go

After that is done run this query

SELECT * FROM #j WHERE n = ' '

You will get back between 200 and 300 rows. What just happened? In our insert we use this NULLIF(REPLICATE('1', RAND()*2) , ' ')
What this does is the following: if REPLICATE('1', RAND()*2) equals ' ' then it will insert a NULL, so where do the blanks come from? Well let's find out

run this

SET SHOWPLAN_TEXT ON

SELECT NULLIF(REPLICATE('1', RAND()*2) , ' ')

and we see the following

  |--Compute Scalar(DEFINE:([Expr1000]=If (replicate('1', Convert(rand(NULL)*2))=' ') then NULL else replicate('1', Convert(rand(NULL)*2))))

This can also be written like this

SELECT CASE  WHEN REPLICATE('1', RAND()*2)   =' ' 
THEN NULL ELSE REPLICATE('1', RAND()*2) END

See what happens? First SQL evaluates if REPLICATE('1', RAND()*2) is ' ' if that is not ' ' then the same code gets executed again and thus could be a blank
If you use a variable this can never happen.

Here is an example, keep hitting F5 and you will see it will never be blank

DECLARE @val float 
SET @val = RAND() 

SELECT NULLIF(REPLICATE('1', CONVERT(int, @val*2)) , ' ')

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions