DBA Article: Learning By Teaching

“Those who can, do. Those who can’t, teach.” (Attributed to H.L. Mencken)

What this is meant to illustrate is that those who have top-notch skill in a field, make their money actually doing work in that field, while those without enough skill to actually do the thing, will end up teaching (at best). Of course, those who teach hate this quote.

But there’s a point in it that’s of interest beyond the immediate meaning. It’s that, if you can do the thing, you could teach it. It’s been said that anyone with a real understanding of a technical subject, can communicate it to others, clearly, even if the students don’t have the vocabulary of the subject.

For example, I’ve experienced two people with different levels of understanding, who were both trying to explain how air conditioners work. One, who knew how to install and repair air conditioners but didn’t know the theory behind them, confused the audience with detailed descriptions of “compressors” and “Freon pumps” and so on. The second, who actually understood the whole process, asked the audience, “Have you ever used one of those cans of compressed air to blow dust off of your keyboard?” Everyone said they had. “You know how the can gets really cold?” “Yeah. Yep. Experienced that.” “Air conditioning works like that. When gasses get compressed, and then expand suddenly, they cool down, just like that dusting can.” Everyone understood.

So, if you want to test your own understanding of a subject, explain it to someone who isn’t already an expert. Once you figure out how to do that, you’ll have proved how well you yourself understand it.

Years ago, I discovered online SQL forums. I found people asking questions. Some of those questions, but only a very small number, I could answer. So I did. Other questions, I could understand what was being asked, and could almost answer it. So I researched the subject till I could answer it, and then did.

Some of my answers were challenged by others. Those challenges forced me to either clarify or defend my answer, or to correct my answer when (more often than I like to admit) my original answer had been factually just plain wrong.

I learned to build tests and to try to break them, so when I gave an answer it was already tested and I knew it would work before I suggested it. This is the senior echelon, beyond teaching and into doing, and experience in it is critical to advancing any skill.

The more questions I tried to answer, the more I learned. I found answers to questions I would never have thought of asking. And, in many cases, those questions were ones I should have been asking, but didn’t realize it. So the answers helped me as much or more than the person with the original question.

And the challenges to my answers! Awesome! I had to eat some crow, and I had to unplug my ego from the discussions, but I made that easy on myself. In doing so, I unlearned a lot of things that were wrong, learned a lot of new things, and improved my own skill.

Even when my original answer had been correct, the challenges exposed weaknesses in the answer, and I learned how to communicate my answers in such a way that they prevented the challenges from having to be posted.

Most importantly, by this process, I found a number of my own blind spots. We can never see our own blind spots, unless someone else points out, directly or indirectly, what’s in them. (That’s what makes them blind spots, after all – we can’t see what’s in them.) By reading questions I never would have asked, or reading answers by others, or by being challenged on my own answers, my blind spots were exposed.

Later, I added presenting to a PASS group to my “learning by teaching” activities.

These days, I do a monthly lunch-and-learn for the software development department at my employer. Organizing the material so that I can communicate it clearly in that kind of time-limited format, really forces me to make sure it’ll be clear and will educate rapidly.

All of these ideas and anything similar, can really help you to advance your own skill. It’s also very, very enjoyable, all by itself. If you haven’t tried it, do.

Of all the lessons I’ve learned in my years as a DBA, all of the “I wish I’d known this when I started out”, I think this is the most important.

DBA Article: Documentation

A while back, I wrote a series of articles for beginning DBAs.  Not on the technical side of how to handle SQL Server, but on the subject of “things I wish I’d known when I was starting out”.

Here’s the first:

DBA Tips & Tricks: Document “Why” Not “What”

One thing I find in reviewing and refactoring older database code, that makes it much more difficult to figure out what to fix and how, is the documentation provided.

We’ve all run into situations where there simply isn’t any documentation, or it consists of “--TODO: Document this section” or something equally unhelpful.

Many DBAs and developers will even claim that “good code documents itself” as a justification for never getting around to documenting it. Sometimes this is seen as lazy, sometimes it’s seen as clever. What it really amounts to, in my opinion, is a complete misunderstanding of what documentation is about.

Here’s an example of what some might call “well-documented code”:


