Clustered and Nonclustered Indexes in SQL Server

HEAP                : table with NO clustered index. Rows stored in no order.
CLUSTERED INDEX     : the table ITSELF is stored sorted by the key.
                      Leaf level = the actual data rows.  Only ONE per table.
                      (Like a phone book sorted by last name.)
NONCLUSTERED INDEX  : a separate sorted structure with key columns + a pointer
                      to the row (clustered key, or RID for a heap).
                      Up to 999 per table.
                      (Like the index at the back of a book.)

PRIMARY KEY creates a CLUSTERED index by default (unless one already exists or you say NONCLUSTERED).

The demo data on this site is deliberately small (300 orders and 50 customers) so the script runs instantly. With so few rows, SQL Server may choose a scan where a big table would use a seek, and the logical reads are tiny. To see the real performance difference, change TOP (300) to TOP (300000) and TOP (50) to TOP (50000) in the demo data script.

Run the sample database script first. Every query below was run on that data, and the Output under each query is the real result.

USE SqlLearningDb;
GO

Lesson tables: the same data stored as a Heap and as a Clustered table

DROP TABLE IF EXISTS dbo.OrdersHeap;
DROP TABLE IF EXISTS dbo.OrdersClustered;

SELECT * INTO dbo.OrdersHeap      FROM dbo.BigOrdersData;   -- SELECT INTO creates a heap
SELECT * INTO dbo.OrdersClustered FROM dbo.BigOrdersData;
GO

(300 rows affected)
(300 rows affected)

Part A: Heap vs Clustered

Turn one copy into a clustered table

ALTER TABLE dbo.OrdersClustered ADD CONSTRAINT PK_OrdersClustered PRIMARY KEY CLUSTERED (OrderID);

Compare structures (heap has index_id = 0, clustered = 1)

SELECT OBJECT_NAME(object_id) AS TableName, index_id, name, type_desc
FROM sys.indexes
WHERE object_id IN (OBJECT_ID('dbo.OrdersHeap'), OBJECT_ID('dbo.OrdersClustered'));

SET STATISTICS IO ON;
GO

Find One order by ID

SELECT * FROM dbo.OrdersHeap      WHERE OrderID = 123;   -- Table Scan  : reads ALL pages
SELECT * FROM dbo.OrdersClustered WHERE OrderID = 123;   -- Clustered Index Seek : 3 reads

Table 'OrdersHeap'. Scan count 1, logical reads 6, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

Table 'OrdersClustered'. Scan count 0, logical reads 2, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

Range query: clustered index returns rows already in order (no Sort operator)

SELECT OrderID, Amount FROM dbo.OrdersClustered WHERE OrderID BETWEEN 100 AND 110 ORDER BY OrderID;
SELECT OrderID, Amount FROM dbo.OrdersHeap      WHERE OrderID BETWEEN 100 AND 110 ORDER BY OrderID;   -- Scan + Sort
GO

SET STATISTICS IO OFF;
GO

Table 'OrdersClustered'. Scan count 1, logical reads 3, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'OrdersHeap'. Scan count 1, logical reads 6, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

Part B: Nonclustered Index + Lookups

Nonclustered index on both tables

CREATE NONCLUSTERED INDEX IX_OrdersHeap_CustomerID      ON dbo.OrdersHeap (CustomerID);
CREATE NONCLUSTERED INDEX IX_OrdersClustered_CustomerID ON dbo.OrdersClustered (CustomerID);
GO

SET STATISTICS IO ON;
GO

Heap: Index Seek + Rid Lookup (pointer = File:Page:Slot)

SELECT OrderID, CustomerID, OrderDate, Amount FROM dbo.OrdersHeap WHERE CustomerID = 7;

Table 'OrdersHeap'. Scan count 1, logical reads 6, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

Clustered: Index Seek + Key Lookup (pointer = clustered key OrderID)

SELECT OrderID, CustomerID, OrderDate, Amount FROM dbo.OrdersClustered WHERE CustomerID = 7;

Table 'OrdersClustered'. Scan count 1, logical reads 8, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

Only columns that exist in the Nc index -> No lookup at all.

(OrderID is in every NC index automatically, because it is the clustered key.)

SELECT OrderID, CustomerID FROM dbo.OrdersClustered WHERE CustomerID = 7;

Table 'OrdersClustered'. Scan count 1, logical reads 2, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

Tipping Point: when too many rows match, lookups become more expensive than a scan,

so the optimizer ignores the NC index and scans the table.

