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

AaronBertrand — Tue, 12 Jul 2011 11:44:00 GMT

T-SQL Tuesday, the invention of Adam Machanic (@AdamMachanic), is what he calls a recurring, revolving blog party. Each month, a new host picks a topic, invites bloggers of all levels to join in, and then after all the submissions are in, writes a summary of all of the entries. This encourages people to participate in the community more frequently, sometimes helping them get their feet wet in a topic they're not already familiar with, and I suspect that everyone involved learns a little something each time.

This month, T-SQL Tuesday #20 is hosted by Microsoft's Amit Banerjee (@banerjeeamit), and the topic he chose is T-SQL Best Practices. I like this topic choice because I have long been a proponent of using positive, consistent practices and kicking all those bad habits. I put out a number of "bad habits" posts here about a year ago, wrote a compressed chapter on this topic for the upcoming SQL Server MVP Deep Dives Volume 2 title, and will be presenting a similarly-themed PASS Summit 2011 session. It's certainly easy to point out bad habits, like SELECT *, and encourage folks to stay away from them. I think it's equally easy to point out some *good* habits that people can incorporate into their coding mantra, whether they be for performance reasons, readability / maintainability reasons, or both. So, my T-SQL Tuesday entry is going to list out a few of these good habits, and explain why I think you should be making them part of your natural coding style.

Always use statement terminators

I wrote about this back in 2009, where I explained that CTEs (starting in 2005) and MERGE (starting in 2008) require the previous statement to be properly terminated with a semi-colon. I also explained that Microsoft will eventually not allow statements without a proper terminator at all (though I admit). SQL Server "Denali" introduces a new command which requires the previous statement to properly terminated: THROW (I wrote about this back in November).

As more and more keywords are added to Transact-SQL, you will find more and more cases where this requirement will be in place. I suspect that we'll all be tired of the syntax error messages and be doing this instinctively long before Microsoft makes it mandatory, but why not start now? As with many other habits, I don't advocate going back and correcting your entire codebase. But if you're refactoring code (for example, to move from RAISERROR to THROW in Denali), and you add those semi-colons on the statements that now precede those lines, you may as well add all the semi-colons applicable to the stored procedure.

Always set isolation level at query level, rather than as a table hint

On just about every system I touch, I see NOLOCK being used quite liberally, peppered onto every table in a query. In and of itself, the use of NOLOCK is okay - I give folks the benefit of the doubt, and assume that they have calculated the risk they're taking by using READ UNCOMMITTED (though I will often query why they aren't using snapshot, both out of curiosity and to perhaps plant a seed that might otherwise not be there). My point in the context of this post is that there are a couple of particular pitfalls to using NOLOCK at the table level. One is that the following syntax is often used:

FROM dbo.table NOLOCK
-- instead of
FROM dbo.table WITH (NOLOCK)

Without the WITH keyword, this can actually interpreted as a table alias (e.g. AS NOLOCK) instead of a table hint. Furthermore, if you have a complicated query against a lot of tables, and add WITH (NOLOCK) to each one, you're creating a lot of work for yourself if you later decide to transition to snapshot (hint, hint). Why not set the isolation level using:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

...which is much easier to change later should you need to, prevents you from missing the hint on one or more tables buried in your query, makes the query itself more readable, and better documents the actual intention of the code. Plus, if someone is on a mission later to change everything to snapshot isolation, they will likely just throw the SET statement at the beginning of each procedure. If there is already a SET statement at the top, they can override it, instead of having to pore through the code and remove every single NOLOCK...

Always prefix stored procedure calls with EXEC / EXECUTE

I see a lot of what I'll call "lazy" stored procedure calls. People write their applications or scripts with the following:

sp_who2;

Then they execute their code from their application (or maybe call it in a different way), and are surprised to suddenly see this error, which they don't see in Management Studio:

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'sp_who2'.

The reason for the error is that, while you can call a stored procedure without an explicit EXEC when it is the first statement in the batch, you don't always have control over what *else* might be sent in the batch (SET commands, for example). I see this problem burn people quite a bit, so do yourself a favor: always prefix your stored procedure calls using EXEC or EXECUTE. At the very least, it prevents you from having to change your code when you do want something else to happen before that statement in the batch.

Always use schema / table prefix

Granted, I wrote about a portion of this topic in my "Bad Habits" series, but from the opposite angle ("stop avoiding the schema prefix"). As the use of schema becomes more common, I see more people getting confused when their object is not found or when the user complains that they are getting permission denied errors. Even if you only use the dbo schema for now, I strongly recommend getting into the habit of always properly schema qualifying your objects - whether at creation time or at query time.

Another thing I see a lot is the inconsistent use of table/alias prefix when referencing columns in a query. While it may be obvious to you which table a certain column came from, it is not going to be obvious to everyone reading the query - especially when they're troubleshooting an issue in real time. Using a sensible alias on each table name in the query will allow you to add an alias prefix to every column (both in the SELECT list and in the other clauses) with relatively little effort/ In turn, this will make your query more readable for everyone, and might even prevent errors later should the schema change.

Consider developing on a case-sensitive database

This is probably going to be my hardest sell here, but I think it can be very important to become disciplined in using consistent casing for object names, column names and data. Why? Well, you never know when your next deployment may target an instance that has a case sensitive collation - particularly if you distribute databases or T-SQL code as an ISV. IntelliSense (whether native or 3rd party) can assist with this greatly, making correct casing virtually automatic, but even when using this assistance there can still be many instances where you inadvertently (or even knowingly) decline the suggested code. While this may cost you a bit of development time up front, it will save you a lot of time later if you end up deploying mixed-case code to a case sensitive server.

So, if you typically develop using a case insensitive collation (e.g. Latin1_General_CI_AI), consider using the same core collation, but with the case sensitive option instead (e.g. Latin1_General_CS_AI). You could go a step further and say, "well, why not develop on several foreign language collations as well?" You might want to do that too, but I'm going to limit my advice to the more common scenario - where you are developing for the more common Western languages.

Above all else, use a consistent coding standard

In the end, it doesn't really matter that much whether you adopt some or all of the above practices - no matter how blue in the face I may get telling you that there are good reasons behind them. What is truly important is that you strive to develop consistent code, and understand the side effects of *not* choosing to implement some of these habits. Setting up a list of general rules for your team, and consistently following your own standards with no exceptions, can go a long way in streamlining development in the long run, and troubleshooting issues when things go wrong.

Search results matching tags 't-sql tuesday' and 'readability'

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