Login or Sign Up to become a member!

EXPERTS, INFORMATION, IDEAS & KNOWLEDGE

Social bookmarker Add this

Non deterministic functions and nullif

From Wiki

Jump to: navigation, search

NULLIF will return a null value if the two specified expressions are equivalent. So to give an example

  1. DECLARE @v VARCHAR
  2.     SET @v = ' '
  3.     SELECT NULLIF(@v,' ')


That returned NULL because @v and ' ' are the same

Now run this first

  1. CREATE TABLE #j (n VARCHAR(15))
  2.      
  3.     DECLARE @a INT
  4.     SET @a = 1
  5.     WHILE @a <= 1000 BEGIN
  6.     INSERT #j
  7.     SELECT NULLIF(REPLICATE('1', RAND()*2) , ' ')
  8.     SET @a = @a + 1
  9.     END


Then without running try to guess if the following query will return any rows

  1. SELECT * FROM #j WHERE n = ' '


And it does return rows, but why?

The NULLIF does this

  1. SELECT CASE  WHEN REPLICATE('1', RAND()*2)   =''
  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

  1. SET SHOWPLAN_TEXT ON;
  2.     GO
  3.      
  4.     SELECT NULLIF(REPLICATE('1', RAND()*2) , ' ');
  5.     GO
  6.      
  7.     SET SHOWPLAN_TEXT OFF;
  8.     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

190 Rating: 1.3/5 (3 votes cast)