Query Tuning

Use SHOWPLAN_TEXT or Graphic Query Plan to analyze queries.
Joins perform better than sub queries
Beware queries that have SCAN but not SEEK operations
Beware join queries that have HASH but not NESTED LOOP operations
Remember that constraints put lots of overhead on INSERT and UPDATE statements

Queries with LIKE

Queries on production systems should NOT use SELECT * FROM…
Main reason is that any time the underlying table is changed, all query plans stored in the cache must be rebuilt
The SQL tools allow very quick scripting – so no excuses!
Queries that use the LIKE clause have two simple rules:
LIKE can use indexes if the pattern starts with a character string, such as WHERE lname LIKE ‘w%’
LIKE cannot use an index if the pattern starts with a leading wildcard, such as WHERE lname LIKE ‘%alton’

Queries with Functions and Calculations in the WHERE clause

Avoid using functions or calculations on the column in a WHERE clause because it causes SQL Server to ignore any index on the column:
WHERE qty * 12 > 10000

WHERE ISNULL(ord_date, ‘Jan 01,2001’) > ‘Jan 01, 2002 12:00:00 AM’
Instead, move the function or calculation to the SARG:
WHERE qty > 10000/12

WHERE ord_date IS NOT NULL
AND ord_date > ‘Jan 01, 2002 12:00:00 AM’