SQL Server Standard Article Available

April 7, 2009 at 1:21 pm (PASS, SQL Server 2005, SQL Server 2008, SQLServerPedia Syndication, TSQL) (, , , )

Unfortunately PASS decided to put the SQL Server Standard to sleep right after I got an article published in it (and no, it wasn’t my fault). However, PASS, being the service oriented organization that they are, has decided to make back issues of the magazine available online. So, to read the article I wrote comparing various methods of retrieving versioned data using different TSQL constructs, click this link. Go to page 14. Oh yeah, and you can see other peoples articles here too.

There are other things coming out of the editorial committee soon (although I need to get one of them done myself… yikes).

Permalink 3 Comments

NULL Is Not NULL

April 7, 2009 at 7:55 am (SQL Server 2005, SQL Server 2008, SQLServerPedia Syndication, TSQL) (, , )

NULL as a concept seems so easy but it leads to so many problems for people. To put it as simply as possible, NULL does not equal anything. It does not “not equal” anything either. It can’t be compared to other values in any way. There was a recent post over at SQL Server Central where the user had a query problem that one of the great people over at SSC solved, handily. They also pointed out that the bit of code being used “WHERE nt.NullableString NOT LIKE ‘null%’” was also a problem. The user insisted that it was eliminating the NULL values. Well, yeah, sort of, it was, but not because it was actually applying a filter to the NULLs. Remember, a NULL does not equal or “not equal” anything and therefor when checking for the equality of something you won’t get NULL values.

Yeah, you’re thinking, so what’s the problem with the code then? This. It can lead to problems with your results. Here’s some sample code that illustrates the problem:

 CREATE TABLE #NullTest
(ID INT IDENTITY(1,1),NullableString NVARCHAR(50) NULL)

–I put the – next to the string NULL so you can see which one it is.
INSERT INTO #NullTest (
NullableString)
SELECT ‘Some Value’
UNION
SELECT
‘NULL-‘
UNION

SELECT NULL
UNION 
SELECT ‘NULL values are not allowed’
UNION 
SELECT ‘NOT NULL’

–returns the five rows available
SELECT * FROM #NullTest AS nt

–returns only two rows, although two other rows should be returned
SELECT *
FROM #NullTest AS nt
WHERE nt.NullableString NOT LIKE ‘null%’

–instead of returning three values, it only returns two, as it should
SELECT *
FROM #NullTest AS nt
WHERE nt.NullableString LIKE ‘NULL%’

— the real values that are not NULL, four of them
SELECT *
FROM #NullTest AS nt
WHERE NullableString IS NOT NULL

DROP TABLE #NullTest

As you can see, the wrong number of rows are returned depending on how the query is used. There are four rows in the table that are NOT NULL (notice the syntax) but running the query the other way only returns two values. There are ongoing debates about the utility, meaning and purpose of NULLs. I’m not getting into it. If you’re using them, you need to understand what they are and what they are not.

Permalink 4 Comments