SQL Query Best Practices

Important Points to remember to fetch data :

• Use Column names in SELECT statements instead of SELECT *
• Always use 'WHERE' clause in query to filter out unwanted data if it is one table only. Add proper filter before executing query. Use join if more then one table is required to get data and then where clause for hard code value.
• Sample
• Don't use where clause with joining two or more tables
• SELECT A.ID, A.Name, S.L astSaleDate
  FROM Customers, Sales WHERE A.ID = S.ID
• Use join
• SELECT A.ID, A.Name, S.LastSaleDate
  FROM A  INNER JOIN S  ON A.D = S.ID  where  S.LastSaleDate ='12/12/2018'
• Use 'WITH(NOLOCK)' at the end in the query to avoid blocking issue.
• Use 'TOP' clause to minimize the data load.
• Use operator 'EXISTS', 'IN' and 'Table JOINS' appropriately in query.
• 'IN' clause is more efficient when sub-query result is very small as most of the filter criteria are in sub-query.
• 'EXISTS' clause is more efficient when sub-query result is very large as most of the filter criteria are in main query.
• 'UNION ALL' is better than 'UNION' clause as 'UNION ALL' does not have to perform SORT and DISTINCT operations.
• Using 'SET NOCOUNT ON' in SP or batch of SQL Statement improves the performance.
• If the column is of type 'NVARCHAR' or 'NCHAR', then always use the 'prefix N' while specifying the character string in the WHERE criteria/UPDATE/INSERT clause.
• While using 'LIKE' clause, do not use wild character at the beginning of word while searching as it results index scan.
• Use 'NOT EXISTS' or 'EXCEPT' in the query instead of NOT IN as it improves the performance and reduces the execution time.

Hope this helps..

Arun