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

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