The Colour of Null

Today was a learning experience.

I’ve been working on a project which partly involved tweaking existing tables and stored procedures in a SQL Server database. I performed some tests between dev and production to make sure I hadn’t messed anything up and I found that a particularly large stored procedure was churning out different results.

The process of slowly stepping though and examining all the joins within the stored procedure took me the best part of two hours, but I finally narrowed down the cause of the difference to a particular table which from hereon will be referred to as table Trouble. I’m sure that an experienced SQL Server developer could have done it in ten minutes, but I’m relatively new to it and hence a bit on the slow side.

Here is a much simplified version of what I saw when I compared some records between the Trouble tables in the dev and production databases:

They’re identical, yes?

No. The Col1 column in production had NULL values which are truly NULL; the Col1 column in development had ‘NULL’ string values. D’oh! The ‘NULL’ strings had squirmed their way into the dev database via a bug in the Excel front end.

If you look very closely at the picture you’ll notice that SSMS distinguishes the NULL values from the ‘NULL’ values by giving them a very faint, yellow tinge. I only noticed the difference after giving my eyes a rest by going to make a cup of coffee.

Determined never to overlook my NULL tinges again, I immediately went to Tools > Options > Query Results to hunt for the Management Studio setting which would allow me to make it more prominent. To my dismay I couldn’t find anything appropriate.

It was only after some digging around that I discovered that the colour is determined by the Windows ToolTip setting. In Windows7 this can be found under Control Panel > Appearance and Personalization > Personalization > Window Color and Appearance > Advanced Appearance Settings:

Tool Tip Settings

Tool Tip Settings

This setting affects the whole of Windows so, after momentarily toying with the idea of electric pink, I settled for a slightly stronger yellow. There are no flies on my NULL values anymore!

Nice and Yellow

Advertisements

About Colin Legg

RAD Developer Microsoft MVP - Excel 2009 - 2014
This entry was posted in Microsoft SQL Server and tagged , , , , , , , , . Bookmark the permalink.

4 Responses to The Colour of Null

  1. Ben says:

    thanks a lot… there’s no place remaining where i have not searched for this…!

    Like

  2. Vaughan says:

    Thanks a bunch for this – very very helpful!

    Like

  3. Nick says:

    Fantastic, thank you! Works great in SSMS 2008 R2.

    What’s nice is that Windows 7 doesn’t seem to use the old-style tooltips many places (at least if Aero is turned on), so changing this color doesn’t have much of an impact at all.

    Like

  4. Chuck Hoffman says:

    BRAVO! Nice job. Really helpful.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s