What generic techniques can be applied to optimize SQL queries?

Question

What techniques can be applied effectively to improve the performance of SQL queries? Are there any general rules that apply?

Answer 1

• Use primary keys
• Avoid select *
• Be as specific as you can when building your conditional statements
• De-normalisation can often be more efficient
• Table variables and temporary tables (where available) will often be better than using a large source table
• Partitioned views
• Employ indices and constraints

Answer 2

Learn what's really going on under the hood - you should be able to understand the following concepts in detail:

• Indexes (not just what they are but actually how they work).
• Clustered indexes vs heap allocated tables.
• Text and binary lookups and when they can be in-lined.
• Fill factor.
• How records are ghosted for update/delete.
• When page splits happen and why.
• Statistics, and how they effect various query speeds.
• The query planner, and how it works for your specific database (for instance on some systems "select *" is slow, on modern MS-Sql DBs the planner can handle it).