SQL Indexes Best Practices

by

In this comprehensive guide, I will share the exact architectural principles, indexing strategies, and best practices I use to keep high-throughput transactional and analytical systems running at peak efficiency.

SQL Indexes Best Practices

Understanding SQL Index Architecture: How Indexes Actually Work

Before diving into optimization strategies, we must understand the mechanics under the hood. At its core, an index is a persistent data structure (most commonly a balanced B-Tree) designed to minimize disk I/O by allowing the database engine to locate specific rows without scanning an entire table.

B-Tree Structure Breakdown

A standard B-Tree index consists of three distinct layers:

  1. Root Node: The single entry point examined by the query engine to direct the search down the hierarchy.
  2. Intermediate Nodes: Branching levels that hold key values and pointers to direct traversal to the next appropriate node level.
  3. Leaf Nodes: The bottom layer of the tree. In a non-clustered index, leaf nodes contain the indexed column values paired with a row locator (a pointer or clustering key). In a clustered index, the leaf nodes are the actual data pages.

When a query requests data without a suitable index, the engine must perform a Full Table Scan (or Clustered Index Scan), reading every single page allocated to that table into memory. With a well-placed index, the engine executes an Index Seek, traversing the B-Tree directly to the target record in a fraction of the computational cost and time.

Clustered vs. Non-Clustered Indexes: The Strategic Difference

Choosing between clustered and non-clustered indexes is one of the first architectural decisions you make when defining a schema.

1. Clustered Indexes

A clustered index determines the physical order of data storage on disk. Because the data itself can only be sorted in one way, you can have only one clustered index per table.

  • Primary Purpose: Organizing the base table pages.
  • Storage Overhead: Zero additional storage beyond the table data pages and minimal B-Tree navigational structure.
  • Optimal Selection: Surrogate identity keys, sequential primary keys (such as auto-increment integers), or strictly monotonic chronological timestamps.

2. Non-Clustered Indexes

A non-clustered index is a completely separate physical structure that contains the indexed key columns along with a bookmark (pointer) back to the base data row.

  • Primary Purpose: Optimizing secondary search paths, filtering conditions, and join predicates.
  • Storage Overhead: Substantial, as it duplicates the indexed column values in a distinct physical allocation.
  • Optimal Selection: Foreign keys, frequently filtered status columns, lookup attributes, and columns participating in JOIN, ORDER BY, or GROUP BY clauses.

Best Practices for Choosing Index Key Columns

Selecting which columns to index requires a deliberate balance between query retrieval speed and data manipulation throughput.

1. Prioritize High Selectivity Columns

Selectivity measures how distinct the values are within a given column relative to the total row count.

$$\text{Selectivity} = \frac{\text{Number of Distinct Values}}{\text{Total Number of Rows}}$$

  • High Selectivity (Close to 1.0): Ideal for indexing. Columns with unique identifiers, email addresses, or transaction reference codes allow the engine to eliminate massive percentages of data pages immediately.
  • Low Selectivity (Close to 0.0): Poor candidates for standard B-Tree indexing. Columns with few distinct values (such as binary flags or status codes) typically prompt the query optimizer to abandon the index in favor of a scan unless filtered down using specialized techniques.

2. Match the Left-to-Right Rule in Composite Indexes

When creating multi-column (composite) indexes, column order dictates usability. The database engine traverses composite indexes following the “Left-to-Right Rule” (also known as the leftmost prefix rule).

Consider an index defined on (Column_A, Column_B, Column_C):

  • Queries filtering by Column_A will utilize the index.
  • Queries filtering by Column_A AND Column_B will utilize the index efficiently.
  • Queries filtering by Column_A AND Column_B AND Column_C will achieve optimal seek performance.
  • Queries filtering only by Column_B or Column_C cannot seek directly into the index B-Tree, forcing an index scan.

Place the most frequently filtered, equality-based (=) columns at the beginning of the composite key, followed by range-based (<, >, BETWEEN, LIKE) columns.

3. Leverage Covering Indexes with Included Columns

A Covering Index occurs when an index contains all the columns requested by a specific query (both in the SELECT list and the WHERE/JOIN clauses). When an index is fully covering, the database engine retrieves all necessary data directly from the index leaf pages, completely bypassing expensive Key Lookups or RID Lookups against the base table.

To build covering indexes without bloating the root and intermediate B-Tree nodes, use the INCLUDE clause:

  • Key Columns: Participate in sorting, filtering, joining, and B-Tree navigation.
  • Included Columns (INCLUDE): Stored only at the leaf level. They do not increase the depth or maintenance cost of intermediate B-Tree navigation but satisfy the SELECT projection.

Specialized Indexing Types and When to Deploy Them

Modern relational database engines offer specialized index variants tailored for distinct architectural patterns.

