30 Essential SQL Query Optimization Tips to Avoid Full Table Scans

The following thirty tips explain how to write efficient SQL queries and manage database objects so that the query optimizer can use indexes instead of performing full table scans.

Create indexes on columns referenced in WHERE and ORDER BY clauses to avoid full scans.

Avoid the != or <> operators in WHERE clauses; they prevent index usage.

Do not test for NULL values in WHERE. Set a default (e.g., 0) on the column and query with equality: select id from t where num is null becomes select id from t where num = 0 Avoid using OR to combine conditions; rewrite with UNION ALL: select id from t where num = 10 or num = 20 becomes

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

Do not use a leading wildcard in LIKE (e.g., '%abc%') because it forces a scan; consider full‑text search instead.

For continuous numeric ranges, prefer BETWEEN over IN: select id from t where num in (1,2,3) becomes select id from t where num between 1 and 3 Parameterized predicates can also cause scans. Force index usage with a query hint: select id from t where num = @num becomes

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

Avoid expressions on indexed columns in the WHERE clause. Rewrite the expression so the column appears alone, e.g.: select id from t where num/2 = 100 becomes select id from t where num = 100*2 Avoid functions on columns; replace with range or LIKE predicates:

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

becomes select id from t where name like 'abc%' and replace date‑diff checks with explicit date ranges.

Never place functions or arithmetic on the left side of an equality comparison; keep the column alone.

When using a composite index, the query must filter on the leftmost column of the index, and the column order in the predicate should match the index definition.

Do not generate empty result sets with SELECT ... INTO #t FROM t WHERE 1=0. Use CREATE TABLE #t (...) instead.

Prefer EXISTS to IN for sub‑queries:

select num from a where num in (select num from b)

becomes

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

Indexes are ineffective when the indexed column contains many duplicate values (e.g., a gender column with roughly equal male/female distribution).

Limit the total number of indexes per table (generally no more than six); each additional index adds overhead to INSERT/UPDATE operations.

Avoid frequent updates to clustered index columns because they require reordering the entire table.

Store purely numeric data in numeric types, not character types, to speed up comparisons and reduce storage.

Prefer variable‑length types ( VARCHAR / NVARCHAR) over fixed‑length ( CHAR / NCHAR) for better storage efficiency and query performance.

Never use SELECT *; explicitly list only the columns you need.

Prefer table variables to temporary tables when possible; note that table variables only support a primary‑key index.

Avoid repeatedly creating and dropping temporary tables; this consumes system resources.

Temporary tables are useful for re‑using large data sets across multiple statements, but for one‑off operations consider exporting the data instead.

For massive one‑time inserts, use SELECT INTO to avoid excessive logging; for smaller loads, create the table first and then INSERT.

Always drop temporary tables explicitly at the end of a stored procedure (e.g., TRUNCATE TABLE #t; DROP TABLE #t;) to release locks.

Avoid cursors for large result sets (>10,000 rows); rewrite the logic using set‑based statements.

Before resorting to cursors or temp tables, search for a set‑based solution, which is usually more efficient.

Cursors can be acceptable for small data sets; use FAST_FORWARD when appropriate.

At the beginning of stored procedures and triggers, set SET NOCOUNT ON and reset it at the end to suppress unnecessary DONE_IN_PROC messages.

Do not return excessively large result sets to the client; evaluate whether the data volume is truly needed.

Keep transactions small to improve concurrency and reduce lock contention.