30 Essential SQL Query Optimization Tips to Avoid Full Table Scans

Avoid using != or <> in the WHERE clause; the optimizer will discard the index and perform a full table scan.

Create indexes on columns referenced by WHERE and ORDER BY to reduce full scans.

Do not test for NULL values in WHERE because it disables index usage. Instead, give the column a default (e.g., 0 ) and query WHERE num = 0 : SELECT id FROM t WHERE num = 0

Avoid OR in WHERE conditions; it forces a full scan. Split the logic into separate queries and combine with UNION ALL : SELECT id FROM t WHERE num = 10 UNION ALL SELECT id FROM t WHERE num = 20

Using a leading wildcard with LIKE (e.g., LIKE '%c%' ) prevents index use. Use a prefix pattern ( LIKE 'c%' ) instead. SELECT id FROM t WHERE name LIKE 'c%'

Prefer BETWEEN over IN for continuous numeric ranges to allow index usage: SELECT id FROM t WHERE num BETWEEN 1 AND 3

Parameter values in WHERE (e.g., @num ) can cause a full scan because the optimizer cannot evaluate the value at compile time. Force the index explicitly: SELECT id FROM t WITH (INDEX(index_name)) WHERE num = @num

Avoid arithmetic expressions on indexed columns (e.g., num/2 = 100 ). Rewrite as a direct comparison: SELECT id FROM t WHERE num = 200

Do not apply functions to indexed columns in WHERE . Replace SUBSTRING(name,1,3) = 'abc' with name LIKE 'abc%' , and replace date‑diff calculations with range predicates. SELECT id FROM t WHERE name LIKE 'abc%' SELECT id FROM t WHERE createdate >= '2005-11-30' AND createdate < '2005-12-01'

Avoid placing functions or arithmetic on the left side of an equality ( = ) in WHERE clauses; it hinders index usage.

When using a composite index, the query must reference the first indexed column (and follow the index column order) to benefit from the index.

Do not write meaningless queries such as SELECT col1, col2 INTO #t FROM t WHERE 1=0 . Create the table directly instead: CREATE TABLE #t (...)

Prefer EXISTS to IN for sub‑query checks: SELECT num FROM a WHERE EXISTS (SELECT 1 FROM b WHERE num = a.num)

Indexes are ineffective on columns with high duplicate values (e.g., a sex column with roughly equal male/female distribution).

Too many indexes degrade INSERT / UPDATE performance; keep the total number of indexes per table to around six, and evaluate the necessity of each.

Avoid updating clustered‑index columns whenever possible, as it forces row‑reordering and consumes significant resources. Consider using a non‑clustered index if frequent updates are required.

Store purely numeric data in numeric types rather than character types to improve comparison speed and reduce storage.

Prefer variable‑length types ( VARCHAR / NVARCHAR ) over fixed‑length ( CHAR / NCHAR ) for better space utilization and faster searches.

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

Use table variables instead of temporary tables when appropriate; note that only a primary‑key index is available on table variables.

Avoid repeatedly creating and dropping temporary tables; it consumes system‑table resources.

Temporary tables are useful for large, reusable result sets; otherwise, consider derived tables or CTEs.

For bulk insertion into a temporary table, use SELECT INTO to minimize logging; for smaller loads, create the table first and then INSERT .

Always explicitly remove temporary tables at the end of a stored procedure: TRUNCATE TABLE #t; DROP TABLE #t;

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

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; a FAST_FORWARD cursor often outperforms row‑by‑row processing.

Place SET NOCOUNT ON at the start of stored procedures and triggers, and SET NOCOUNT OFF at the end to suppress unnecessary DONE_IN_PROC messages.

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

Avoid large transactions; they reduce concurrency and increase lock contention.