T-SQL Tuesday

T-SQL Tuesday #20: T-SQL Best Practices

Logo for T-SQL Tuesday

T-SQL Tuesday

The topic for T-SQL Tuesday the Twentieth revolves around best practices. Wait: not important enough. Best Practices. The big BP. Not that BP. Today’s topic was proposed by Amit Banerjee ( Blog | @banerjeeamit ).

Code Clarity

Here’s a couple of examples of the way that I write T-SQL.

CREATE TABLE People.Persons(
	PersonID		int			NOT NULL	IDENTITY(1,1)	PRIMARY KEY
	,FirstName		varchar(20)	NOT NULL
	,LastName		varchar(40)	NOT NULL
	,DateOfBirth	date		NULL
)
 
INSERT INTO People.Persons(FirstName, LastName, DateOfBirth)
VALUES
('Matthew','Velic','19831013')
 
SELECT p.FirstName + ' ' + p.LastName AS [Full Name]
	,p.DateOfBirth AS [Date of Birth]
FROM People.Persons AS pp
WHERE p.DateOfBirth = '19831013'

You may notice a few things about my style:

  • I use AS when declaring any alias, and my table aliases tend to be initials.
  • I try to format my statements into nice, tabbed columns.
  • When inserting data, even across all columns of a table, I still declare my column names.
  • Although I had not joined, I take the time to write INNER and OUTER in my joins.

Why go through all this trouble? Because it helps to clarify my code for the person coming in behind me. The person whose skill level I don’t know. The person who may not realize all the optional T-SQL declarations or that whitespace isn’t significant.

When database duties landed in my lap, I didn’t know these things. I began to review all this code on our server, and it was a mess. The DBA before me didn’t use AS with any aliases. He used a single letter when defining table aliases. Queries were written across single lines of text with little vertical break down for readability’s sake. As I tried to wade through the bog of poorly written code, I used SQL Server Books Online as my reference.

I grew more lost.

Don’t Make Me Think

I never think!

There’s a wonderful book, Don’t Make Me Think, by Steve Krug. While it pertains to web usability, especially in regards to design, and to how users browse the web, I think the book makes a strong argument overall. Every extra millisecond I have to think about some “clever” design choice is an extra chance that I’ll simply browse away. It’s a lost opportunity. In SQL Server terms, every extra millisecond that I have to decipher some “clever” script is wasted brain power.

Getting back to my predicament as a new DBA: many of the examples in Books Online follow a strict standard of coding. Aliases are typically declared with an AS. Queries have nice vertical space, and resemble squares more than lines. When trying to compare Books Online to the SQL I had in front of me… it was as though I were comparing night to day.

I know how to write and read scripts when the optional T-SQL is absent. But it was an experience to have to puzzle it out. The smallest thing I can do for those coming after me, the thing that doesn’t take me more than a couple of seconds per script, is write clear T-SQL that follows the general format of the examples in Books Online.

I’m not saying that Books Online is or should be the end-all for formatting. If your team already has a prescribed manner of writing that is well documented, run with it! But for smaller shops and for consultants that may be on the job one day and handing off your code the next, I think code clarity helps in the long run. And it just might help that newbie on the day that the database duties land in her lap.

Standard