Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary. Once you register for an account you will have immediate access to the forums and all past articles and commentaries.
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
LTD Social Sitings
Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.