SELECT OrderID, CustomerID, OrderDate, Amount
FROM dbo.OrdersClustered WHERE CustomerID BETWEEN 1 AND 20;       -- ~40% of rows -> Clustered Index Scan
GO

SET STATISTICS IO OFF;
GO

Table 'OrdersClustered'. Scan count 1, logical reads 8, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

Part C: Primary Key Nonclustered + a Different clustered key

Sometimes the best clustered key is not the PK.

DROP TABLE IF EXISTS dbo.OrdersByDate;
SELECT * INTO dbo.OrdersByDate FROM dbo.BigOrdersData;
GO

-- PK enforced by a nonclustered index
ALTER TABLE dbo.OrdersByDate ADD CONSTRAINT PK_OrdersByDate PRIMARY KEY NONCLUSTERED (OrderID);

-- Table physically ordered by OrderDate (great for date-range reports)
CREATE CLUSTERED INDEX CIX_OrdersByDate_OrderDate ON dbo.OrdersByDate (OrderDate, OrderID);
GO

SET STATISTICS IO ON;
SELECT COUNT(*), SUM(Amount) FROM dbo.OrdersByDate    WHERE OrderDate >= '2025-03-01' AND OrderDate < '2025-04-01';  -- range seek, few pages
SELECT COUNT(*), SUM(Amount) FROM dbo.OrdersClustered WHERE OrderDate >= '2025-03-01' AND OrderDate < '2025-04-01';  -- full scan
SET STATISTICS IO OFF;
GO

(300 rows affected)

Table 'OrdersByDate'. Scan count 1, logical reads 2, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

Table 'OrdersClustered'. Scan count 1, logical reads 8, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

Part D: Choosing a Good Clustered Key (n.u.s.e.)

  Narrow       : small data type (INT/BIGINT), because it is copied into EVERY NC index
  Unique       : otherwise SQL Server adds a hidden 4-byte "uniquifier"
  Static       : never updated (updating it moves the row and updates all NC indexes)
  Ever-increasing : IDENTITY / SEQUENCE -> inserts go at the end, little fragmentation
A random GUID (NEWID()) as clustered key = page splits + fragmentation.

DROP TABLE IF EXISTS dbo.GuidKeyDemo;
DROP TABLE IF EXISTS dbo.IntKeyDemo;

CREATE TABLE dbo.GuidKeyDemo (ID UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID() PRIMARY KEY CLUSTERED, Payload CHAR(200) NOT NULL DEFAULT 'x');
CREATE TABLE dbo.IntKeyDemo  (ID INT IDENTITY(1,1)  NOT NULL                PRIMARY KEY CLUSTERED, Payload CHAR(200) NOT NULL DEFAULT 'x');

-- Insert 200 rows one by one into each (simulates an OLTP app)
SET NOCOUNT ON;
DECLARE @i INT = 0;
WHILE @i < 200
BEGIN
    INSERT INTO dbo.GuidKeyDemo DEFAULT VALUES;
    INSERT INTO dbo.IntKeyDemo  DEFAULT VALUES;
    SET @i += 1;
END;
SET NOCOUNT OFF;

SELECT OBJECT_NAME(object_id) AS TableName,
       CAST(avg_fragmentation_in_percent AS DECIMAL(5,2))   AS FragPct,
       page_count,
       CAST(avg_page_space_used_in_percent AS DECIMAL(5,2)) AS PageFullnessPct
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'SAMPLED')
WHERE object_id IN (OBJECT_ID('dbo.GuidKeyDemo'), OBJECT_ID('dbo.IntKeyDemo')) AND index_id = 1;
-- GUID: high fragmentation, more pages, half-empty pages.  INT IDENTITY: ~0% fragmentation.
-- If you need GUIDs, use NEWSEQUENTIALID() or keep the GUID in a NONCLUSTERED index.
GO

Part E: Space Used by Each Table

EXEC sp_spaceused 'dbo.OrdersHeap';
EXEC sp_spaceused 'dbo.OrdersClustered';
EXEC sp_spaceused 'dbo.OrdersByDate';
GO

Summary

PRACTICE QUESTIONS 1. Convert dbo.OrdersHeap into a clustered table on OrderID. What happens to IX_OrdersHeap_CustomerID? 2. Find the CustomerID range where the optimizer switches from Seek+Lookup to Scan. 3. Create a nonclustered index on (Region) and explain why it is rarely used (only 4 distinct values). 4. Repeat PART D with NEWSEQUENTIALID() and compare fragmentation.