Interesting Tidbit on ISNULL()

ISNULL() replaces NULL with the value you provide. For example:  

DECLARE @var int
SET @var = NULL
select ISNULL(@var, 100) as [Var]

This returns 100.  Simple and easy, right?  ISNULL is often used to take care of NULLs in mathematical sets. For example:

SELECT AVG(ISNULL(Charge, 0))
FROM BankAccount

This will change all NULLs to 0, so we can use them in the AVG function. (Remember, a NULL in any mathematical function will return NULL for the result). But what about something like this:

DECLARE @feathers int
SELECT @feathers = ISNULL(featherCount,0)
FROM Bird
WHERE BirdID = 17

That's fine if the featherCount for BirdID=17 is a number, or is null.  But what if there is no row with BirdID = 17?  @feathers will NOT be set to 0, as you might expect.  It will be = NULL.

Bottom line: ISNULL returns NULL, not the specified value, when 0 rows are returned.

 

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

Kick off Banned Book Week with “Beautiful Data”

It’s Banned Book Week!  The American Library Association observes Banned Book Week the last week of September to celebrate intellectual freedom.  Thanks to author Neil Gaiman (@neilhimself) for retweeting, or I wouldn’t have heard of it.

I’ve decided to spread the joy with a little celebration of my own.  We’re having a book review party here at MidnightDBA!  Now, this is a technical blog, and I don’t know of any tech books that are in danger of being banned, but we won’t discriminate.

We begin with Beautiful Data: The Stories Behind Elegant Data Solutions.  It’s 20 different stories about data – gathering, planning, interpreting, storing, visualizing, etc.  I’d like to go through and comment on every story in the book, but then this would be a Cliffs Notes, not a review.  Let’s have some highlights:

In “Seeing Your Life in Data”, Nathan Yau tells about developing two projects: “the Personal Environmental Impact Report (PEIR), a tool that allows people to see how they affect then environment… and your.flowingdata (YFD), and in-development project that enables users to collect data about themselves via Twitter”.   That in itself is cool; users simply sent formatted tweets (“ate salad”) to track mood, eating, or what have you, and then interact with the data on the site.  The difference in the data collection for the two systems is also an interesting discussion, and I liked the insight into the process for choosing the best PEIR visualization.

I think my favorite chapter is titled “What Data Doesn’t Do”, by Coco Krumme.  To break away and talk about me for a moment (and isn’t everything, in the end, about me?), I subscribed for some time to a LSAT Logic in Real Life podcast, which explored the fallacies behind our reactions to common or current events.  I really enjoyed learning the names and methods of misplaced logic and biases.  “What Data…” struck me in a very similar vein.  I’ve been trying hard not to quote this chapter, for fear that I’ll just type it out.  Still, I can’t resist my absolute favorite paragraph. It begins with the header “Data Alone Doesn’t Explain”

People explain. Correlation and causality, you may have heard, make strange bedfellows. Given two variables correlated in a statistically significant way, causality can work forward, backward, in both directions, or not at all. Statisticians have made a hobby … of chronicling the abused of correlation, like old ladies clucking at the downfall of traditional values in the modern world.

Beautiful.  Again, I’d love to give a review of each chapter, but then you’d fall in love with my writing instead of Beautiful Data.  Yeah, of course you would.

Finally, and most shallowly, it’s a really pretty book. Check out the cover art!  And that’s without considering the 70 color plates, including everything from user surveys and line charts, to laser data and DNA.  One, two…that’s 33 words to effectively say, “Pretty pictures!”

Let’s be serious for a moment, though. This book was, to me, truly extraordinary and truly entertaining.  I read it in pieces over the course of a few weeks, and it was lovely to take in one story – one angle on data problems or applications – and muse on it on and off until I had a few minutes to read the next.  It’s a book that lends itself to piecemeal reading, jumping around, and rereading at will. And it’s one I recommend not just to IT pros, but to everyone.

– Jennifer McCown, http://www.MidnightDBA.com

Weathering a Code Slump

Man, I tell you brothers n sistahs, it has been one HARD week of coding. The more kindhearted of you might not have noticed, but it's been a lot of "soft" blogging around here recently…a restult of my brainfry-ed-ness.  Well yeah, the T-SQL for the Absentminded DBA post was pretty awesome, you're right.  Anyway.

My fellow devs, I believe, will empathise when I say that my confidence has just been SHOT this last week, and it's a hard thing to be under deadline like that. And to put together good technical articles or blogs on top of that?  Yeah, no.  I have been reassured – just as I have reassured others in the past – that this is what we do. It's easy to get the focus wrong: either you're looking too closely at one particular part of a query, and forget the big picture, or you've got the wide-angle lens on and the problem seems insurmountable.  That's been me, for about week. 

This got me thinking about our first DBAs @ Midnight video, by the way, on How to Study.  There was an awesome moment where we cracked ourselves up over the idea of code slumpbusters – that's right, I said it. It starts at right about 13:55 if you're interested, but I digress.

So how do you keep the right focus?  How do you pull yourself back from a stretch coding that you're making way too difficult?  I'm still working on that, but I suspect it's going to be getting back to basics:  Outline, tech specs (of some sort), pseudocode, pseudocode-to-code, test. 

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

(Links – hopefully – fixed now…)