SQL Query Optimization Tips to Avoid Full Table Scans

The article provides a set of best practices for writing efficient SQL queries to prevent full table scans and improve performance.

1. Optimize queries by creating indexes on columns used in WHERE and ORDER BY clauses.

2. Avoid using != or <> operators in WHERE clauses, as they can cause the engine to ignore indexes.

3. Do not test for NULL in WHERE clauses; instead set default values to avoid nulls.

Example:

select id from t where num is null

Set default value 0 and query:

select id from t where num = 0

4. Avoid using OR in WHERE conditions; rewrite using UNION ALL when appropriate.

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

5. Avoid LIKE patterns with leading wildcards, which trigger full scans; consider full-text search.

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

6. Use IN/NOT IN cautiously; prefer BETWEEN for continuous numeric ranges.

select id from t where num between 1 and 3

7. Parameterized WHERE clauses can cause scans; force index usage if needed.

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

8. Avoid expressions on indexed columns in WHERE clauses.

select id from t where num/2 = 100

Rewrite as:

select id from t where num = 100*2

9. Avoid functions on columns (e.g., SUBSTRING, DATEDIFF) in WHERE clauses; use range conditions instead.

select id from t where name like 'abc%'

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

11. For composite indexes, always filter on the leading column.

12. Avoid creating empty result sets with SELECT ... INTO #temp WHERE 1=0; use CREATE TABLE instead.

13. Prefer EXISTS over IN for subqueries.

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

14. High‑cardinality columns benefit most from indexes; low‑cardinality columns may not.

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

16. Minimize updates to clustered index columns.

17. Use numeric data types for numeric data to improve comparison speed.

18. Prefer VARCHAR/NVARCHAR over CHAR/NCHAR for variable‑length strings.

19. Avoid SELECT *; specify needed columns.

20. Use table variables instead of temporary tables when appropriate.

21–30. Additional recommendations include reducing temp table churn, limiting large result sets, avoiding cursors, using SET NOCOUNT ON, and keeping transactions short.