TSQL Tuesday #20: T-SQL Best Practices

This month’s TSQL Tuesday is hosted by Amit Banerjee ( blog | @banerjeeamit ) and topic is TSQL Best Practices.  Now, I have never taken a T-SQL class, and I am not a database developer.  I have spent minimal time with anyone, one on one, reviewing the “right way” to write T-SQL code.    I understand the difference between a set-based and a cursor-based approach. I can declare variables, RAISERRORs and loop.  In the end, I can get the data I need, and change the data I need to change.  But I don’t, at the very core, understand all I need to know (fundamentally) to write efficient T-SQL.

I can however, comment the hell out of anything.  Thus, my T-SQL best practice is: Comment your code…please.

I have learned this lesson the hard way.  My team is often engaged to write scripts for customers to move data, change data, fix data, etc.  Sometimes we only spend a few days developing, testing and implementing the script.  Sometimes projects drag on for weeks, even months.  It’s the ones that span multiple months that give me headaches.  How on earth am I supposed to remember why I wrote this particular select statement?  Why am I using a temp table here?  In fact, what is it I’m supposed to be doing again?

I admit, I have had to ask a customer what the script is supposed to do.  I dug through emails, tried to find notes in a folder, and I finally have to give up and ask.   It’s not a recommended way to impress or inspire confidence.  And so after that one incident, I started to seriously comment my code.

I start with a template for scripts, courtesy of this post by Buck Woody ( blog | @buckwoody ).  Any script that our team creates that executes in a customer environment has this type of information, including a reference to a support issue ID or a software fix.  For straightforward scripts, I might include all information in the header.  For more complicated scripts, I will add comments before certain statements, or for certain sections of the script.  Comments are not prefaced with two hyphens (–) but rather within forward slashes and asterisks (/* */).  I will use two hyphens before certain DML statements.  I distinctly remember our Senior VP of Development suggesting this trick when I first started at the company.  We were dialed into a customer trying to resolve an issue and I was still fairly new to SQL Server.  He reminded me that if you accidently press F5 (or click the green arrow) without any text highlighted you will run all the statements in the script.  Heaven forbid you had an INSERT, UPDATE or DELETE statement in there that you didn’t want to run yet (or hadn’t tested, or had the wrong where clause).

Most of the scripts I write are not fully automated, and it’s because I’m modifying data.  I take my time, check my counts and verify my script is doing what it should based on my comments (I also include notes of how many rows should be changed, and how long it took to run in the Test or Development environment).

One area that I still need to improve is my formatting, which is ironic because as much as I love comments, I love formatting even more.  I attribute this to my type A personality.  I can spend hours getting to a graph or table to look “just so” in Excel.  I will fight Word to the death to get my numbers and bullets to line up correctly, and have the right headers in each section so my Table of Contents is perfect.  As for T-SQL, I guess I haven’t found my style yet (and if there’s a definitive reference that states how T-SQL should always be formatted, feel free to let me know.  From what I have seen, a lot of people follow some kind of standard (UPPERCASE reserved words, indent at BEGIN, etc.), but I haven’t seen the T-SQL Formatter’s Bible on Amazon…yet…

/*

Name: TSQL2sday_SampleScript.sql

Author: ELS

Created: 06/03/2011

Last Updated: 07/11/2011

Purpose: Display statistical information and load data for demonstrations

Usage: Before executing, verify there's a recent backup.

Support Issue ID/Software Fix: N/A

Supported Versions: SQL 2008, SQL 2008R2

*/

USE AdventureWorks
GO
/*
view the stats header
*/
DBCC SHOW_STATISTICS ("Sales.SalesOrderDetail",IX_SalesOrderDetail_ProductID) WITH STAT_HEADER

/*
load in some data for testing
(file generated by RedGate Data Generator in case needs to be recreated)
*/
BULK INSERT AdventureWorks.Sales.SalesOrderDetail
FROM 'C:\SQLStuff\Statistics\Data\sod.txt'
WITH
(
DATAFILETYPE = 'native',
TABLOCK
)

/*
delete some data (testing only)
*/
--DELETE FROM Sales.SalesOrderDetail WHERE SalesOrderDetailID > 121317

/* End TSQL2sday_SampleScript.sql */