From Wikibooks, open books for an open world
Jump to navigationJump to search
 | Wikipedia has related information at 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 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.
