Connect the Nots

connectToday I learned that you can’t adjust the font size in SQL Server Management Studio’s Object Explorer pane. Nor registered servers, nor properties. This has been noted, and closed as “Maybe someday we’ll get to it, maybe”.

This rather sucks, as the trend seems to be making more and more things zoomable and adjustable and all that. And all in time for me and Sean to grow thoroughly into middle age, with our progressively stronger computer reading glasses.

All this is to say, it’s like some fun, weird, depressing Easter egg hunt to wander around the corridors of Connect.Microsoft.com, the official MS bugs and suggestions site.

So, from Connect: What are YOUR favorite bugs? Most unjustly closed suggestions? More worthless requests?  Inquiring minds want to know…

Happy days,
Jen McCown
http://www.MidnightDBA.com/Jen

“Thing IS NOT NULL” isn’t the same as “Thing != NULL”

Nonsensical questions: What color do you want that DB?
Nonsensical questions: What color do you want that DB?

I’ve talked about NULLs before. (Ref: Here’s a bit on ISNULL(), and here’s one about understanding NULL at a basic level.)  And yet, it’s worthwhile to bring these things up from time to time.

“[Thing] != NULL” is nonsense. It won’t work the way you want it to work, because NULL literally means “unknown”. Nothing in the world can be equal to, or not equal to, an unknown.

The TechNet article on IS [NOT] NULL says:

To determine whether an expression is NULL, use IS NULL or IS NOT NULL instead of comparison operators (such as = or !=). Comparison operators return UNKNOWN when either or both arguments are NULL.

Example time – what does != NULL do?

Let’s walk through an example.

This will create a temporary table and add three rows, one of which has a NULL value in the column “mychar”:

CREATE TABLE #tmp (id INT IDENTITY(1,1), mychar CHAR(1) NULL);
INSERT INTO #tmp (mychar) VALUES ('a'), ('b'), (NULL);
SELECT id, mychar FROM #tmp;

So, the data in our table is:

id mychar
1 a
2 b
3 NULL

Now it’s very easy to demonstrate how the IS NULL/IS NOT NULL predicates will compare against the =/!= comparison operators.

IS NULL vs =

First, let’s take a look at IS NULL versus = NULL.

select id, mychar FROM #tmp WHERE mychar IS NULL;
select id, mychar FROM #tmp WHERE mychar = NULL;

The IS NULL query returns one row, as it should:

id mychar
3 NULL

But the “= NULL” query returns no rows. Why? Because SQL has NO IDEA what you’re talking about. “Does mychar equal an unknown? Um. I don’t know? Whatever, here’s your empty result set.

IS NOT NULL vs !=

And now, IS NOT NULL versus != NULL.

select id, mychar FROM #tmp WHERE mychar IS NOT NULL;
select id, mychar FROM #tmp WHERE mychar != NULL;

The IS NULL query returns two rows, as it should:

id mychar
1 a
2 b

But again, the query with the comparison operator “!= NULL” returns no rows.  “Does mychar not equal an unknown?” is, as far as SQL Server is concerned, not a valid question. It’s exactly like asking a coworker, “What sized year did you eat for lunch?” or “What color do you want that database?

A little discussion…

As to whether it’s faster, as someone asked, to use = NULL and != NULL…I haven’t run the benchmarks, as it’s nonsense. But I’d assume these would be faster than IS NULL/IS NOT NULL, since SQL doesn’t bother trying to work out “is this equal to unknown”. You’re just not going to get any rows back.

If you’d like to speed up performance in this way, it’d be much simpler and clearer to use WHERE 1=2; or to not write the query at all.

Happy Days,
Jen
http://www.MidnightDBA.com/Jen

Have you checked out Minion Reindex yet? It’s our free, easy, comprehensive index maintenance solution. You’ll love it, seriously.

Sean’s session, “Monster Text Manipulation: Regular Expressions for the DBA” #SQLBits

Update: Thank you, SQLBits, for rescheduling Sean’s session! In addition to the recording/code link for this class (below), you can find recording/code for Stored Procedure Solutions here, and for Powershell Cmdlets for DBAs here.

We had an awful schedule mix up here at SQLBits. If, as a result, you missed out on Sean’s Regular Expressions for the DBA session, you can find a previous recording here, and the session code here.

To the attendees and organizers of SQLBits: this mixup was entirely my doing, and I am VERY sorry for the inconvenience.
-Jen

Real news, real tech.