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

T-SQL Tuesday is a monthly SQL Server blogging event.  One of us sets a topic for the month’s event, and we all have a week in which to write a blog post that fits that topic.  This month’s event is hosted by Amit Banerjee (blog|twitter), and he’s chosen the subject of “T-SQL Best Practices”.

Make Use of Free Reference Books

I would recommend that all T-SQL developers go to the Red-Gate bookstore (whose page is still not easily accessible…) and download and read (at least) the following:

  • The Red-Gate Guide to SQL Server Team-Based Development, by Phil Factor, Grant Fritchey, Alex Kuznetsov, and Mladen Prajdić
  • Defensive Database Programming, by Alex Kuznetsov

They’re available as free PDF ebooks, or you can buy paper copies from Amazon.  I should probably add a disclaimer saying “other free ebooks are available”…

My advice?

  • Test your code.  Don’t just test it in a small development environment, make sure you’ve got a production-scale QA or UAT environment in which to test as well.
  • Return only the data you need – don’t use SELECT * unless you intend to use all fields; use a WHERE clause to filter down to just the records you intend to display
  • Write your queries to make use of indexes.
  • And please DOCUMENT YOUR SYSTEMS as well as your code.  You have a Linked Server?  Document it – why it’s there, how long you’ll need it, what it’s used by.  Just remember, you might not be around in 5 years time when some poor sap has to migrate your lumbering behemoth onto a fresh platform.

I found this quote courtesy of Jeff Atwood (blog|twitter) a while ago, and it has stayed with me since:

Always code as if the person who ends up maintaining your code is a violent psychopath who knows where you live.

“Coding for Violent Psychopaths”, based on a quote found at C2’s Wiki – “Code For The Maintainer”

EDIT: Found the original Usenet post by John F Woods on comp.lang.c++

This entry was posted in SQLServerPedia Syndication, T-SQL Tuesday and tagged , , , , . Bookmark the permalink.

1 Response to T-SQL Tuesday #20 – T-SQL Best Practices

  1. Pingback: T-SQL Tuesday #20 Wrap-up and a few smiles « TroubleshootingSQL

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.