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.
is pronounced "ree-bar
" and is a "Modenism
" for R
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
"If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
"Change is inevitable... change for the better is not."
When you put the right degree of spin on it, the number 3|8
is also a glyph that describes the nature of a DBAs job. 😉
How to post code problems
Create a Tally Function (fnTally)