30 Essential SQL Query Optimization Techniques

1. Index columns used in WHERE and ORDER BY

Avoid full‑table scans by creating indexes on columns referenced 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 comparisons also prevent index usage. Example: select id from t where num is null Set a default value (e.g., 0) and rewrite as:

select id from t where num = 0

4. Avoid OR in WHERE

OR leads to full scans. Replace with UNION ALL:

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

5. LIKE with leading wildcard causes full scans

Query like name like '%abc%' triggers a full scan; consider full‑text search instead.

6. Use IN/NOT IN cautiously

These can cause full scans. Prefer BETWEEN for continuous ranges:

select id from t where num between 1 and 3

7. Parameter values in WHERE may prevent index use

Because the optimizer cannot know the value at compile time, it may choose a full scan. Force index usage:

select id from t with(index(索引名)) where num = @num

8. Do not apply arithmetic expressions on indexed columns

Expression num/2 = 100 disables index; rewrite as num = 200:

select id from t where num = 100*2

9. Avoid functions on indexed columns

Functions like substring(name,1,3)='abc' or datediff(day,createdate,'2005-11-30')=0 prevent index use. Replace with range conditions:

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

10. Do not place functions or arithmetic on the left side of =

Such expressions may stop the optimizer from using indexes.

11. Use the leftmost column of a composite index

When a composite index is defined, the query must filter on its first column to be usable; keep column order consistent with the index.

12. Avoid meaningless queries that return no rows

Example that consumes resources: select col1,col2 into #t from t where 1=0 Replace with a proper table definition:

create table #t(...)

13. Prefer EXISTS over IN

Rewrite:

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

14. Indexes are ineffective on low‑cardinality columns

If a column (e.g., gender) has many duplicate values, an index may not improve query speed.

15. Limit the number of indexes per table

Too many indexes (more than six) can degrade INSERT/UPDATE performance; add indexes only when truly needed.

16. Avoid updating clustered index columns frequently

Changing clustered key values forces row reordering and heavy resource consumption; consider a non‑clustered key if updates are frequent.

17. Prefer numeric over character columns for numeric data

Numeric comparisons are faster and use less storage than string comparisons.

18. Use VARCHAR/NVARCHAR instead of CHAR/NCHAR

Variable‑length types save space and improve search efficiency.

19. Never use SELECT *

Specify required columns to avoid returning unnecessary data.

20. Prefer table variables over temporary tables

Table variables have limited indexing (only primary key) but can be more efficient for small datasets.

21. Reduce creation/deletion of temporary tables

Frequent temp‑table churn consumes system resources.

22. Use temporary tables judiciously

They are useful for repeated access to large result sets, but for one‑off operations a derived table may be better.

23. For bulk inserts into a new temp table, use SELECT‑INTO

When inserting a large amount of data at once, SELECT … INTO avoids excessive logging; for small data, create the table first then INSERT.

24. Explicitly drop temporary tables at the end of a stored procedure

TRUNCATE then DROP to release locks promptly.

25. Avoid cursors for large rowsets

Cursors are slow; if processing exceeds 10,000 rows, rewrite using set‑based logic.

26. Seek set‑based solutions before cursor or temp‑table approaches

Set‑based queries are generally more efficient.

27. When necessary, use FAST_FORWARD cursors for small datasets

They can outperform row‑by‑row processing for limited data.

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

Suppresses unnecessary DONE_IN_PROC messages, reducing network traffic.

29. Limit result set size sent to clients

Large result sets should be evaluated for necessity to avoid overwhelming clients.

30. Keep transactions short

Shorter transactions improve concurrency and reduce lock contention.