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.

Removing Code Smells - An Example

Common code smells

Type codes and switch statements are common code smells. In addition, there are quite some other code smells that are also very common. Below is a summary list:

- Duplicate code.
- Too many comments.
- Type code.
- Switch or a long if-then-else-if.
- Cannot give a good name to a variable, method or class.
- Use names like XXXUtil, XXXManager, XXXController and etc.
- Use the words "And", "Or" and etc. in the name of a variable, method or class.
- Some instance variables are not always used.
- A method contains too much code.
- A class contains too much code.
- A method takes too many parameters.
- Two classes are using each other.