Optimizing SQL Queries

References:

1) https://www.youtube.com/watch?v=Iyr7KQUCb0M

2) https://assets.red-gate.com/community/books/inside-the-sql-server-query-optimizer.pdf

FILTER DATA

•  Avoid Using Unnecessary DISTINCT & ORDER BY
• Create Non-clustered index on frequently used columns in WHERE Clause
• Avoid applying Functions to columns in Where Clause

• Avoid Leading Wildcards as they prevent index usage

• Ensure Columns used in ON clause are Indexed

FILTER DATA

• Filter before Joining

• Aggregate before joining
  
• use UNION instead of OR in JOINS

• Use SQL HINTS when you are joining small table with big table

• Use UNION ALL instead of using UNION when duplicates are acceptable
• use UNION ALL +DISTINCT when duplicates are not acceptable
  

AGGREGATING DATA

• Use COLUMNSTORE INDEX for aggregations on large tables.

• Pre-aggregate the data and store it in new table for reporting

SUBQUERIES

• Don't use IN opeartor instead use JOIN or EXISTS
• EXISTS is better that JOIN

CREATING TABLES

• Avoid data types VARCHAR & TEXT
• Use NOT NULL constraint where applicable
• Ensure all your table have a Clustered Primary Key
• Create a non-clustered index for foreign keys that are used frequently.

INDEXING

• Avoid over indexing
• Drop unused Indexes
• Update Statistics (Weekly)
• Reorganize & Rebuild Indexes (Weekly)
• Partition your tables if you data is large. & Do column store index to enhance performance.