Essential Database Design and Query Optimization Tips for Faster SQL Performance

Database Structure Design

When defining tables, keep rows short, use the most appropriate data types, and size fields just enough to meet business requirements. These practices reduce storage fragmentation, lower I/O, and improve index efficiency.

Row size should stay below 8,020 bytes. Exceeding this limit forces the row onto multiple pages, causing fragmentation and slower reads.

Prefer numeric columns over character columns for values that are inherently numeric (e.g., phone numbers). Numeric comparisons are a single CPU operation, while string comparisons examine each character.

Both CHAR and VARCHAR can store up to 8,000 bytes. CHAR is slightly faster but always consumes the defined space; VARCHAR saves space for variable‑length data at a modest performance cost. Choose CHAR for fixed‑length fields (e.g., status codes) and VARCHAR for variable‑length fields (e.g., comments).

Define the smallest length that satisfies the maximum expected value. Shorter columns reduce the amount of data scanned and lower the memory needed for indexes.

Query Optimization

General guidelines: minimize round‑trips to the server, cache reusable results, request only required columns, avoid SELECT *, and limit result sets with TOP, LIMIT or similar clauses.

Avoid IS NULL checks on indexed columns. Instead, give the column a default (e.g., 0) and query with = so the index can be used. SELECT id FROM t WHERE num = 0; Do not use the inequality operators != or <> on indexed columns; they force a full scan.

Replace OR conditions with UNION ALL when the predicates are mutually exclusive. This allows each sub‑query to use its own index.

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

Prefer BETWEEN for continuous ranges instead of IN. SELECT id FROM t WHERE num BETWEEN 1 AND 3; Avoid leading wildcards on indexed character columns. Use a prefix search or a suitable substring function.

-- Index can be used
SELECT * FROM T1 WHERE NAME LIKE 'L%';
-- Index cannot be used
SELECT * FROM T1 WHERE NAME LIKE '%L%';

If the optimizer chooses a full scan because a parameter value is unknown, force a specific index with the hint syntax.

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

Never place expressions on the indexed column side of a predicate. Move calculations to the right‑hand side.

-- Bad: prevents index use
SELECT * FROM T1 WHERE F1/2 = 100;
-- Good: index can be used
SELECT * FROM T1 WHERE F1 = 100 * 2;

Avoid functions on indexed columns in the WHERE clause. Rewrite the condition as a range or a prefix match.

-- Bad
SELECT id FROM t WHERE SUBSTRING(name,1,3) = 'abc';
-- Good
SELECT id FROM t WHERE name LIKE 'abc%';

-- Bad (date function)
SELECT id FROM t WHERE DATEDIFF(day, createdate, '2005-11-30') = 0;
-- Good (range)
SELECT id FROM t WHERE createdate >= '2005-11-30' AND createdate < '2005-12-01';

Keep the column on the left side of the equality operator; placing an expression on the left disables index usage.

SELECT * FROM T1 WHERE F1 = 200;  -- correct
SELECT 200 = F1 FROM T1;        -- may prevent index use

When using a composite index, always filter on the leading column of the index; otherwise the index cannot be leveraged.

Prefer EXISTS to IN for correlated subqueries.

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

Use table variables instead of temporary tables when the data set is small. Table variables have limited indexing (only primary‑key or unique constraints).

Avoid repeatedly creating and dropping temporary tables inside loops. Reuse a single temporary table or switch to a table variable.

For bulk loading into a temporary table, prefer SELECT INTO to reduce logging. For modest row counts, create the table first and then insert.

Explicitly TRUNCATE and DROP temporary tables at the end of a stored procedure to release system resources.

Set SET NOCOUNT ON at the start of stored procedures and triggers, and reset with SET NOCOUNT OFF before returning. This suppresses the “DONE_IN_PROC” messages and reduces network chatter.

Keep transactions as short as possible to improve concurrency and reduce lock contention.

Return only the columns and rows required by the client. Large result sets increase bandwidth and memory usage on both server and client.

Match data types in predicates. Comparing incompatible types (e.g., float to int) forces implicit conversions that prevent index usage.

-- Bad
SELECT name FROM employee WHERE salary > 60000;  -- salary is money, 60000 is int
-- Good
SELECT name FROM employee WHERE salary > CAST(60000 AS money);

Include all join predicates in the WHERE clause (or ON clause) to allow the optimizer to choose the most efficient join order.

SELECT SUM(A.AMOUNT)
FROM ACCOUNT A
JOIN CARD B ON A.CARD_NO = B.CARD_NO AND A.ACCOUNT_NO = B.ACCOUNT_NO;