SQL Server Indexes for New DBAs: From “What is this?” to “I’ve got this.”

If you’re new to SQL Server and you want query speed without guesswork, learn indexes. This guide walks you from first principles to advanced tuning with practical T-SQL you can try today.

---

1) What an index is (in plain English)

An index is a data structure that helps SQL Server find rows faster. Instead of scanning every row in a table, SQL Server uses an index to jump close to the data and grab only what it needs.

Two main types you’ll use every day:

• Clustered index: defines the physical order of rows in the table. There can be only one per table. The table’s data pages are the leaf of the clustered index.
• Nonclustered index: a separate structure that points to rows in the table. You can have many of these. The leaf of a nonclustered index stores the indexed keys plus a “bookmark” back to the base table.

There’s also columnstore, which is great for analytics, but this post focuses on rowstore indexes (clustered and nonclustered).

---

2) The B-tree, page layout, and what happens under the hood

Indexes are stored as a B-tree (balanced tree):

• Root page: the entry point for lookups.
• Intermediate pages: routing nodes. They narrow the search.
• Leaf pages: where the actual data lives.
  • For a clustered index, the leaf pages are the table’s data pages.
  • For a nonclustered index, the leaf pages contain the index keys and a bookmark back to the base table.

Key storage facts:

• SQL Server organizes data in 8 KB pages.
• Extents group 8 pages (64 KB).
• The tree stays shallow. Depth is usually 2–4 levels even for large indexes, which is why lookups are fast.
• If a row doesn’t fit on a page due to updates, you’ll see page splits and possible fragmentation.

Bookmarks:

• If the table has a clustered index, a nonclustered index leaf stores the clustered key as the bookmark.
• If the table is a heap (no clustered index), a nonclustered index leaf stores a RID (File:Page:Slot).

---

3) Clustered vs. heap: which should you choose?

• Prefer clustered for OLTP tables. You get predictable bookmark lookups, better range scans, and fewer issues with forwarded records.
• Heaps can make sense for:
  • Staging or ETL landing tables with bulk inserts and truncate-reload patterns.
  • Very narrow append-only logs that you query by a nonclustered index anyway.
• A bad clustered key choice hurts. Pick a narrow, stable, ever-increasing key when possible (e.g., BIGINT IDENTITY or a sequential GUID generated with NEWSEQUENTIALID() if you must use GUIDs).

Avoid:

• Wide clustered keys (they bloat every nonclustered index).
• Volatile clustered keys (updates force extra work across all nonclustered indexes).

---

4) Hands-on: build a sample and see the difference

You can run this in tempdb.

USE tempdb;
GO

IF OBJECT_ID('dbo.Orders') IS NOT NULL DROP TABLE dbo.Orders;
CREATE TABLE dbo.Orders
(
    OrderID     INT IDENTITY(1,1) NOT NULL,
    CustomerID  INT NOT NULL,
    OrderDate   DATETIME2(0) NOT NULL,
    Status      CHAR(1) NOT NULL,  -- N=New, S=Shipped, C=Cancelled
    Amount      DECIMAL(12,2) NOT NULL,
    Notes       NVARCHAR(200) NULL
);
GO

-- Make a clustered index on OrderID (narrow, increasing)
CREATE CLUSTERED INDEX CX_Orders_OrderID ON dbo.Orders(OrderID);

-- A useful nonclustered index for date filtering and retrieving status
CREATE NONCLUSTERED INDEX IX_Orders_OrderDate
ON dbo.Orders(OrderDate)
INCLUDE(Status, Amount);
GO

