Non deterministic functions and nullif
From Wiki
NULLIF will return a null value if the two specified expressions are equivalent. So to give an example
- DECLARE @v VARCHAR
- SET @v = ' '
- SELECT NULLIF(@v,' ')
That returned NULL because @v and ' ' are the same
Now 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
Then without running try to guess if the following query will return any rows
- SELECT * FROM #j WHERE n = ' '
And it does return rows, but why?
The NULLIF does this
- SELECT CASE WHEN REPLICATE('1', RAND()*2) =''
- THEN NULL ELSE REPLICATE('1', RAND()*2) END
It checks the first expression and if that is not then it does the second, however that can also be a blank
Just run this 10 times or so and you will see that it will become a blank eventually
Here is a way to check the execution plan
- SET SHOWPLAN_TEXT ON;
- GO
- SELECT NULLIF(REPLICATE('1', RAND()*2) , ' ');
- GO
- SET SHOWPLAN_TEXT OFF;
- GO
The plan looks like this
|--Compute Scalar(DEFINE:([Expr1000]=If (replicate('1', Convert(rand(NULL)*2))=' ') then NULL else replicate('1', Convert(rand(NULL)*2)))) |--Constant Scan
Contributed by: --SQLDenis 03:16, 31 May 2008 (GMT)
Part of SQL Server Programming Hacks
Section Pitfalls
