Five Places to Document Before You Win the Lottery

When a project starts to go sideways and you’re cursing Naive Past You who signed off on the “aggressive” time estimates as you feed quarters into the vending machine in hopes that powdered donuts and honey buns won’t give you a heart attack before you hit the deadline, the last thing you want to talk about is documentation. Non-developers, if you value your life, observe the developer and her surroundings before you ask whether the documentation is updated. If you see any of the following…

  • Pyramid of Diet Coke or Mountain Dew cans
  • Pile of takeout containers (half-eaten because there’s no time)
  • Wild, bloodshot eyes
  • Caffeine-induced shakes
  • Sleeping bag and/or pillow

 …Back away slowly. DO NOT, under any circumstances, ask the developer if the documentation is up to date. You may lose a valuable body part before you can wedge yourself behind the file cabinet.

I love the beginning of a project. It’s full of resolutions and promises about how this time it’s going to be different. A quick run-down of statements I’ve heard (and said):

  1. “We’re building time into the schedule to keep the documentation up to date”
  2. “All of my time estimates will include some buffer for documentation”
  3. “QA will not sign off without documentation”
  4. “We’ve made accurate documentation part of the critical path”
  5. “We’ll document as we go”

What ends up happening? I can sum it up in 4 words: “We’ll fix it later”. But we never do. Then we leave and the next poor soul comes in blind and realizes that the documentation is either non-existent or woefully out of date. He gets to spend the next few weeks being indignant and self-righteous because the documentation is bad. He quickly forgets the undocumented tangle of code he left behind at his last job.

So, what exactly is documentation? Is it a necessary evil, a sacrifice to the development lifecycle gods? Is it yet another bit of unproductive administration work you have to do to check all the boxes on the project plan? Is it some antiquated legacy of waterfall methods? No. It is none of these things. Documentation, when done well, is a conversation between you and the next guy who comes along and looks at your work. You might not be there to explain why you had to do that weird thing in that stored procedure. You might not be able to tell the story behind the funky flat table you wish your name wasn’t associated with. You might not be able to sit down over lunch and talk about the wacky business rules you’re trying to enforce. But, your documentation will be there. More than anything else, this is your legacy.

Am I proposing that you create exhaustive documentation explaining every decision and line of code? No. That’s totally unrealistic, and I believe that it’s what gets us into trouble. Overwhelmed by the goal of perfect documentation, we end up not writing any documentation at all. I fall into the good enough camp in my approach. Even then, it can be a struggle to keep up. In that spirit, I have a few must-haves and guidelines for what and when to document. Also, I’m sort of a function over form kind of girl. If it works, do it. If it’s pretty, well, that’s nice too. Of course, I’m a developer. If you’re a DBA, your list will probably look different. But, I recommend the same basic approach. What are my must-haves and what are my triggers for a bit of explanatory prose? Or, you know, poetry… but if you have time to write documentation in iambic pentameter, you probably need a more challenging job.

Must-Haves

Data Dictionary – If nothing else, get the basics down:

  1. Entities and their purpose
  2. Attributes and their definition
  3. Business names for entities and attributes
  4. Explanation or examples of what data can end up in a column

Source to Target Mapping – if you’re moving data around, make a map

  1. Where does the data come from?
  2. Where is it going?
  3. What are the stops along the way?

Anything Manual – if you have to do anything out of process or manually, write it down

  1. Bob from Accounting asked you to run a “quick little report” for him in 2009. Somehow, you’re still running it every Monday
  2. You manually kick off that pesky job every Thursday evening instead of scheduling it
  3. You had to (gasp!) manually update data in the database

Other Guidelines

In-Line Documentation – (Stored Procedures/Functions/Scripts/etc.)

  1. Set up a nice little template inside any procedure/function/script that tells: Author/Date/Purpose/Major Revisions
  2. If you did something unconventional inside some encapsulated code, make a note of why you went this direction
  3. If some piece of code started out pretty and then grew horns and a tail and bought a pitchfork from Evil Processes R Us, explain what happened
  4. If you’re working around some limitation of the application/database/etc., say how and why

In Case I Win the Lottery Documentation (aka, In Case I Get Hit by a Bus)

    Imagine that you walk out the door this afternoon, and for some wonderful (not getting hit by a bus) reason, you never step foot inside your office again. You never log into the network again, and no one knows how to get in touch with you. What do only you know? Trust me, if you’ve been with an organization for any length of time, I can guarantee that you have some proprietary knowledge. This can be the least formal of all. Just make a list of things you hope someone else knows how to do when you’re sitting in Tahiti sipping fruity drinks, ogling the very cute cabana boy as you ask him to bring you yet another extra towel.In our little SQL Server community, we talk a lot about being part of the community. (I know… totally Meta) Documentation is a great way to be a good citizen inside our world. Chances are someone you know, or someone who knows someone you know, will be coming into an organization after you leave. Helping the next guy ramp up more quickly and avoid your early stumbles is just about the best you can do to support your fellow database professional.

Noir SQL… Or, a Hardboiled Approach to Getting the Job Done

You can tell a lot about my state of mind by the books I’m reading. Lately, it’s Urban Fantasy with a Noir feel to it. Specifically, I’m reading Mike Carey’s Felix Castor series, and I just finished a book by Richard Kadrey called Sandman Slim: A Novel. I love the anti-hero. The protagonist who is gritty and dirty and has a few great scars is my kind of guy. He unapologetically breaks the rules and isn’t all, “it’s more about the journey than the destination.” For him, destination is what matters, no matter now you got there.

