Index

Indexes 101: What, Why, and When?

“What Is an Index?”
I get this question a lot, especially from developers and sysadmins who’ve been handed a SQL Server and told, “Keep it running fast.” No pressure.

At a basic level, an index is a roadmap. Instead of scanning every house on the street to find the one you want, you check a list. Indexes help SQL Server find data faster – less scanning, more targeted seeking. All those pages at the back of a technical book? Those are indexes. The same idea applies to tables.

Now, not all indexes are created equal. Some are used every day. Others are…let’s say, special occasion. Let’s walk through them in order from most to least common (based on my experience).

Clustered Indexes

What it is: Defines how the data is stored on disk. SQL Server physically orders the table based on this. One per table.
Why it matters: Every seek operation relies on this index. If you don’t create one, SQL Server makes a hidden “heap”, which adds time to the query.
When to use: Pretty much always, unless you’re loading massive amounts of data fast and don’t care about retrieval speed such as an ETL/ELT process.
Pro tip: Pick a column (or set) that is narrow, static, and unique (like an identity column). Even better, use a column that is frequently used to retrieve sets of data such as date for “Last month’s sales” types of reports.

Non-Clustered Indexes

What it is: Think of these like side tables (they do add to the database size) with pointers back to the main data. You can have many per table, but too many can be an issue.
Why it matters: Great for covering queries. Especially helpful if they include only a few columns.
When to use: When the same query pattern happens often and doesn’t match the clustered key.
Watch out for: Too many non-clustered indexes = slow inserts and updates. Balance is key.

…Included Columns…

What it is: Bonus data tacked onto a non-clustered index. Doesn’t affect sort order but helps avoid expensive trips to the table to find more data.
Why it matters: Lets you “cover” more queries without bloating the main index definition.
When to use: If the query needs extra columns but they don’t need to be sorted.

Filtered Indexes

What it is: Indexes with WHERE clauses.
Why it matters: Small, fast, and efficient for predictable filters.
When to use: Perfect for things like IsActive = 1 or Status = ‘Pending’.

Columnstore Indexes

What it is: Think column-based storage, not row-based. Optimized for analytics.
Why it matters: Game-changing for reporting queries across millions of rows.
When to use: Data warehouses, not your OLTP system.
Bonus: Clustered columnstore = high compression. Great for archiving.

XML Indexes

What it is: Indexes specifically for querying XML data types.
Why it matters: Without them, XML queries crawl.
When to use: If you’re unlucky enough to be storing structured data as XML blobs.

Spatial Indexes

What it is: Indexes that help with geography and geometry data types.
Why it matters: SQL Server can’t efficiently process spatial queries without these.
When to use: Mapping, geolocation apps, routing engines—very niche but powerful. I’ve only seen a spatial index in production once in my career.

The Bottom Line:

Indexing is both art and science. You want just enough to make reads fly and not too many that writes crawl. Think of it like your car with just the right tire pressure—too little and you drag, too much and you lose control.

— Thanks for reading!

Kevin