Real nice article, Steve, especially on the importance of properly commenting code and the inference that it should also be properly structured (first letter of SQL, right? :D) My rule-of-thumb for comments is that if you remove all the code, you should be able to build a functional flowchart from the comments. There's nothing worse that being given a 1200 line stored procedure to troubleshoot with no or inadequate comments (e.g. "Update the Customer Table" as a comment is totally inadequate).
I'll also stress the importance of the "flower box" header. We actually keep a revision history of changes in the header. If nothing else, it tells you who the previous SME's are so that you can hunt them down and pick their brain if you need to. We also have a section in the header called "Usage Examples" where there's a lick of code that the person doing troubleshooting can use/modify to run the stored procedure for something that is known to work instead of trying to figure out what all the possible values should be for a 15 parameter bit of code.
Last but not least, you can have a ton of external documentation for the code but, when all else fails and all that documentation is lost (and frequently is unless you have the "documentation religion", which most shops don't), the documentation in the form of well written comments is sometimes all there is and is usually better than frequently unmaintained external documentation.
I also agree that any code that can execute the statements should always have SET NOCOUNT ON and SET XACT_ABORT ON. They're virtually free and if you get into the habit (or template) of always using them unless they would do something contrary in the code (and should still be included even then but commented out with a comment as to WHY they must not be used).
Last must not least, you might want to avoid the use of @@IDENTITY in favor of the SCOPE_IDENTITY() function.. 😀 We have that in our coding standards.
Again, nice high-level article on some of the important things about code in SQL Server. Thanks for taking the time to write it.