Clustered and nonclustered indexes

This lesson uses DDI_SQL_Training dataset v1.0.0 on SQL Server 2019 or later. Compare index types in the frozen product table.

Run the example

SELECT name AS IndexName, type_desc AS IndexType, is_unique AS IsUnique
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. The clustered index organizes data rows by key at its leaf level.
2. A nonclustered index has its own key structure and points to rows through a locator.
3. is_unique describes enforced key uniqueness, not whether the optimizer will use the index.

Check the result

A table can have one clustered index and multiple nonclustered indexes.

Practice

Inspect index type and uniqueness on dbo.Orders.

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