Mastering SQL Server Index Creation, Maintenance, and Optimization

Index Creation

SQL Server manages indexes with the DDL statements CREATE INDEX, ALTER INDEX and DROP INDEX. When a clustered index is created the internal process depends on the current state of the table:

Table already has a clustered index – an error is raised because a table can have only one clustered index.

Empty table – only system catalog rows are updated; no data pages are allocated.

Table with data and no non‑clustered indexes – rows are sorted by the index key, pages are filled according to the FILLFACTOR, and non‑leaf nodes are built with virtually no external fragmentation.

Table with data and existing non‑clustered indexes – space used by the non‑clustered indexes is released (metadata remains), system tables are updated, and then the clustered index is built.

Creating a non‑clustered index follows a similar pattern. If the table is empty only the catalog is updated. If data exists, SQL Server scans the table (or an existing covering non‑clustered index), creates index entries sorted by the key, fills pages according to the fill factor, and builds leaf pages with minimal external fragmentation.

Index Alteration

The ALTER INDEX statement supports four primary actions:

Disable the index

Rebuild the index

Reorganize the index

Change index options

Disable

Disabling removes the index’s data pages (freeing space) but retains its metadata, allowing a later rebuild or drop. Example:

ALTER INDEX PK_FragTest_PKCol ON FragTest DISABLE;

Disabling a non‑clustered index releases its space and makes the index invisible to queries. Disabling a clustered index releases non‑leaf pages and renders the table inaccessible until the index is rebuilt.

Rebuild

Rebuilding recreates the index and can change options in a single operation. Example:

ALTER INDEX PK_FragTest_PKCol ON FragTest REBUILD
WITH (FILLFACTOR = 75, SORT_IN_TEMPDB = ON, MAXDOP = 3);

The rebuild eliminates external fragmentation, repopulates pages to the specified fill factor, and updates any supplied options in the system catalog while leaving unspecified options unchanged.

Reorganize

Reorganization defragments the index without requiring additional disk space. It proceeds in two stages:

Internal fragmentation – pages are processed in small batches (e.g., eight pages at a time). If a batch can be compressed to fit the fill factor, excess pages are released.

External fragmentation – pages are reordered to match the logical key order, swapping one page at a time until the physical order aligns with the logical order.

Reorganization consumes fewer resources than a rebuild and keeps the index online for queries.

Index Options

Options supplied to CREATE INDEX (and, for some, ALTER INDEX … REBUILD) fall into three categories: those that affect creation, those that affect usage, and those that affect both. The most commonly used options are:

FILLFACTOR – percentage of free space left on leaf pages (default 0 = fully packed).

PAD_INDEX – applies the fill factor to non‑leaf pages when set to ON.

SORT_IN_TEMPDB – performs the sort phase of index creation in TempDB instead of the user database.

IGNORE_DUP_KEY – for unique indexes, duplicate keys generate a warning instead of an error.

DROP_EXISTING – used with CREATE INDEX to replace an existing index of the same name and type.

ONLINE – allows concurrent queries during a rebuild (Enterprise/Developer editions only).

MAXDOP – limits the number of CPU cores used for index creation or rebuild.

ALLOW_ROW_LOCKS and ALLOW_PAGE_LOCKS – control lock escalation behavior; they do not affect the physical structure of the index.

DATA_COMPRESSION – enables row or page compression, affecting both storage and query performance.

Index Dropping

Dropping an index frees the space occupied by its data pages and removes its metadata from the system catalog. Restrictions:

An index that enforces a PRIMARY KEY or UNIQUE constraint cannot be dropped.

Dropping a clustered index does not drop the table; the table becomes a heap and all non‑clustered indexes are automatically rebuilt.

When multiple indexes must be removed, drop non‑clustered indexes first, then the clustered index.

Best‑Practice Summary

Use CREATE INDEX to define indexes and set appropriate options at creation time.

Prefer ALTER INDEX … REORGANIZE for routine fragmentation removal because it is lightweight and keeps the index online.

Use ALTER INDEX … REBUILD when fragmentation is severe or when you need to change index options.

Disable an index before a rebuild if disk‑space consumption is a concern; the disabled index releases its pages, reducing the temporary space required for the rebuild.

Choose a fill factor that reflects the expected insert‑update pattern: lower values for tables with frequent inserts, higher values for read‑only or append‑only workloads.

Create the clustered index before any non‑clustered indexes on a table; when removing indexes, reverse the order.

Set MAXDOP to a value that balances CPU usage with other workloads, and consider SORT_IN_TEMPDB on systems with ample TempDB space.