This is a very useful article, thank you. I do have some comments regarding the Standards for T-SQL Coding
document. There is no doubt that existence of such document is fully justified for a successful implementation of any project, but I believe that you should consider adding a small disclaimer after the link to your "sample of one such document
" stating that this is just a sample which by no means is intended for anybody’s serious consideration. This is because the sample document is ridden with quite few questionable and even plain wrong recommendations. Your article is read by SQL Server Central subscribers of all levels, and a small disclaimer could be useful. Here is the small list of examples which I believe maybe considered as questionable by many:
"System level stored procedures are named using a prefix 'SP__' (two underscores)".
This is still ill advised despite the two underscores IMHO.
The comment about the text data types:
"There are a lot of bugs associated with replicating tables containing text columns. So, if you don't have to store more than 8KB of text, use CHAR(8000) or VARCHAR(8000) datatypes instead."
This is just plain wrong because the page size in SQL Server is 8K, the maximum number of bytes of in row data of a single record is 8060, and while SQL Server engine will allow you (with warning) to create a table which can potentially have records wider than (if you opt for varchar(8000)), maintaining a table like this is a recipe for disaster because this table will be subjected to occasionally (not always) failing to insert and/or update some of its records.
"IDENTITY columns can run out of numbers at some point, depending on the data type selected; numbers can't be reused automatically, after deleting rows; and problems may arise if you are using replication. So, come up with an algorithm to generate a primary key in the front-end or from within the inserting stored procedure".
Even Oracle developers don’t use this approach any more as they have nextval function to their disposal to retrieve the next available value from existing sequence. With SQL Server there are many different ways to deal with issues such as filling the holes after deletes (if one perceives this as an issue) and setting up seeds for replication to still work flawlessly.
"Minimize the use of NULLs, as they often confuse the front-end applications, unless the applications are coded intelligently to eliminate NULLs or convert the NULLs into some other form."
If developers are not capable to intelligently code the applications then it is too bad for them, but still DBA should not just nod and start creating a whole bunch of defaults to insert empty strings, zeroes and 9999-12-31s in place of respectable null values.
Lack of a small disclaimer aside, the article is great. I really like the Microsoft Business Intelligence Project Booster Kit section.