Tips 21–30: DDL & Indexing

Performance starts before the first query — in how the tables are designed (DDL) — and continues long after, in how the indexes are maintained. These final 10 tips cover both.

Tip 21 — Choose precise data types

Avoid VARCHAR / TEXT when a more exact type fits.

-- ❌ Bad
jersey_no VARCHAR(50)

-- ✅ Good
jersey_no TINYINT

Smaller types → more rows per 8 KB page → fewer pages to read. Correct types also give correct sorting ('10' < '9' as text!) and avoid implicit conversions that block index seeks.

Tip 22 — No excessive lengths

Avoid VARCHAR(MAX) unless it is truly needed.

-- ❌ Bad
name VARCHAR(MAX)

-- ✅ Good
name VARCHAR(50)

VARCHAR(MAX) can hold up to 2 GB, cannot be an index key column, may be stored off-row, and makes SQL Server request more memory for sorts. Size columns to the real data.

Tip 23 — Use NOT NULL

Enforce integrity with NOT NULL wherever a value is required.

-- ❌ Bad
id INT

-- ✅ Good
id INT NOT NULL

NOT NULL stops bad data at the door, and it helps the optimizer: it knows no NULL checks are needed (for example, NOT IN can be planned efficiently).

Tip 24 — Clustered primary key

Every table should have a clustered primary key — it gives the data a structure.

-- ❌ Bad: table without a key (a heap)
CREATE TABLE players (id INT, name VARCHAR(50));

-- ✅ Good
CREATE TABLE players (
    id   INT NOT NULL PRIMARY KEY CLUSTERED,
    name VARCHAR(50) NOT NULL
);

Without it the table is a heap: every lookup by id is a full scan, and updates can leave "forwarded records" behind. A good clustered key is narrow, unique, static and ever-increasing (e.g. INT IDENTITY(1,1)).

Tip 25 — Index the foreign keys

Add non-clustered indexes to foreign keys that are queried or joined often.

-- ❌ Bad: FK players.team -> teams.team, but no index on players.team

-- ✅ Good
CREATE INDEX IX_fk_team ON players (team);

SQL Server automatically indexes primary keys, but not foreign keys. FK columns are exactly the ones used in joins (tip #10), and deleting a parent row must check the child table — without an index that check is a full scan.

Tip 26 — Avoid over-indexing

Too many indexes slow down INSERT, UPDATE and DELETE.

-- ❌ Bad: 15 indexes on one table
-- ✅ Good: keep only the useful ones

Every INSERT into players must also insert into every index on it. 15 indexes = 16 writes per row, plus more storage and more maintenance. Index for your real, frequent queries — not for every column.

Tip 27 — Drop unused indexes

Review regularly: dropping indexes nobody reads saves space and improves write speed.

-- ❌ Bad: an index nobody reads
-- ✅ Good
DROP INDEX IX_unused ON players;

Find them with sys.dm_db_index_usage_stats (no seeks/scans/lookups, many updates) — see Indexing Strategy, Statistics & Maintenance.

Tip 28 — Update statistics weekly

Fresh statistics help the optimizer choose the best plan.

-- ❌ Bad: stale statistics -> wrong row estimates -> bad plans
-- ✅ Good
UPDATE STATISTICS players;

Schedule it (e.g. weekly via a SQL Agent job, or EXEC sp_updatestats for the whole database), and after big data loads.

Tip 29 — Fix fragmented indexes

Reorganize or rebuild fragmented indexes regularly (e.g. weekly).

-- ❌ Bad: fragmented index
-- ✅ Good
ALTER INDEX ALL ON players REBUILD;

Rule of thumb: ~5–30% fragmentation → REORGANIZE; over ~30% → REBUILD. Check with sys.dm_db_index_physical_stats.

Tip 30 — Partition + columnstore

For huge fact tables: partition the data, then add a columnstore index.

-- ❌ Bad: one giant 500M-row table
-- ✅ Good: PARTITION by season + COLUMNSTORE
CREATE CLUSTERED COLUMNSTORE INDEX CCI_innings
ON innings
ON ps_season (match_date);

Partition elimination reads only the needed seasons; columnstore compresses and scans fast inside them; maintenance happens one season at a time.

Cheat sheet — indexes & partitioning

Common mistakes

• ❌ Storing dates or numbers as VARCHAR. ✅ DATE, INT, TINYINT, DECIMAL.
• ❌ Creating an index for every column "for speed". ✅ Fewer, well-chosen (often composite) indexes.
• ❌ Assuming foreign keys are indexed automatically. ✅ In SQL Server they are not — add the index.

Interview tip

Q: Why are too many indexes bad? A: Every INSERT, UPDATE and DELETE has to maintain every index, so writes slow down; indexes use storage and memory; they need statistics and defragmentation; and the optimizer has more choices to evaluate. Keep the indexes that real queries use and drop the rest.