--smalldatetime fix
ALTER FUNCTION [dbo].[fDateFix] (@dateval DATETIME)
RETURNS SMALLDATETIME
AS
    BEGIN
    -- Declare Variables 
        DECLARE @sdate SMALLDATETIME
        DECLARE @edate SMALLDATETIME
        DECLARE @newdate SMALLDATETIME
  
    -- Set values
        SET @sdate = CAST('1900-01-01' AS SMALLDATETIME);
        SET @edate = CAST('2079-06-06' AS SMALLDATETIME);
  
    -- Check value of input, correct if needed
        IF @dateval IS NOT NULL
            BEGIN
                IF @dateval < @sdate
                    BEGIN
                        SET @newdate = @sdate
                    END
                ELSE
                    IF @dateval > @edate
                        BEGIN
                            SET @newdate = @edate
                        END
                    ELSE
                        BEGIN
                            SET @newdate = @dateval
                        END
            END
        ELSE
            BEGIN
                SET @newdate = NULL
            END
 
    -- Return corrected date         
        RETURN @newdate
    END


Each section says what it does. It clearly states that the function has the purpose of “smalldatetime fix”. The first part declares the variables, the next part assigns values to some of them, and so on.

This is a clear example of where “the code documents itself” is both true, and completely and utterly useless!

Why is it necessary to “fix” a smalldatetime? What are those “magical number” type dates that those variables are assigned to? Why does this function exist?

How about this, instead:

/*
   Purpose:    This takes DateTime input, checks if it is out-of-range
               for datatype SmallDateTime, and sets to the closest
               allowed value for that datatype if so.

               Use when tables are designed with SmallDateTime and inputs
               are not policed (free-form) in applications.
*/
ALTER FUNCTION [dbo].[fDateFix] (@dateval DATETIME)
RETURNS SMALLDATETIME
AS
    BEGIN
        DECLARE @sdate SMALLDATETIME
        DECLARE @edate SMALLDATETIME
        DECLARE @newdate SMALLDATETIME
  
        SET @sdate = CAST('1900-01-01' AS SMALLDATETIME); -- Minimum allowed SmallDateTime value
        SET @edate = CAST('2079-06-06' AS SMALLDATETIME); -- Maximum allowed SmallDateTime value
  
       -- Check value of input, correct if needed
        IF @dateval IS NOT NULL
            BEGIN
                IF @dateval < @sdate
                    BEGIN
                        SET @newdate = @sdate
                    END
                ELSE
                    IF @dateval > @edate
                        BEGIN
                            SET @newdate = @edate
                        END
                    ELSE
                        BEGIN
                            SET @newdate = @dateval
                        END
            END
        ELSE
            BEGIN
                SET @newdate = NULL
            END
 
        RETURN @newdate
    END


(Note, this function is one I copied out of a third-party application and is not intended to be an example of good code. It could obviously be improved by a blind man with a sledgehammer. It is used here simply to show the difference in documentation styles.)

Note that anyone reviewing the “what it does” version gets no help at all from the “documentation”. “Oh really? So this part declares variables? I would never have guessed that!”, isn’t something that one says without sarcasm.

But the “why was this written” documentation says things the code itself can’t say, and is useful to someone reviewing the code at a later time. It raises questions like, “Is there a comparable function that corrects from DateTime2 to DateTime? If so, why isn’t it a single function with the output type determined via a parameter?” And, “Why don’t they just police this in the application? Shouldn’t this be handled closer to the user, so they know that we’re just going to arbitrarily change their input?” And so on. Very useful to someone doing a refactor.

Documenting why changes were made, in the DDL scripts that implement the changes, can allow for a chronological study of either source control or a DDL log. Imagine the different between logging “Updates the table” type comments, vs “Per meeting with John Doe and Sue Smith on 1 Jan 2013, changed business rules to use UpdateDate column for the record instead of AuthorizationDate”. Suddenly, code changes that might otherwise be mysterious a year from now, are quite clear.

Keep this in mind when writing and reviewing code. In later years, you’ll be glad if you implement it.

If you have a good project ticketing system, “why” documentation can be as simple as “Purpose: See ticket DB-1103, notes from 3 May 13”. It adds a level of work to reading the documentation, since it’s not contained in the code, but allows for much richer “why” where that’s needed.

Ultimate SEO Fail

Did a Bing search for “search” and, not only does Google come up first, but Bing wasn’t even in the first seven results.

That’s Ballmer’s legacy, just like that.