-- Load some demo rows
;WITH n AS (
    SELECT TOP (50000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n
    FROM sys.all_objects
)
INSERT dbo.Orders(CustomerID, OrderDate, Status, Amount, Notes)
SELECT
    ABS(CHECKSUM(NEWID())) % 5000 + 1,
    DATEADD(DAY, -ABS(CHECKSUM(NEWID())) % 365, SYSUTCDATETIME()),
    CHOOSE(ABS(CHECKSUM(NEWID())) % 3 + 1, 'N','S','C'),
    CAST(ABS(CHECKSUM(NEWID())) % 100000 / 100.0 AS DECIMAL(12,2)),
    NULL
FROM n;
GO

-- Compare scan vs seek
SET STATISTICS IO, TIME ON;

-- Likely uses the nonclustered index seek on OrderDate
SELECT SUM(Amount)
FROM dbo.Orders
WHERE OrderDate >= DATEADD(DAY, -7, SYSUTCDATETIME());

-- For a specific order, uses clustered index seek
SELECT OrderID, CustomerID, Amount
FROM dbo.Orders
WHERE OrderID = 12345;

SET STATISTICS IO, TIME OFF;

Watch the execution plans. You should see Index Seek operations and low logical reads compared to full scans.

---

5) Key concepts you’ll use all the time

Selectivity and cardinality

• Selectivity is how well a predicate filters rows. Higher selectivity leads to seeks.
• SQL Server estimates rows using statistics. Accurate stats produce better plans.

SARGability

SARGable means the predicate can use the index efficiently. Good:

WHERE OrderDate >= '2025-08-01'
WHERE CustomerID = 123

Not good (non-SARGable):

WHERE CONVERT(VARCHAR(10), OrderDate, 112) = '20250801'
WHERE Amount + 0 = 19.99
WHERE LEFT(Notes, 3) = 'VIP'

Rewrite to keep the column on the left unmodified, and use search-friendly patterns:

WHERE OrderDate >= '2025-08-01'
WHERE Amount = 19.99
WHERE Notes LIKE N'VIP%'

Covering indexes

If your nonclustered index covers all columns the query needs (keys + INCLUDE), SQL Server can satisfy the query from the index leaf without extra lookups.

Key order matters

Lead with the column most commonly used to filter and that gives the highest selectivity. Add additional key columns to support range filters or ORDER BY. Put pure “return” columns in INCLUDE.

Example:

-- We often filter by CustomerID and a date range, and we select Amount
CREATE NONCLUSTERED INDEX IX_Orders_CustomerDate
ON dbo.Orders(CustomerID, OrderDate)
INCLUDE(Amount, Status);

Ascending and descending

You can define per-column sort direction. Use descending keys to avoid a sort for queries like ORDER BY OrderDate DESC.

CREATE NONCLUSTERED INDEX IX_Orders_DateDesc
ON dbo.Orders(OrderDate DESC)
INCLUDE(Amount);

6) Filtered indexes: smaller and faster when used correctly

Great when most rows do not meet your predicate. Examples:

-- Only shipped orders need fast access
CREATE NONCLUSTERED INDEX IX_Orders_Shipped
ON dbo.Orders(OrderDate)
INCLUDE(Amount)
WHERE Status = 'S';

-- Only recent data
CREATE NONCLUSTERED INDEX IX_Orders_Recent
ON dbo.Orders(OrderDate)
WHERE OrderDate >= DATEADD(DAY, -30, SYSUTCDATETIME());

Caveats:

• The query predicate must logically match the filter, or the index will not be used.
• Parameterized queries may miss filtered indexes due to parameter sniffing. Consider OPTION(RECOMPILE) for critical cases or pass in literals via dynamic SQL.

---

7) Unique indexes and constraints

• PRIMARY KEY and UNIQUE constraints create unique indexes behind the scenes.
• Unique indexes help the optimizer by guaranteeing cardinality and can eliminate distinct sorts.

ALTER TABLE dbo.Orders
ADD CONSTRAINT UQ_Orders_CustDate UNIQUE (CustomerID, OrderDate, OrderID);

8) INCLUDE columns, limits, and storage rules

• Key columns: max 16 columns and 900 bytes total.
• INCLUDE columns: up to 1023 nonkey columns, not part of the 900-byte limit, but the row on the leaf page still must fit within the page size.
• Keep keys narrow. Put wide, rarely filtered columns in INCLUDE.

