NULL is NOT LIKE and NOT NOT LIKE

This is one of those things that you probably know, but have not run into it lately.  That was the case for me recently as I was troubleshooting an issue in someone else's code, and spent several hours on it before stumbling across this cause.

The issue at hand is that people forget about the true nature of NULL in a database column.  Another way of thinking about NULL is to substitute the word UNKNOWN.  NULL is not anything...it is unknown.  NULL does not equal anything.  That is why you have to use the magic phrase IS NULL instead of = NULL in your SQL queries:

USE AdventureWorks

SELECT COUNT(*) FROM Person.Address
-- 19,614 total records in the table

SELECT * FROM Person.Address WHERE AddressLine2 = NULL
-- ZERO Records Returned

SELECT * FROM Person.Address WHERE AddressLine2 IS NULL
-- 19,252 Records Returned

And not only is NULL not equal to anything, it is not LIKE anything either.  That's pretty easy for people to grasp, once they have bought into the equals issue...

SELECT * FROM Person.Address WHERE AddressLine2 LIKE '%2%'
-- 61 Records returned

Notice that none of the rows returned have NULL in the AddressLine2 column because NULL is not LIKE 2.  But what trips up some database developers, and was at the root of my trouble today, is that while NULL is not LIKE anything, it also is not NOT LIKE anything either.

SELECT * FROM Person.Address WHERE AddressLine2 NOT LIKE '%2%'
-- 19,553 Records EXPECTED
-- Only 301 Records actually returned

In the above example, the developer was expecting the inverse of the LIKE clause, that is, he expected the other 19,553 records to be returned, but if you look at the actual results, you see that what you get are only the records that have a value (NOT NULL) and that value is not like the search value.  All of the records that have a NULL AddressLine2 are excluded from the result set.

How do you fix it?

There are a few different approaches you could take to fixing it.  My first suggestion would be to disallow NULLs in the column in the first place.

-- When removing NULL from an existing table, first UPDATE the table with values.
UPDATE Person.Address SET AddressLine2 = '' WHERE AddressLine2 IS NULL

-- NOW Alter the table to disallow NULL
ALTER TABLE Person.Address
ALTER COLUMN AddressLine2 NVARCHAR(60) NOT NULL

Now don't get me wrong, I'm not one of those guys who forbids NULL in his databases.  I'm like the ability to have NULL in many situations.  To me there is a certain amount of truthiness to saying, "Hey, I don't know what the value is." instead of just using an empty string or zero (especially zero...zero is a real value...there's a big difference between having zero dollars in your savings account and not knowing how much is in savings).  On the other hand, there are times when a column absolutely must have a value.  It is NOT acceptable for the bank to not know how much is in my savings account, for example.

So, if you are going to still allow NULL in your database, then you need to interpret or convert NULL on the fly using ISNULL or a CASE statement, or some other related approach.

SELECT * FROM Person.Address WHERE ISNULL(AddressLine2, '') LIKE '%2%'
-- 61 Records returned

SELECT * FROM Person.Address WHERE ISNULL(AddressLine2, '') NOT LIKE '%2%'
-- 19,553 Records returned

This returned the expected results, but be warned, there are serious performance ramifications to using functions (e.g. ISNULL() ) in your WHERE clause, and better options exist.  Some recommended reading on that subject are the article Avoid enclosing Indexed Columns in a Function in the WHERE clause by Bill Graziano and the blog post Optimizing Conditional WHERE Clauses: Avoiding ORs and CASE Expressions by Jeff Smith.

P.S. For another interesting challenge with NULL, check out Peter Larson's NULL NOT IN Conundrum.

Debugging with IE8

I recently upgraded to Internet Explorer 8 because I was having an issue with a collection of sites that I visit regularly (typically opening them in a tab group).  There is something renegade that frequently caused IE7 to crash if I too-quickly closed a tab and tried to scroll or quickly close another tab...it was annoying, but not enough for me to truly troubleshoot and pinpoint the culprit.  This didn't happen all the time, but when it did, usually that group was involved.  Well, I thought maybe it was just something screwy with my dev machine, and maybe the IE8 upgrade would fix it.  And it did...for a while.

You see, IE8 installs with the default action of spanning new processes for new tabs (there is some formula where it's not exactly 1-for-1, but for the first few it looks that way).  Well, this apparently handled my issue such that when whichever site or sites it was that were trouble, when I closed them, the process responsible for them might crash, but it wouldn't crash the whole of Internet Explorer (and my remaining tabs).  Now, I'm not here to promote Internet Explorer, but it is our company standard, so I use it because I have to develop to it.  And I know someone reading this will go off about Google Chrome or whatever other favorite tabbed browser they love and how it is far superior to IE.  Fine, I'm not interested.  You see, I'm not zealous about my browser, and I don't care too much, as long as it let's me do what I want to do.  But I'm getting off-track.  I just wanted to say, "hey, I upgraded to IE8 recently".

But the point of this post is that when I upgraded to IE8 on my development machine, I ran into a very serious problem.  A problem big enough that I started asking myself if I would be able to uninstall it and get back to IE7, even though the tab crashing thing was really annoying.  The problem I ran into was that Visual Studio 2005 would no longer stop on any Breakpoints in my ASP.NET application.  When you're in the middle of a big development effort, that's a very bad problem to have.

Fortunately, with a quick search of the Internet, I found out what the problem was.  That crash-proofing feature I espoused just paragraphs ago was causing problems with the debugger.  In essence, the Visual Studio 2005 debugger couldn't figure out which process to attach to.  See http://weblogs.asp.net/abdullaabdelhaq/archive/2009/06/01/VS-Debug-Problem-with-IE8.aspx for another explanation.  And also   https://blogs.msdn.com/askie/archive/2009/03/09/opening-a-new-tab-may-launch-a-new-process-with-internet-explorer-8-0.aspx for even more details.

So, I did just as it recommended and added the registry key to tell IE to stop spawning separate processes, and PRESTO! the debugger was working again and recognizing my breakpoints.  Hooray!  (Unfortunately, that tab-crashing behavior came back with it.  I'm not going to identify the sites in that group because I don't want to throw a bunch of innocent sites under the bus along with the guilty party.  And I'm still not convinced it isn't something screwy about my own setup.)

So I'm doing my part to spread the word for developers in case you run into this problem yourself.  There are notes in one of those links I provided that suggest this may not be a problem for "newer development environments" whatever that means (Visual Studio 2008?  The one after that?) and I'd love to upgrade to VS 2008, but that sort of change comes with its own set of baggage and you don't just jump into something like that with a project like this, without a little planning.  I'll get to that later.  In the meantime, I'm back in business, and if you have the same problem, now you know how to get unstuck, too.

«June»
SunMonTueWedThuFriSat
31123456
78910111213
14151617181920
21222324252627
2829301234
567891011