Here is a fun teaser. 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
SELECT NULLIF(REPLICATE('1', RAND()*2) , ' ')
SET @a = @a + 1
Then without running try to guess if the following query will return any rows
SELECT * FROM #j WHERE n = ' '
If you would like to receive an email when updates are made to this post, please register here
Subscribe to this post's comments using
About Denis Gobo
I was born in Croatia in 1970, when I was one I moved to Amsterdam (and yes Ajax is THE team in Holland) and finally in 1993 I came to the US. I have lived in New York City for a bunch of years and currently live in Princeton, New Jersey with my wife and 3 kids. I work for Dow Jones as a Database architect in the indexes department, one drawback: since our data goes back all the way to May 1896 I cannot use smalldates ;-( I have been working with SQL server since version 6.5 and compared to all the other bloggers here I am a n00b. Some of you might know me from http://sqlservercode.blogspot.com/ or even from some of the newsgroups where I go by the name Denis the SQL Menace If you are a Tek-Tips user then you might know me by the name SQLDenis, I am one of the guys answering SQL Questions in the SQL Programming forum.