Resources for SQL Server code review and best practices

Question

Are there any good resources out there for T-SQL coding standards?

Answer 1

People that we contract to write code for us must adhere to our standards in both DB and application coding. If they are providing us with an off-the-shelf product, then we have no say over their standards.

Answer 2

Check out this excellent resource:

SSW Rules to Better SQL Server Databases

This is also good, although some of the advice may have changed since the article dates from 2001):

SQL Server TSQL Coding Conventions, Best Practices, and Programming Guidelines

Answer 3

I was a developer for an ASP.NET application and my manager required me to submit my SQL statements to the DBA for review. What I did was to consolidate all SQLs used in the application to one module file. (VB .NET module with readonly strings)

Just to name a few mandates, off hand.

1. All SQL statements must use parameterised queries. This is a good practice. SQL injection is not possible when parameters (aka bind variables in Oracle) are used. Some reported a significant performance increase in using bind variables. This is especially true for Oracle. Not sure for MS SQL

E.g. use "SELECT username FROM user WHERE userid = @userid" instead of Dim sql as String = "SELECT username FROM user WHERE userid = {0}" sql = String.Format(sql, userid)

1. "SELECT *" should not be used. Columns must be explicitly named.

2. JOINS should be used instead of NESTED QUERIES whenever possible.

3. Reduce the use of VIEWS as this will impact performance. (This is controversial) My manager went to the extreme to forbid the usage of views. We will developing something which performance and scalability is of more importance than readability of codes.

Answer 4

For SQL coding standards, your best bet is to search for what others have written. There are several resources containing standards that various people have published. You are unlikely to find one that will completely fit your organization. Plus, some have standards that IMHO are just plain wrong. Your best bet is to read through the documents you find and extract the concepts and rules that make sense and fit your organization. Some standards may be overkill, like how to indent the code. It depends on how strict you want the standards to be. Here are a few examples:

http://www.nyx.net/~bwunder/dbChangeControl/standard.htm

http://www.SQLAuthority.com

http://www.SQLserverPortal.com

You'll have to look around at links two and three as I don't have the exact URLs handy. Also checkout the link posted by Mitch Wheat above. These are just some examples, but you'll find more by searching.

Answer 5

I have either contributed to or implemented coding practices for SQL server in several organizations. You can spend days researching what others have done but and you can probably use pieces but I find each environment to be completely unique.

At a high level...I would suggest separating function from form as much as possible. What do I mean? There are some best practices that can be tested and documented to your specific environment and application such as when to use temp tables over large queries, no lock, dynamic sql usage, query hints, configuration. These can totally vary depending on hardware and use. Then there are other standards that are more opinion based: naming conventions, use of schemas, procs, views, functions, version control, etc. The latter group can get pretty political - really political. It is also a good idea to start small - implement a little at a time.

On outside vendors I have found it impractical to influence until there is a performance impact (ex: explicit query hints that cause huge table scans). Then it is most effective to provide data and get them to patch it. If there is some sort of service contract I don't see how you can enforce practices. Note that they may be writing for multiple versions and/or platforms and want the code as flexible as possible.