Essential Database Design and Query Optimization Tips for Faster SQL Performance

Database Structure Design

When designing tables, keep each row under 8020 bytes to avoid fragmentation; use numeric types instead of strings for fields like phone numbers to reduce storage and improve join performance; choose CHAR for fixed‑length fields (e.g., usernames) and VARCHAR for variable‑length data (e.g., comments); set column lengths as short as possible while meeting requirements to speed up queries and reduce index overhead.

Query Optimization

Minimize database round‑trips by caching results, filter rows early, and select only needed columns. Use indexes wisely: avoid WHERE column IS NULL, replace with a default value and query WHERE column = 0. Do not use != or <>, and avoid OR in predicates; rewrite as separate queries combined with UNION ALL. Prefer BETWEEN over IN for continuous ranges.

Avoid functions or arithmetic on indexed columns in the WHERE clause because they force full table scans. For example, replace SELECT * FROM T1 WHERE SUBSTRING(NAME,2,1)='L' with SELECT * FROM T1 WHERE NAME LIKE 'L%'. Similarly, move calculations to the right side: SELECT * FROM T1 WHERE F1/2=100 becomes SELECT * FROM T1 WHERE F1=200.

Force index usage when necessary: SELECT id FROM t WITH (INDEX(index_name)) WHERE num = @num. Use EXISTS instead of IN for better performance, e.g.,

SELECT num FROM a WHERE EXISTS (SELECT 1 FROM b WHERE b.num = a.num)

.

When working with composite indexes, always filter on the leading column(s) and keep the column order consistent with the index definition.

Additional Best Practices

Prefer table variables over temporary tables; if temporary tables are needed, create them with SELECT INTO for large bulk inserts.

Explicitly drop or truncate temporary tables at the end of stored procedures to release locks.

Set SET NOCOUNT ON at the start of procedures and triggers to reduce network chatter.

Avoid large transactions and returning massive result sets to improve concurrency.

Ensure compatible data types in comparisons (e.g., avoid comparing money with an integer literal).

Include as many join conditions as possible in the WHERE clause to let the optimizer choose the most efficient join order.

Following these guidelines helps reduce I/O, CPU usage, and lock contention, leading to faster, more scalable database applications.