30 Essential SQL Query Optimization Tips to Boost Performance

SQL Query Optimization Tips

1) Index the columns used in WHERE and ORDER BY clauses. Proper indexes prevent full‑table scans.

2) Avoid the != or <> operators in WHERE clauses. These cause the optimizer to ignore indexes.

3) Do not test for NULL values in WHERE clauses. Use a default value instead, e.g.: SELECT id FROM t WHERE num = 0 4) Replace OR conditions with UNION ALL queries. Example:

SELECT id FROM t WHERE num = 10
UNION ALL
SELECT id FROM t WHERE num = 20

5) Avoid leading wildcards in LIKE patterns. Use full‑text search for patterns such as WHERE name LIKE '%abc%'.

6) Use BETWEEN instead of IN for continuous ranges. Example: SELECT id FROM t WHERE num BETWEEN 1 AND 3 7) Parameter values in WHERE can force a scan. Force index usage with a hint:

SELECT id FROM t WITH (INDEX(index_name)) WHERE num = @num

8) Do not perform arithmetic on indexed columns. Rewrite WHERE num/2 = 100 as WHERE num = 200.

9) Avoid functions on indexed columns. Replace SUBSTRING(name,1,3)='abc' with WHERE name LIKE 'abc%' and rewrite date functions as range predicates.

10) Do not place expressions on the left side of = in predicates.

11) For composite indexes, always filter on the leading column(s) in order.

12) Do not create empty result sets like SELECT col1, col2 INTO #t FROM t WHERE 1=0 ; instead create the table definition directly.

13) Prefer EXISTS over IN for sub‑queries. Example replacement shown.

14) Indexes are ineffective on columns with high duplicate values. A sex column with roughly equal male/female distribution is a poor candidate.

15) Limit the number of indexes per table (recommended ≤ 6). Excessive indexes degrade INSERT / UPDATE performance.

16) Avoid updating clustered index key columns. Changing the key forces row relocation.

17) Use numeric data types for numeric data. They are faster to compare and consume less space.

18) Prefer VARCHAR/NVARCHAR over fixed‑length CHAR/NCHAR for variable‑length data.

19) Never use SELECT * ; list only required columns.

20) Use table variables instead of temporary tables when appropriate; note that table variables have limited indexing.

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

22) Temporary tables are acceptable for reusable large result sets, but for one‑off operations consider exporting data instead.

23) For bulk inserts into a new temporary table, use SELECT INTO rather than CREATE TABLE followed by many inserts.

24) Always explicitly drop temporary tables at the end of a stored procedure (truncate then drop) to release locks.

25) Avoid cursors; they are slow for large row counts (>10,000).

26) Before resorting to cursors or temp tables, look for set‑based solutions.

27) When a cursor is unavoidable, use FAST_FORWARD for small datasets; compare performance with set‑based alternatives.

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

29) Limit the amount of data returned to clients; large result sets should be justified.

30) Keep transactions short to improve concurrency.