SQL Query Optimisation - Other than common ones

In addition to the common optimization tips for SQL Server query, the following are some of the useful tips.

- To view the row count from a table use, "SELECT rows FROM sysindexes WHERE id = OBJECT_ID('table_name') AND indid < 2" query instead of count(*) or use sp_spaceused which will also provide the row count.
- Avoid having clause in group by and use where if necessary
- Avoid distinct clause and use it only when it is really mandatory
- Include SET NOCOUNT ON for stored procedures as it will reduce the transfer of rowcount data travelling from database to application
- In case you need to get only first n rows in a query use top or SET ROWCOUNT statement.
- Avoid using optimizer hints in any database. This may make the query faster initially but will really affect the performance as the data grows in the table.
- Try to use inner tables instead of creating temporary table whereever possible.
- Use a EXIST, NOT EXISTS or LEFT OUTER JOIN instead of a IN or NOT IN in where clause.