Microsoft SQL Server/Best Practices

Query optimization 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 Data manipulation language 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.