30 Essential SQL Query Optimization Tips from a Senior Architect

1. Optimize queries to avoid full table scans

Place indexes on columns used in WHERE and ORDER BY clauses.

2. Do not use != or <> in WHERE

These operators cause the engine to ignore indexes and perform a full scan.

3. Avoid NULL checks in WHERE

NULL predicates also prevent index usage. select id from t where num is null Set a default value (e.g., 0) on the column to eliminate NULLs and rewrite the query:

select id from t where num = 0

4. Avoid OR in WHERE

Using OR makes the optimizer drop the index. select id from t where num = 10 or num = 20 Rewrite using UNION ALL:

select id from t where num = 10
union all
select id from t where num = 20

5. Avoid LIKE '%abc%' patterns

Such patterns trigger full scans; consider full‑text search instead.

6. Use IN and NOT IN cautiously

Both can lead to scans; prefer range queries when possible.

select id from t where num in (1,2,3)

7. Parameterized predicates may cause scans

When the optimizer cannot know the parameter value at compile time, it may forgo index usage. select id from t where num = @num Force index usage with a query hint:

select id from t with (index(IndexName)) where num = @num

8. Avoid expressions on indexed columns

Expressions such as num/2 = 100 prevent index use.

select id from t where num = 100*2

9. Do not apply functions to indexed columns

Functions like SUBSTRING(name,1,3)='abc' or DATEDIFF(day,createdate,'2005-11-30')=0 disable index usage.

Rewrite as:

select id from t where name like 'abc%'
select id from t where createdate >= '2005-11-30' and createdate < '2005-12-01'

10. Avoid functions or arithmetic on the left side of =

Such constructs can prevent the optimizer from using an index.

11. Use the leading column of a composite index

Only the first column of a multi‑column index guarantees index usage; keep field order aligned with index order.

12. Do not create empty result‑set queries

Queries like select col1,col2 into #t from t where 1=0 waste resources.

Instead, create the table directly:

create table #t (...)

13. Prefer EXISTS over IN

EXISTS

often yields better performance.

select num from a where exists (select 1 from b where b.num = a.num)

14. Indexes on low‑cardinality columns may be ineffective

If a column like sex contains only two values, an index provides little benefit.

15. Limit the number of indexes per table

Too many indexes (more than six) degrade INSERT / UPDATE performance; evaluate necessity.

16. Minimize updates to clustered index columns

Changing clustered index keys forces row reordering and is costly.

17. Prefer numeric over character columns for numeric data

Numeric comparisons are faster and use less storage.

18. Use VARCHAR/NVARCHAR instead of CHAR/NCHAR

Variable‑length types save space and improve search speed.

19. Avoid SELECT *

Specify only required columns to reduce I/O.

20. Prefer table variables over temporary tables when possible

Table variables have limited indexing (only primary key).

21. Limit creation and deletion of temporary tables

Frequent temp‑table churn consumes system resources.

22. Use temporary tables judiciously

They are useful for repeated large‑set references but not for one‑off operations.

23. For bulk inserts, use SELECT INTO instead of CREATE TABLE + INSERT

This reduces logging overhead.

24. Explicitly clean up temporary tables at the end of stored procedures

Truncate then drop to release locks promptly.

25. Avoid cursors for large data sets

Cursors are slow; rewrite set‑based logic when processing >10,000 rows.

26. Prefer set‑based solutions before resorting to cursors or temp tables

Set‑based approaches are generally more efficient.

27. Small data sets may benefit from FAST_FORWARD cursors

They can be faster than row‑by‑row processing for modest volumes.

28. Set SET NOCOUNT ON at the start of stored procedures and triggers

Suppresses unnecessary DONE_IN_PROC messages.

29. Limit large result sets sent to clients

Assess whether returning massive data is truly needed.

30. Keep transactions short

Shorter transactions improve concurrency.