30 Essential SQL Query Optimization Tips to Avoid Full Table Scans

The article lists thirty practical tips for optimizing SQL queries to prevent full table scans and improve overall database performance.

Avoid using != or <> in the WHERE clause; such operators cause the engine to ignore indexes and perform a full scan.

Create indexes on columns that appear in WHERE and ORDER BY clauses to reduce the chance of a full table scan.

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

Avoid using OR to combine conditions; rewrite as separate queries combined with UNION ALL : SELECT id FROM t WHERE num = 10 UNION ALL SELECT id FROM t WHERE num = 20

Patterns with a leading wildcard (e.g., LIKE '%c%' ) trigger full scans; consider full‑text search instead.

Use IN / NOT IN sparingly. For continuous ranges, prefer BETWEEN : SELECT id FROM t WHERE num BETWEEN 1 AND 3

Parameter values in WHERE can cause scans because the optimizer cannot use the value at compile time. Force index usage with: SELECT id FROM t WITH (INDEX(index_name)) WHERE num = @num

Avoid arithmetic expressions on indexed columns. Rewrite num/2 = 100 as num = 200 : SELECT id FROM t WHERE num = 100 * 2

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

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

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

Avoid meaningless queries such as SELECT col1, col2 INTO #t FROM t WHERE 1=0 . Instead, create the table directly: CREATE TABLE #t (...)

Prefer EXISTS over IN for better performance: SELECT num FROM a WHERE EXISTS (SELECT 1 FROM b WHERE num = a.num)

Indexes lose effectiveness on low‑cardinality columns (e.g., a column where values repeat many times). Such indexes may not improve query speed.

Too many indexes degrade INSERT / UPDATE performance. Limit the number of indexes per table (generally no more than six) and evaluate the necessity of each.

Avoid updating clustered index columns frequently, as this forces row reordering and high resource consumption. Consider using a non‑clustered index if updates are common.

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

Prefer VARCHAR/NVARCHAR over CHAR/NCHAR for variable‑length data to save space and improve search efficiency.

Never use SELECT * ; list only the required columns to avoid unnecessary data transfer.

Use table variables instead of temporary tables when possible; note that table variables have limited indexing (only a primary key).

Reduce the frequency of creating and dropping temporary tables to lessen system‑table resource consumption.

Temporary tables are useful for repeated access to large data sets, but for one‑off scenarios consider derived tables or subqueries.

When inserting a large volume of data into a temporary table, use SELECT INTO for speed; for small volumes, create the table first then INSERT .

At the end of a stored procedure, explicitly clean up temporary tables: TRUNCATE TABLE #temp followed by DROP TABLE #temp .

Avoid cursors, especially when processing more than 10,000 rows; rewrite the logic using set‑based operations.

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

Cursors can be acceptable for small data sets; a FAST_FORWARD cursor may outperform row‑by‑row processing. Test both approaches to choose the best.

Set 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.

Avoid returning excessively large result sets; verify whether the data volume is truly needed.

Minimize the size of transactions to improve concurrency and reduce lock contention.