25 Essential SQL Query Optimization Tips to Avoid Full Table Scans

SQL Query Optimization Guidelines

1. Avoid full table scans by creating indexes on columns used in WHERE and ORDER BY clauses.

2. Do not use != or <> in WHERE clauses, as they force a full scan.

3. Avoid NULL checks in WHERE clauses; instead set a default value (e.g., 0) and query on that.

select id from t where num is null

select id from t where num = 0

4. Do not use OR to combine conditions; rewrite using UNION ALL when appropriate.

select id from t where num = 10 or num = 20

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

5. Avoid LIKE patterns with leading wildcards; consider full‑text search instead.

select id from t where name like '%abc%'

6. Use IN sparingly; replace with BETWEEN for continuous ranges.

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

select id from t where num between 1 and 3

7. Parameterized predicates can cause scans; force index usage when needed.

select id from t where num = @num

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

8. Do not apply arithmetic or functions to indexed columns in WHERE clauses.

select id from t where num/2 = 100

select id from t where num = 100*2

9. Avoid functions on strings; rewrite using range predicates.

select id from t where substring(name,1,3) = 'abc'

select id from t where name like 'abc%'

10. Do not place expressions on the left side of =; keep the column alone.

11. For composite indexes, always filter on the leading column and keep column order consistent with the index definition.

12. Do not create empty result sets (e.g., SELECT ... INTO #t WHERE 1=0); use CREATE TABLE instead.

create table #t(...)

13. Prefer EXISTS over IN for sub‑queries.

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

14. Indexes are ineffective when the indexed column has low cardinality (e.g., gender).

15. Limit the number of indexes per table (recommended ≤6) to balance read and write performance.

16. Avoid updating clustered index columns frequently; consider making the index non‑clustered if updates are common.

17. Prefer numeric data types over character types for numeric data to improve comparison speed and reduce storage.

18. Use VARCHAR/NVARCHAR instead of CHAR/NCHAR to save space and improve search performance.

19. Never use SELECT *; explicitly list required columns.

20. Prefer table variables over temporary tables; note that table variables have limited indexing.

21. Minimize creation and dropping of temporary tables to reduce system‑table overhead.

22. Temporary tables are useful for repeated large data sets, but for one‑off operations consider using SELECT INTO.

23. For bulk inserts into a temporary table, use SELECT INTO; otherwise create the table first and then INSERT.

24. At the end of stored procedures, explicitly truncate and drop temporary tables to avoid long‑lasting locks.

25. Avoid cursors when possible; for large row counts (>10,000) rewrite using set‑based logic.

26. Before resorting to cursors or temp tables, seek a set‑based solution.

27. When necessary, FAST_FORWARD cursors can be efficient for small data sets; compare both approaches.

28. Set NOCOUNT ON at the start of stored procedures and triggers to suppress unnecessary DONE_IN_PROC messages.

29. Limit result set size sent to clients; evaluate whether large data transfers are required.

30. Keep transactions short to improve concurrency.