---

9) When the plan needs a bookmark lookup

If a nonclustered index does not cover the query, SQL Server will:

• Use the nonclustered index to find matching keys.
• Perform a Key Lookup (clustered) or RID Lookup (heap) for each matching row to fetch remaining columns.

Lookups are fine for highly selective predicates, but they get expensive when you touch many rows. You can:

• Add INCLUDE columns to cover the query.
• Accept the lookup if the cost is low.
• Re-shape the query.

---

10) Statistics: your silent partner

• Auto-created and auto-updated stats are on by default on most databases. Keep them on.
• Update stats after large data changes or bulk loads:

-- Update a single index’s stats with full scan
UPDATE STATISTICS dbo.Orders IX_Orders_OrderDate WITH FULLSCAN;

-- Update all stats on a table
UPDATE STATISTICS dbo.Orders WITH FULLSCAN;

-- Or at the database level
EXEC sp_updatestats;

• For partitioned tables, consider incremental statistics.

11) Measuring impact: plans and I/O

Turn on I/O and time:

SET STATISTICS IO, TIME ON;
-- your query
SET STATISTICS IO, TIME OFF;

Look for:

• Logical reads. Lower is better.
• Operators: Index Seek beats Index Scan in most OLTP filters.
• Predicate type in the plan. Residual predicates mean the index helped, but not perfectly.

---

12) Maintenance: fragmentation, fill factor, and page splits

• Fragmentation grows when pages split and rows move around. It hurts large range scans more than point lookups.
• Check it:

SELECT
    db_name() AS DBName,
    OBJECT_NAME(s.[object_id]) AS TableName,
    i.name AS IndexName,
    s.index_type_desc,
    s.avg_fragmentation_in_percent,
    s.page_count
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'SAMPLED') AS s
JOIN sys.indexes AS i
  ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id
WHERE s.page_count > 1000
ORDER BY s.avg_fragmentation_in_percent DESC;

Typical guidance:

• Reorganize when fragmentation is roughly 5–30%.
• Rebuild when >30%.

-- Reorganize
ALTER INDEX IX_Orders_OrderDate ON dbo.Orders REORGANIZE;

-- Rebuild with options
ALTER INDEX ALL ON dbo.Orders
REBUILD WITH (FILLFACTOR = 95, SORT_IN_TEMPDB = ON, ONLINE = ON);  -- ONLINE requires Enterprise or certain editions

Notes:

• FILLFACTOR leaves free space on index pages to reduce splits during inserts. Do not set it too low or you waste space and memory.
• Use SORT_IN_TEMPDB = ON if tempdb is on fast storage.
• Resumable and online rebuilds help in high-availability environments (SQL Server 2017+ for resumable).

---

13) Finding missing and unused indexes

Use with care. The DMVs show suggestions based on past queries. Do not blindly create everything.

-- Missing indexes (high-level)
SELECT TOP (20)
    migs.avg_total_user_cost * (migs.avg_user_impact/100.0) * (migs.user_seeks + migs.user_scans) AS improvement_measure,
    mid.statement, mid.equality_columns, mid.inequality_columns, mid.included_columns
FROM sys.dm_db_missing_index_group_stats migs
JOIN sys.dm_db_missing_index_groups mig ON migs.group_handle = mig.index_group_handle
JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
ORDER BY improvement_measure DESC;

Unused or rarely used indexes:

SELECT
    OBJECT_NAME(i.[object_id]) AS TableName,
    i.name AS IndexName,
    us.user_seeks, us.user_scans, us.user_lookups, us.user_updates
FROM sys.indexes AS i
LEFT JOIN sys.dm_db_index_usage_stats AS us
  ON us.[object_id] = i.[object_id]
 AND us.index_id = i.index_id
 AND us.database_id = DB_ID()
WHERE OBJECTPROPERTY(i.[object_id], 'IsUserTable') = 1
ORDER BY (us.user_seeks + us.user_scans + us.user_lookups) ASC, us.user_updates DESC;

