Wikibooks: Microsoft SQL Server/Best Practices

• Format: Book
• Language: English
• Subjects: DML
• Online Access: https://en.wikibooks.org/wiki/Microsoft_SQL_Server/Best_Practices

wikipedia 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 w 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. BookCat