Do use templates to expedite the creation of standardized stored procedures.
Comments: Program Name: Programmer: Date Created: Purpose: Dependency: History of Changes:
Do use a prefix like EraseMe or DeleteThis for objects that you want to delete, but would like time to "wait and see".
Do code your assignment statements within the Set clause of an Update statement as follows:
All variable assignments first, because that is the order in which they are processed regardless of the order listed.
Followed by all column assignments.
NOTE: Variable assignments are executed in the order listed and their new values become available to subsequent assignment statements. Column assignments do not become available to subsequent assignment statements. This makes it possible to swap the value of two columns without a temporary holding variable.
Do use the "FROM Table1 INNER JOIN Table2 ON" construct rather then "FROM Table1, Table2" construct. See FAQ183-5168 for a detailed discussion.
Do not use varchar fields for dates. SmallDateTime is sufficient for most purposes.
Do not use @@Identity. Use Scope_Identity( ) instead.
Do not enter multiple pieces of information in one column, e.g. full name. Use FirstName, LastName.
Do not use the double = construct in the Set clause of an Update statement, i.e. ColName=@Variable=@Variable+1. This construct does fail to operate consistently when used with additional assignment statements.
Avoid the use of cursors. They are slow and very rarely necessary.
Avoid the use of triggers. When you must use them, be sure that you have allowed for multiple inserts, deletes or updates.
Avoid the use of user definded functions. They are slow, esentially eliminating the advantage of a set-based solution.
Avoid the use of dynamic SQL. There may be times when it is appropriate, but dynamic SQL doesn't allow the optimizer to pre-compile the procedure. If the user provides input to the dynamic SQL, then there is the very real possibility of SQL injection attacks.
Thanks again to all those who contributed to this FAQ. If you have further suggestions please forward them to me.