Advanced Database Indexing Strategies

What is Advanced Indexing?

While basic indexes act like a book’s index (mapping values to page numbers), Advanced Indexing is about restructuring how the database reads data to minimize Disk I/O—the most expensive operation in database management.

1. The Covering Index (Index Only Scan)

When a query requests columns that aren’t in the index, the DB must perform a “Key Lookup” or “Bookmark Lookup” to fetch the rest of the row from the data pages.

A Covering Index uses the INCLUDE clause to store extra non-key columns directly in the index’s leaf nodes.

-- Before: Requires a lookup for 'TotalAmount'
CREATE INDEX IX_OrderDate ON Orders(OrderDate);

-- After: Satisfies the query entirely from the index
CREATE INDEX IX_OrderDate_Covering ON Orders(OrderDate) INCLUDE (TotalAmount, CustomerId);

2. Filtered Indexes

A Filtered Index is a non-clustered index that includes a WHERE clause. It allows you to index only a subset of rows, saving space and making updates faster.

• Use Case: Indexing a column where 90% of values are NULL or where you only ever care about “Active” records.

CREATE INDEX IX_ActiveProducts ON Products(Price) WHERE IsActive = 1;

3. Columnstore Indexes

Traditional indexes are Rowstore (data is saved row by row). Columnstore indexes store data by column.

• Strength: High compression (up to 10x) and massive performance gains for aggregations (e.g., SUM, AVG) on billions of rows.
• Environment: Ideal for Data Warehousing and Analytical (OLAP) workloads.

Fragmentation: The Silent Performance Killer

As data is modified, the physical order of pages on disk begins to drift from the logical order of the index.

• Internal Fragmentation: Wasted space inside a page.
• External Fragmentation: Pages are physically scattered.

Solution:

• Reorganize: Desegments the index (lightweight).
• Rebuild: Drops and recreates the index (heavyweight but more effective).

Practice Exercise

Identify why a query SELECT Name FROM Users WHERE Age > 25 might be slow even if there is an index on Age. Design a covering index to fix it.

Answer

The Analysis

The engine uses the index on Age to find the correct rows, but then it has to perform a Key Lookup to the main table for every single match to retrieve the Name. If there are 100,000 users over age 25, that’s 100,000 extra I/O operations.

The Solution: A Covering Index

CREATE INDEX IX_Users_Age_Covering ON Users(Age) INCLUDE (Name);

Why This Works

1. Eliminates Lookups: The Name is now physically stored alongside the Age inside the index structure.
2. Sequential Read: The engine can perform a single, fast sequential scan of the index to find all matching names.
3. Memory Efficiency: The index is significantly smaller than the full table, meaning more of it can fit in the database’s RAM (Buffer Pool).

Summary

Indexing is the art of balancing read speed vs. write overhead.

• Use Covering Indexes to kill lookups.
• Use Filtered Indexes for sparse data.
• Use Columnstore for analytics.
• Always monitor Fragmentation to ensure your performance doesn’t degrade over time.