SQL Server : Some common query optimization techniques

• Try to fetch relevant data as per your needs from SQL Server, so server need to consume resources for unwanted data.

• Always provide column names in SELECT clause and provide only required column list.

• Try to avoid DISTINCT clause, one work around to eliminate distinct is to add a unique/primary key column in result, so you will be able to decide weather record is duplicate of not based on different value of primary/unique key.

• Try to avoid cross joins.

• if you are grouping data and applying having clause try to put criteria in where clause if possible, as group by and having will be applied on result of query so you can limit the no. of rows by applying filter criteria in where clause so you will get lesser record and system will be able to perform grouping operation fast.

• use Like clause carefully and efficiently as it will be more resource intensive for server and produce more unwanted records in query result. for ex if you want to search city name start from  'MA' use LIKE 'MA%' instead of  LIKE '%MA%'.

• Avoid Co-related sub queries and use Join instead, for ex :  

SELECT c.Name, c.City,(SELECT CompanyName FROM Company WHERE ID = c.CompanyID) AS CompanyName
FROM Customer c        

you can achieve same result by using JOIN which will be faster than this. 

• If you want to check if a record exists, use EXISTS() instead of COUNT() because count will scan entire table while Exist will return when matching record found. for ex: IF( SELECT COUNT(NAME) FROM PERSON WHERE AGE>100) >0) do something...instead use IF(EXISTS(SELECT NAME FROM PERSON WHERE AGE>100)