SQL Query Optimization Tips and Best Practices

1. Reduce database access and minimize result sets. Use selective columns, limit rows (e.g., SELECT TOP 300 COL1, COL2, COL3 FROM T1), avoid SELECT *, and place indexed columns first in WHERE clauses.

2. Avoid incompatible data types. Mismatched types (e.g., float vs int) prevent the optimizer from using efficient plans; cast values appropriately before comparison.

3. Do not apply functions or expressions to indexed columns in WHERE clauses. Rewrite expressions so the column remains untouched, for example change SELECT * FROM T1 WHERE F1/2=100 to SELECT * FROM T1 WHERE F1=200.

4. Avoid operators that disable index usage. Operators such as !=, IS NULL, IN, and NOT IN can force full table scans; prefer index‑friendly predicates.

5. Prefer numeric fields over character fields for numeric data. Numeric comparisons are faster because they avoid per‑character string evaluation.

6. Use EXISTS / NOT EXISTS instead of counting rows. Example: SELECT * FROM T2 WHERE T2.C2=T1.C2 inside EXISTS is more efficient than aggregating counts.

7. Avoid leading‑wildcard searches on indexed columns. Queries like SELECT * FROM T1 WHERE NAME LIKE '%L%' cannot use the index, while SELECT * FROM T1 WHERE NAME LIKE 'L%' can.

8. Write complete join conditions. Including all relevant predicates (e.g., AND A.ACCOUNT_NO=B.ACCOUNT_NO) can dramatically improve performance.

9. Eliminate sequential scans caused by complex WHERE clauses. Split queries with UNION to allow each part to use indexes.

10. Avoid expensive regular‑expression‑like patterns. Replace LIKE '98___' with range queries such as WHERE zipcode > '98000' to enable index usage.

11. Use views to pre‑sort and filter data. Creating a view (e.g., CREATE VIEW DBO.V_CUST_RCVLBES AS SELECT ...) can reduce I/O and speed up repeated queries.

12. Prefer BETWEEN over IN for range checks. Example: SELECT * FROM T1 WHERE ID BETWEEN 10 AND 14 allows index seeks.

13. Replace GROUP BY with DISTINCT when appropriate. SELECT DISTINCT OrderID FROM Details WHERE UnitPrice > 10 is simpler and often faster.

14. Partially utilize indexes with selective predicates. Queries that filter on indexed columns (e.g., WHERE dept='prod') benefit from index seeks, while broader scans do not.

15. Use UNION ALL instead of UNION when duplicate elimination is unnecessary. This avoids the costly SELECT DISTINCT step.

16. Remove no‑op queries. Statements like SELECT COL1 FROM T1 WHERE 1=0 waste resources without returning data.

17. Avoid SELECT INTO for large tables. It can cause table locks and block concurrent access.

18. Force index usage when needed. Syntax such as SELECT * FROM T1 (INDEX = IX_ProcessID) WHERE ... directs the optimizer to a specific index.

19. Update best practices. Do not modify primary keys, keep VARCHAR updates to same length, minimize triggers, avoid updating indexed columns unnecessarily, and limit updates that copy data to other databases.

Overall, these guidelines emphasize testing different query forms, examining execution plans (e.g., using CTRL+L in the query analyzer), and iteratively refining SQL to achieve the lowest possible execution cost.