Just my 2 cents on this...
Jeff Moden (9/21/2012)
6. When commenting SQL code, use block comments instead of line comments
The following isn't a bad suggestion but, unless you ccome up with a reassonable standard snippet of code for use in the CATCH block, you're going to have an awful lot of errors that no one can determine the origin of nor even what the error actually is.
8 USE TRY...CATCH instead of @@ERROR for error handling
This is a great idea but has somme nasty caveates depending on which ORM you may be using. For example, if you create a VARCHAR(10) column and pass filters to queries through Linq2Sql, Linq2Sql will convert the filter to NVARCHAR(10) in the code it executes. Since NVARCHAR has a high data precedence than VARCHAR, the column being filtered will first convert to NVARCHAR which destroys any hope of getting an INDEX SEEK out of the code. Depending on how they might be used, I also have a personal dislike for BIT columns. For example, it's sometime much more expedient to SUM a flag column to get a count of rows where the flag is set and that's just not possible with the BIT datatype. I've also see certain types of columns that really should be TINYINT for future expansion (think "bullet proofing" code) that some might not consider at design time.
1. Use the smallest data type appropriate to the data you're storing
The following two suggestions seem to form a paradox. I'll also just about guarantee that Item 3 will be severely abused.
2. Keep tables narrow
3. Do not blindly normalize everything
They fixed it in 2012 but I recommend that you recommend that MAX datatypes should be avoided unless they are absolutely necessary. I know that goes along with using the smallest datatype possible but using MAX in anything less than 2012 has some real index maintenance issues with rebuilding indexes in an ONLINE fashion.
I'd specifically specify that you should make the clustered index UNIQUE along with the following because it has some very real performance implications.
4. Make clustered indexes monotonically increasing whenever possible
You might want to specify that you shouldn't blindly make indexes because they can really bog down inserts, updates, and deletes.
The following should probably be changed to "DO NOt USE QUERY OR TABLE HINTS WITHOUT PRIOR APPROVAL FROM THE DBA FOR EACH USE!".
7. Use Query hints sparingly
The following is a pretty bad misconception. It truly depends on what is being done for performance purposes. Both temp Tables and Table Variables start out in memory and both overflow to disk if they get too big. Table Variables are also evaluated as having just one row unless you do a recompile after they're loaded and they do not use statistics at all. Further, sectional troubleshooting where non-persistent data tables exist is a real bitch in SSMS. Consdier rewording this to say something like "Use Temp Tables in favor of Table Variables except for when the use of Table Variables provides a substantial increase in performance or the code doesn't work because of the Temp Tables when being called from external sources."
3. Use table variable for small data sets and temp tables for larger ones
The following is a bit confusing. I believe what you mean to say is to not intermingle DDL within the DML of a stored procedure.
1. Do not use both DML and DDL on temp tables within a stored procedure
--Jeff Moden
Change is inevitable... Change for the better is not.