What an index does

This lesson uses DDI_SQL_Training dataset v1.0.0 on SQL Server 2019 or later. Inspect indexes that help SQL Server find and order product rows.

Run the example

SELECT name AS IndexName, type_desc AS IndexType
FROM sys.indexes
WHERE object_id = OBJECT_ID(N'dbo.Products') AND index_id > 0
ORDER BY index_id;

Read it step by step

1. sys.indexes lists index definitions for the Products object.
2. A clustered index has data rows at its leaf level; a nonclustered index has keys and row locators. The optimizer may seek or scan either structure.
3. The optimizer chooses a plan based on estimated cost; an index existing does not prove a specific query will seek it.

Check the result

These index names are part of the frozen v1.0.0 schema, not measured execution-plan choices.

See an index at work

Listing indexes shows what exists, not what it does. Enable Actual Execution Plan in your editor and run these two queries separately: SELECT OrderID, OrderDate FROM dbo.Orders WHERE OrderID = 100; and SELECT OrderID, OrderDate FROM dbo.Orders WHERE CustomerID = 1;

PK_Orders is keyed on OrderID, so SQL Server can go straight to OrderID 100. No index is keyed on CustomerID, so the second query reads through the clustered index to find its 250 rows. A nonclustered index on CustomerID could allow a seek, at the cost of extra storage and write maintenance.

Practice

List the named indexes on dbo.Customers.

SELECT name AS IndexName, type_desc AS IndexType
FROM sys.indexes
WHERE object_id = OBJECT_ID(N'dbo.Customers') AND index_id > 0
ORDER BY index_id;