Lately, I feel a bit like the scarred anti-hero. I’m doing some things in a production environment that I’m not totally thrilled about, and I wish I could stop the line and do things the “right” way. I want to use SSIS to transform data. I want to encapsulate processes into neat, repeatable, parameterized modules. But, you know what? When there’s a same-day turnaround on a request, you make do. You go a little Noir on your T-SQL, know what I mean?

I want to show you two things that I’ve actually done in the past few weeks. No, given a nice, neat environment, this SQL might never have been written. Am I proud of it? Well, yes. Yes I am. At the end of the day, I got the customer what he needed. Was it pretty? No. I’m cool with that. Being the anti-hero is kind of fun every once in a while.

Fixed-Width Output

I needed to give a guy a text file in fixed-width format. I had a process from my predecessor that just wasn’t working. The file was already late. So here’s what I did. I’m using the AdventureWorks database to show an example.

SELECT
	LEFT((ISNULL(Title,'')+SPACE(50)), 8)+
	LEFT((ISNULL(FirstName,'')+SPACE(100)), 20)+
	LEFT((ISNULL(LastName,'')+SPACE(100)), 30)+
	LEFT((ISNULL(MiddleName,'')+SPACE(100)), 5)+
	LEFT((ISNULL(EmailAddress,'')+SPACE(100)), 35)+
	LEFT((ISNULL(Phone,'')+SPACE(100)), 25)
FROM AdventureWorks.Person.Contact	;

The result:

Paste it into Notepad and see how it looks:

I save the text file and send it on. Pour myself a whiskey, neat, and light up an unfiltered Lucky Strike.  Okay, not really, but you know what I mean. 

A quick run-down:

ISNULL: If any of the values I’m concatenating are NULL, then the entire string will come back as NULL. I wrap all of my columns in ISNULL like so:

ISNULL(Title, ‘’)

This sets the value to an empty string if the value is NULL.

SPACE: This handy little string function will pad the given number of spaces onto the result you return. I want to make sure I end up with enough padded spaces to fill out the fixed-width portion of that column. So, I pad the output:

ISNULL(Title, ‘’)+SPACE(50)

This will give me the output from the Title column, plus 50 spaces.

LEFT: Now, not every value coming out of the database is going to have the exact same number of columns. So, I use the LEFT function to trim it down to the exact length I want. LEFT will take the left-most number of characters you tell it to. If I say,

LEFT((ISNULL(Title,”)+SPACE(50)), 8 )

I’m telling it to give me characters 1-8 that are returned. Since I’ve padded my output with spaces, it’ll be the result from the column, plus as many spaces as I need to pad the output to 8.

Pretty? No. Functional? Yes. Noir SQL? Absolutely.

Remove Unwanted Characters

Next up, I have a source file I use from another department. It comes in Excel format, and includes a phone number. I’m supposed to get something that looks like this: 1112223333. Nice, neat, simple. What do I get? A hodge-podge of phone number formats. I’m looking at something like this:

CREATE TABLE PhoneNumber
(
	PhoneNumber varchar(50)
); 

INSERT INTO PhoneNumber(PhoneNumber)
VALUES
	('1112223333'), ('(111) 222-3333'), ('111-222-3333'), ('111 222 3333'); 	

SELECT PhoneNumber
FROM PhoneNumber

Okay. So I need to clean these numbers up quickly. Destination, not journey, my friends. I’m the anti-hero. I import the data into SQL Server using the Import/Export utility so I can manipulate the data. Then, I run this nifty little REPLACE statement:

SELECT PhoneNumber,
	CASE
	WHEN ISNUMERIC(PhoneNumber) = 0
		THEN REPLACE(
			REPLACE(
				REPLACE(
					REPLACE(PhoneNumber, '-', ''),			--Strip out dashes
				' ', ''),							--Strip out spaces
			')', ''),								--Strip out close parenthesis
		'(', '')									--Strip out open parenthesis
		ELSE PhoneNumber
	END as FormattedPhoneNumber
FROM dbo.PhoneNumber

Check out the results:

Sweet. It’s quick, it’s dirty, and it saved me having to wait on the source data provider to clean things up on his end. I turn the query into an UPDATE statement, and I’ve got clean data to import.  Again, a run-down of the functions:

ISNUMERIC: Tells me whether the value I’m passing is qualifies as a number or not. NOTE: It recognizes hexadecimal as a number, so use carefully. I set up a CASE statement that asks if the value is numeric. If it is, that means I don’t have any characters like “(“, “)”, or “-“ in there. If not, I apply a nested REPLACE to the value.

REPLACE: Replace is awesome. I can say something like this: REPLACE(PhoneNumber, ‘-‘, ‘’). This is saying that if I find a dash, I want to replace it with an empty string. What’s really cool is that I can nest them. So, I can tell it to remove the dashes, then the spaces, then the open parenthesis, and finally the close parenthesis in one statement.

Bottom line: Sometimes things just have to get done. The difference between an anti-hero and a true antagonist is that we anti-heroes know to go back and do things the right way as soon as we get a moment to breathe. In the meantime, don’t apologize for leaving behind a few unmarked graves when you need to get the job done. We’re anti-heroes. We have the scars to prove it.