Jen McCown

I take occasional "brain breaks" at the office, to refresh me before I set upon the next course of work.  Mostly I like to troll around a favorite set of websites and links from them. So it was today that I happened across this book recommendation – I think on Brent Ozar's site – for a 2006 book entitled "Time management for system administrators", by Tom Limoncelli (see his blog at http://everythingsysadmin.com/, he seems like good people).  There's a preview available online, so I skimmed it.  This caught my eye:

Over the years, I've decided the answer to these questions is always "yes". I can now stop wasting brainpower trying to make a decision each time the issue comes up.

Would this be a good time to save the file I'm working on?
Should I take my organizer with me (versus leaving it here)?
Should I add this to my to do list?
Should I check my calendar before I agree to this appointment?
Should I write this on my calendar?
Should I check to see whether I have plans after work before I agree to stay late?
Should I check to see whether I have any early appointments before I decide to play one more game of Half Life this morning?
Should I do The Cycle today (versus slacking off)?
Should I fill my car's gas tank now (versus procrastinating until it is an emergency)?
Should I do this small task or chore now (versus procrastinating and hoping nobody notices or the task doesn't turn into an emergency)?

This, and pretty much all of the other content posted on the preview, appealed to me.  After all, I've just come from being a full time mom and part time contractor, to full time mom and office drone, producing training videos, articles, and blogs, and trying to slip in the occasional "having a life".   I could do with a little time management aimed specifically at IT people. 

-Jen McCown, http://www.MidnightDBA.com

I just recorded a couple of Midnight Snacks on NULLs, and I felt inspired to blog about it too.

Equals Null Mistake (video)

First, let's talk about a mistake that's easy to make, especially if you're in the throes of a coding frenzy, tossing JOINs here and WHERE clauses there, to get your data set returned just right.  So you're working with this:

select * from [Hortence]
WHERE heightIn >= 40
  AND (lastname LIKE 'M%' OR lastname LIKE 'N%')

And the query returns 32 rows.  So you think, ok good, now let's see all of the M% and N% people who have NULL for the heightIn column…

select * from [Hortence]
WHERE heightIn = NULL 
  AND (lastname LIKE 'M%' OR lastname LIKE 'N%') 

Beautiful! And you run the query, and it returns no rows.  "Now," you say quietly to yourself, "that's just not right. I know there are several rows with NULL heightIn fields!"  So you widen the result set, just to see what's up:

select * from [Hortence]
WHERE heightIn = NULL 

And it returns no rows. "That's TOTALLY not right," you say loudly, startling a passing business analyst. "What the friggle?" You try again:

select * from [Hortence]

And there are rows, and yes, some of them have NULL heightIn fields. "Well I KNEW that already!" you shout, and building security starts to get concerned.  Slow down, cowpoke, and let's try again.  It's really easy to forget that NULLs are a special case.  NULL means "unknown"…Null can't equal Null because there's no way to prove that an unknown equals another unknown.  So for our special case NULL, we must use our special NULL comparison operator, IS NULL.

select * from [Hortence]
WHERE heightIn IS NULL 
  AND (lastname LIKE 'M%' OR lastname LIKE 'N%') 

 There. Now stop talking to yourself at work.

About Nulls

We can have some quick fun with NULLs.  Walk through the following code and comments in SSMS for a quick lab on NULL behavior.

IF 1 = 1
  SELECT '1 = 1'      — true, 1 = 1

IF 1 = NULL
  SELECT '1 = NULL' — No, NULL is unknown

IF 1 <> NULL
  SELECT '1 <> NULL' — Again, false: nothing cannot be proven equal or inequal to the unknown NULL

IF 1 IS NULL
  SELECT '1 IS NULL' — "IS NULL" is the right operator, but the known value 1 clearly isn't NULL (unknown)

IF 1 IS NOT NULL
  SELECT '1 IS NOT NULL' — True! 1, a known value, is NOT NULL (unknown)

IF '' IS NULL
  SELECT '<blank> IS NULL' — The blank string value '' is not unknown; we know it's blank. So '' IS not NULL

IF '' IS NOT NULL
  SELECT '<blank> IS NOT NULL' — See? '' IS NOT NULL. Told you so.

SELECT ISNULL(NULL, 1) — the ISNULL function returns the first non NULL value from a list of two.
SELECT ISNULL(1, 2)
SELECT ISNULL(3,2)

— the COALESCE function returns the first non NULL value from a list of two or more.
SELECT coalesce(NULL, 1, 2, 3, NULL, 4, 5, 'xyz') [coalesce]

-Jen McCown, http://www.midnightdba.com/