What are your most common sql optimizations?

Question

Reducing the amount of data that is returned, by only returning the fields required and only returning the rows required. This is the most common, as you do it for every query that returns data.

Adding indexes. This is not done as frequently, as some tables doesn't need any other index than the one created for the primary key.

My favorite list of tips (explained in detail here) is as follows

1. Try to restrict the queries result set by using the WHERE clause.
2. Try to restrict the queries result set by returning only the particular columns from the table, not all the table's columns.
3. Use views and stored procedures instead of heavy-duty queries.
4. Whenever possible, try to avoid using SQL Server cursors.
5. If you need to return the total table's row count, you can use an alternative way instead of the SELECT COUNT(*) statement.
6. Try to use constraints instead of triggers, whenever possible.
7. Use table variables instead of temporary tables.
8. Try to avoid the HAVING clause, whenever possible.
9. Whenever possible, try to avoid using the DISTINCT clause.
10. Include SET NOCOUNT ON statement into your stored procedures to stop the message indicating the number of rows affected by a T-SQL statement.
11. Use select statements with the TOP keyword or the SET ROWCOUNT statement if you need to return only the first n rows.
12. Use the FAST number_rows table hint if you need to quickly return 'number_rows' rows.
13. Try to use UNION ALL statement instead of UNION, whenever possible.
14. Do not use optimizer hints in your queries.

By far and above: Making covering indexes

A covering index includes all the columns that the query will need, thereby avoiding the need to do lookups on the results of an index seek. This will then avoid the system feeling like a scan could be quicker (which is remarkably quick considering the cost of lookups).

But also worth mentioning:

Having an index that will allow a merge join. A MERGE join is able to occur when joining two tables that are ordered by the join conditions. But of course, in saying 'table', we really mean 'index', right...

Also - removing scalar functions and using table-valued functions instead... as scalar functions are not able to be simplified out.

Also - putting a unique index on a column that you know to be unique, allowing the query optimizer to use this knowledge to make better optimization choices. Also applies to NOT NULL constraints.

Also - using Binary collation when comparing strings that are in a known case, so that the system doesn't have to consider the different case options.

Of course I could go on all day...

Rob

Caching db output. Avoiding pressuring the database at all seems to be a prudent optimization.

+1 memcached.

Index foreign keys!

Maybe this isn't a sql query syntax optimisation, but more a storage optimisation. But i see it re-occur all the time & its a pet peeve.