The best known principle for sql development is any production sql code must be thoroughly reviewed by developers, administrators and other relevant experts. No major sql code changes should be deployed without benchmark testing. This is usually known and followed because everybody understands that carelessness with database code may lead to a crash or comlete performanse exhaustion of the system.

 But many architects/managers usually forget about prevention sql issues. These preventing approaches relate to SQL Code Standard and SQL code Checklist. Enterprise must have these documents in the top of database development, these documents may be merged into a single SQL code document, but it must exist in some way.

SQL Code Standard talks mostly about the way things should be implemented and SQL Checklist - about constructions and approaches that should be avoided.

SQL code Standard

SQL Code Standard for enterprise can look like the following list of requirements

• All indentations must be saved as spaces not tabs
• Indentation must consist of 4 spaces
• Every nested logical unit must begin with new indentation
• all T-SQL reserved words must be lowercase
• Even though sql server is case-insensitive - all ojbect names must be typed as if it were case-sensitive (saving original letters case)
• Any stored procedure creation script must include drop procedure part in if exists clause
• Header of any object creation script must include information about author, creation date, purpose description etc.
• continue this list basing on your enterprise's needs

SQL code checklist

• If there is UNION (not UNION ALL) there should be a clear reason. UNION ALL is much faster.
• Every join should be if possible inner join.
• Except some rare particular cases joining and filtering columns should be indexed. Try to add a hint or additional clause to use any existing index if scan is hard. You should make sql server use an existing index.
• Standard for transactions within stored procedures. Each call of sp or data modification statements should be analyzed on @@error and @retval, appropriate @errormsg generated. For SQL Server 2005 and SQL Server 2008 this item may concern try-catch usage
• Every object should have a schema (dbo.) prefix. Most of tables are used in nolock mode. Usage of isolation level higher than read committed should be described in details.
• If a developer is interested in existence of some particular row within a table he or she should use if exists statement, not a select into a variable and then analysis of variable for NULL.
• After each hint should follow description why it is used.
• It is standard to not use trigger logic in very high loaded OLTP systems except particular cases.
• If inner join on clause has something like on a.id = b.id and a.value = @value the second part should be transferred from on to where clause where a.value = @value.
• Every table must have a Primary Key.There should be a reasonable explanation of heap usage
• Fillfactor for clustered index is 90, for non clustered – 70
• All FKs should have option NOT FOR REPLICATION
• All default and other constraints should be named.
• Condition in where clause like a.b = @value or @value = 1 should be analyzed in execution plan. May be it is worth splitting into 2 queries with if @value = 1 else usage.
• New indexes or new tables with very few indexes should be questioned and analyzed.
• There should be important reason to use varchar columns greater then 1000 symbols.
• In update or insert statement if we calculate field value as concatenation of many string it should be converted to the native field size. Update a set b = CONVERT(varchar(100),@a + @b)
• Set nocount on must be specified in the beginning of each stored procedure