Microsoft SQL Server/Best Practices

• Always qualify objects by owner.=
• Use query "with (nolock)" when you don't require high transactional consistency.
• Do not use GOTO.
• Avoid CURSOR use because it's significantly slower. If necessary, always declare the correct type of cursor (FAST_FORWARD).
• Avoid SELECT INTO for populating temp tables. Create the table then use INSERT SELECT.
• Always use ANSI join syntax.
• Always check for object existence.
• Use SCOPE_IDENTITY() instead of @@IDENTITY.
• Always check @@TRANCOUNT and commit/rollback as necessary.
• Order DML to avoid deadlocks.
• Always check @@ERROR and @@ROWCOUNT by assigning to a variable.
• Always check sp return values.
• Do not create cross-database dependencies.
• Avoid table value UDF – performance problems.
• Avoid dynamic SQL – if necessary use sp_executesql over EXEC.
• Avoid using NULL values.
• When there are only two values, ISNULL is more efficient than COALESCE.
• Always specify columns; try to avoid "SELECT *". Exceptions include these two cases: "WHERE EXISTS (SELECT * ...)" and aggregate functions.