Clustered and nonclustered indexes

Faster reads have write costs.

UnderstandStudy an examplePractise

Understand the concept

An index is a data structure supporting access patterns. A rowstore clustered index contains table rows at its leaf level; a table has at most one. A table without one is a heap. Nonclustered indexes store keys and a row locator. A wide clustered key can increase other indexes because it is used as their locator.

Indexes cost writes, storage and maintenance. Indexing every column is not a solution. A scan can be correct on tiny lab tables; seeks are not inherently cheaper. Evaluate filters, ordering and selected columns against the real workload. PRIMARY KEY and clustered index are distinct concepts even when defaults combine them.

WHEN TO USE IT

Design an access path for a workload.

COMMON PITFALL

Treating every scan as bad and every seek as good.

01 / PRACTICE

One topic. Three levels of depth.

T-SQL / SQL Server 2019+

SELECT name, type_desc FROM sys.indexes WHERE object_id = OBJECT_ID(N'lab.Product');

Inspect existing index types.

This page does not execute SQL. Practise in the downloadable SqlStudioLab using SSMS. Before rerunning temporary-table examples, remove only the relevant #table. Views, procedures and functions create persistent lab objects; never use a production connection.

02 / YOUR TURN

Try it yourself first.

List existing indexes on Customer.

Show the solution and explanation

Worked solution

SELECT name, type_desc, is_unique FROM sys.indexes WHERE object_id = OBJECT_ID(N'lab.Customer');

This metadata read does not create an index.

RasimAtaberkŞahin

Understand the concept

One topic. Three levels of depth.

Try it yourself first.