1. The Physical Foundation

To understand indexing, you must first understand how SQL Server stores data on the disk.

Heaps vs. B-Trees

Every table in SQL Server is physically stored in one of two ways:

1. Heap: A pile of unordered data. New rows are thrown into the first available empty spot.

   • Pros: Very fast inserts (no sorting overhead).

   • Cons: excruciatingly slow reads (must read the entire table to find anything).

2. B-Tree (Balanced Tree): A hierarchical tree structure that keeps data sorted. This is what an index is.

---

2. Core Index Types

A. Clustered Index

• Concept: The Clustered Index is the table. It sorts the actual data rows physically on the disk.

• Limit: 1 per table (data can only be sorted in one physical order).

• Analogy: A dictionary. The words are the index, but they are also the content. You don't look up "Apple" to find a page number; you look up "Apple" and the definition is right there.

• Best Practice: Pick a key that is Narrow, Static, Unique, and Ever-Increasing (e.g., INT IDENTITY).

  • Corner Case: If you don't define a Primary Key, the table remains a Heap. If you define a PK, it automatically becomes the Clustered Index unless specified otherwise.

B. Non-Clustered Index

• Concept: A separate structure stored alongside the table. It contains the Index Key + a "pointer" to the actual data row.

• Limit: Up to 999 per table.

• Analogy: The index at the back of a textbook. It lists topics (Keys) and page numbers (Pointers).

• The Pointer:

  • If the table is a Heap, the pointer is a RID (Row ID: FileID:PageID:SlotID).

  • If the table has a Clustered Index, the pointer is the Clustered Index Key.

  • Performance Note: This is why a wide Clustered Key (like a GUID) is bad—it makes every Non-Clustered index significantly larger because they all must store that GUID as the pointer.

---

3. Advanced Index Types

C. Filtered Index

A Non-Clustered index with a WHERE clause.

• Use Case: You have a Users table with 10 million rows, but you only query the Active ones (5% of data).

    CREATE NONCLUSTERED INDEX IX_Users_Active ON Users(Email) WHERE IsActive = 1;
  

• Pros: Tiny storage footprint; lightning-fast maintenance.

• Corner Case: You cannot use filtered indexes if your application uses inconsistent SET options (like QUOTED_IDENTIFIER).

D. Columnstore Index

Designed for Data Warehousing (OLAP), not transaction processing (OLTP).

• Concept: Rotates the table 90 degrees. Instead of storing row-by-row, it stores column-by-column.

• Pros: massive compression (10x) and speed for aggregations (SUM, AVG).

• Cons: Terrible for single-row lookups or frequent updates.

E. Unique Index

Ensures data integrity. Can be Clustered or Non-Clustered.

• Note: Constraints (PRIMARY KEY, UNIQUE) are logical concepts; Indexes are the physical implementation. A UNIQUE constraint builds a Unique Index.

---

4. Critical Performance Concepts

Seek vs. Scan (The Golden Rule)

• Index Seek: The engine uses the B-Tree to jump directly to the specific row(s). (Goal)

• Index Scan: The engine reads every single row in the index to find the data. (Avoid)

Key Lookup (Bookmark Lookup)

This happens when a Non-Clustered Index finds the row, but doesn't have all the columns requested.

1. SQL seeks the Non-Clustered Index.

2. It finds the pointer (Clustered Key).

3. It jumps to the Clustered Index (the table) to grab the missing columns.

• Cost: Expensive! If you do this for 10,000 rows, it's slower than just scanning the whole table.

• Solution: Covering Index. Use INCLUDE to store extra data in the leaf nodes without sorting by them.

  SQL

    CREATE INDEX IX_Person_Name ON Person(LastName) INCLUDE (FirstName, Email);
  

The Tipping Point

Even if you have an index, SQL Server might ignore it.

• If your query retrieves more than ~25-33% of the rows (or pages), the optimizer effectively says, "It's faster to just read the whole table than to jump back and forth using the index." It will force a Table Scan.

---

5. Corner Cases & "Gotchas"

1. SARGability (Search ARGument ABLE)

For an index to be used, the predicate must be SARGable.

• Bad (Index Scan): Applying a function to the column.

  WHERE YEAR(OrderDate) = 2023 -> The engine must calculate YEAR() for every single row.

• Good (Index Seek):

  WHERE OrderDate >= '2023-01-01' AND OrderDate < '2024-01-01'

• Bad: WHERE Name LIKE '%Smith' (Leading wildcard acts like a function).

• Good: WHERE Name LIKE 'Smith%'.

2. Implicit Conversions

If you compare a VARCHAR column to an NVARCHAR variable, SQL Server implicitly converts the column to match the variable (higher precedence). This wraps the column in a conversion function, killing SARGability.

• Result: A silent performance killer where an Index Seek turns into a Scan.

3. The Hidden "Uniquifier"

If you create a Clustered Index that is not unique, SQL Server secretly adds a 4-byte integer column called a "uniquifier" to duplicate keys to distinguish them. This is hidden overhead you can't see or query easily.

4. NULLs in Indexes

• SQL Server does index NULL values (unlike Oracle B-Trees).

• They are treated as the "lowest" possible value and stored at the beginning of the index.

5. Page Splits

If you insert a row into the middle of a full index page (e.g., adding User ID 500 when the page holds 400-600 and is full), SQL Server must split the page. It moves half the data to a new page.

• Impact: Creates Fragmentation and generates massive Transaction Log activity.

• Mitigation: Use FILLFACTOR (e.g., 90%) to leave 10% empty space on pages for future growth.

---

6. Summary: Pros & Cons

---

7. Checklist for Developers

Before creating an index, answer these 5 questions:

1. Selectivity: Does the column have many unique values? (Indexing a 'Gender' column is usually useless; indexing 'SocialSecurityNumber' is perfect).

2. Workload: Is this table Read-Heavy or Write-Heavy? (Don't over-index a logging table).

3. Covering: Can I use INCLUDE to satisfy the query completely (avoiding Key Lookups)?

4. Key Order: For composite indexes (Column A, Column B), are you filtering by A?

   • Index on (Last, First) supports searching by Last AND Last + First.

   • It does NOT support searching by First alone.

5. Maintenance: Do I have a job to Rebuild (if frag > 30%) or Reorganize (if frag > 5%) this index?