If an index is updated a lot but never read, it may be a drop candidate. Verify with workload knowledge and test.

---

14) Advanced patterns you’ll soon need

Computed columns and indexing them

If your predicate is non-SARGable due to a calculation, move it into a persisted computed column and index that.

ALTER TABLE dbo.Orders
ADD OrderDateInt AS CONVERT(INT, FORMAT(OrderDate, 'yyyyMMdd')) PERSISTED;

CREATE INDEX IX_Orders_OrderDateInt ON dbo.Orders(OrderDateInt);

-- Now searches like this are SARGable
SELECT *
FROM dbo.Orders
WHERE OrderDateInt BETWEEN 20250801 AND 20250815;

Data compression

Row or page compression can reduce I/O and sometimes increase throughput.

ALTER INDEX ALL ON dbo.Orders REBUILD WITH (DATA_COMPRESSION = ROW);

Test on your workload. Compression trades CPU for fewer reads.

Partitioning and aligned indexes

For very large tables, partition by a sensible key (often date) and align all indexes to the same scheme. You can then switch partitions in and out and maintain a single partition at a time.

Join indexing strategy

If you frequently join on CustomerID, ensure both sides have supporting indexes with matching types and compatible key order. Example:

CREATE TABLE dbo.Customers
(
    CustomerID INT NOT NULL PRIMARY KEY CLUSTERED,
    Name NVARCHAR(100) NOT NULL
);
CREATE NONCLUSTERED INDEX IX_Orders_CustomerID ON dbo.Orders(CustomerID, OrderDate);

Sort avoidance

If the query orders by (OrderDate DESC, OrderID DESC), an index on (OrderDate DESC, OrderID DESC) can avoid an explicit sort operator.

---

15) Common mistakes and how to avoid them

• Picking a wide or changing clustered key. Keep it narrow and stable.
• Indexing everything. Each index has a write cost. Add them intentionally, measure impact.
• Ignoring data types. Mismatched types force conversions and block index usage.
• Leading wildcards in LIKE. Use left-anchored patterns, full-text search, or different designs.
• Forgetting statistics. Stale stats lead to bad plans.
• Not reviewing execution plans. Always check how the optimizer is using (or not using) your indexes.

---

16) A simple workflow for adding the right index

1. Identify the slow query and its pattern.
2. Check the plan, reads, and estimated vs actual rows.
3. Choose keys:
   • Lead with the most selective equality filter.
   • Then range filter columns.
   • Add ORDER BY columns if it helps avoid a sort.
   • Put return-only columns in INCLUDE.
4. Create the index in a test environment.
5. Re-run the query and compare logical reads and duration.
6. Verify side effects on write workload.
7. Document the intent of the index in the index name or in extended properties.

Example:

CREATE NONCLUSTERED INDEX IX_Orders_ByCustomerDate_IncludeAmountStatus
ON dbo.Orders(CustomerID, OrderDate)
INCLUDE(Amount, Status)
WITH (FILLFACTOR = 95, SORT_IN_TEMPDB = ON);

17) Quick reference cheat sheet

• Clustered index: the table’s physical order. One per table.
• Nonclustered index: separate structure, many allowed, points back via clustered key or RID.
• Keys: max 16 cols, 900 bytes. Keep them narrow.
• INCLUDE: up to 1023 columns. Use for coverage.
• Filtered index: great for sparse data, queries must match the filter.
• SARGability: keep functions off columns, use matching data types.
• Maintenance: reorganize 5–30%, rebuild above 30%. Consider fill factor and compression.
• Stats: keep them fresh to get good cardinality estimates.
• Measure: STATISTICS IO/TIME and execution plans.
• Don’t over-index. Each extra index adds write overhead.

---

18) Cleanup (optional)

-- If you want to clean up the demo objects
DROP TABLE IF EXISTS dbo.Orders;

Final thought

Indexes are not magic. They are precise tools. When you pick good keys, keep stats fresh, and measure results, your queries will feel instant and your servers will breathe easier.