1. Filtered (Partial) Indexes

Filtered indexes include a WHERE predicate directly in the index definition, storing only a subset of table rows.

  • Use Case: Tables with highly skewed distributions—such as indexing only non-processed records where status equals “Pending”, or indexing nullable columns where values are not null.
  • Advantages: Radically smaller index footprints, faster maintenance overhead, and highly accurate statistics for the specific data slice.

2. Unique Indexes

Unique indexes enforce entity integrity at the storage engine level, guaranteeing that no two rows contain identical key values.

  • Use Case: Natural keys, social security numbers, corporate tax identifiers, and system usernames.
  • Performance Benefit: Aside from ensuring data integrity, unique indexes provide the optimizer with absolute cardinality guarantees, enabling deterministic execution plans and early-exit lookups.

3. Columnstore Indexes

Unlike traditional row-oriented B-Trees that store entire records together on data pages, Columnstore indexes organize and store data column by column.

  • Use Case: Analytical workloads (OLAP), data warehousing, and aggregation queries running across millions or billions of rows (SUM, AVG, COUNT).
  • Performance Benefit: High data compression ratios (often 5x to 10x) and massive reductions in disk I/O, as the engine reads only the specific columns requested in the query.

Anti-Patterns: Critical Indexing Mistakes to Avoid

In database tuning, knowing what not to do is just as important as knowing what to build. Below are the most damaging indexing mistakes in production systems:

1. Applying Functions on Indexed Columns (Non-SARGable Queries)

A query is SARGable (Search Argument Able) when the database engine can directly exploit an index seek. Wrapping an indexed column inside a scalar function, mathematical operation, or explicit type conversion forces the engine to evaluate that function for every single row, breaking the B-Tree seek mechanism.

  • Problematic Construct: Wrapping date columns inside formatting functions or substring extractions on strings.
  • Remediation: Rewrite the predicate so the column remains bare on one side of the operator, transforming the search argument into a clean range.

2. Leading Wildcards in String Searches

Using wildcard patterns at the beginning of a string search (such as pattern matching against %Term) prevents the database engine from navigating the B-Tree hierarchy from left to right. This immediately forces a full index scan.

  • Remediation: Use trailing wildcards (Term%) where possible, or deploy Full-Text Search engines when arbitrary substring matching is mandatory.

3. Over-Indexing and the Write Penalty

Every non-clustered index created on a table is not free; it represents a live copy of that data. When an application executes an INSERT, UPDATE, or DELETE:

  • The base table is modified.
  • Every single non-clustered index containing the affected columns must be synchronously updated within the same transaction.

Over-indexing severely throttles write throughput, inflates transaction log generation, and increases concurrency locking and blocking issues.

Maintaining and Monitoring Index Health

Indexes are not “set-and-forget” objects. Over time, continuous data modifications degrade index quality and query performance.

1. Managing Fragmentation

Index fragmentation occurs when data modifications cause page splits, leaving pages half-empty or physically scattered out of logical order across storage disks.

  • Internal Fragmentation: Unused space inside index pages resulting in wasted memory buffer pools and extra disk I/O.
  • External Fragmentation: Physical allocation of pages does not match the logical B-Tree order.

Maintenance Strategy

  • Low Fragmentation (< 10-15%): No action required.
  • Moderate Fragmentation (15% to 30%): Perform an online index Reorganize (defragments leaf pages with minimal locking).
  • High Fragmentation (> 30%): Perform an index Rebuild (drops and recreates the entire B-Tree structure, updating statistics).

2. Statistics Maintenance

The query optimizer relies on distribution statistics associated with indexes to estimate row counts and cost-effective execution paths. If statistics become stale due to high data turnover, the optimizer may choose a full table scan over an index seek, even when a perfect index exists. Ensure automated statistics updates are enabled and augment them with scheduled maintenance on volatile tables.

3. Auditing Unused and Duplicate Indexes

Regularly query dynamic management views and system catalogs to identify unused or redundant indexes.

  • Duplicate Indexes: Indexes with identical key columns in the same order.
  • Redundant Indexes: An index on (Column_A) when another composite index already exists on (Column_A, Column_B).
  • Unused Indexes: Indexes with zero user seeks/scans over extended reporting periods but millions of maintenance writes.

Summary of Core Principles

Designing high-performance database systems requires an intentional, evidence-based approach to indexing:

  1. Design for read-write balance: Every index accelerates reads while adding direct latency to writes. Index only what your query workload actively demands.
  2. Respect the B-Tree hierarchy: Order composite keys methodically and keep search predicates SARGable.
  3. Audit proactively: Continually monitor execution plans, eliminate unused indexes, and keep statistics fresh to ensure the optimizer consistently makes the right choices.

You may also like the following articles: