Physical Order of Rows: What a Clustered Index Does

The physical order of rows in a table depends on one thing: whether the table has a clustered index. Without one, the rows sit in the order they arrived. With one, SQL Server keeps them sorted by the index key. A shopping mall works the same way, because its stores are arranged by unit number, not by name.

The Mall Analogy

Walk through a mall and unit 410 sits next to unit 411, whatever the two stores sell. The unit number is the key, so the mall is a clustered table. A paperback is another example: page 100 follows page 99, so you never search for what comes next.

Two Tables, Twelve Stores

To see this, build two tables with the same twelve rows. MallHeap has no index. MallClustered has a primary key, which is clustered by default. The Filler column makes each row about 1.9 KB, so only four rows fit on an 8 KB page. Run this in any test database. Every result below comes from SQL Server 2025.

CREATE TABLE MallHeap (UnitNo INT NOT NULL, StoreName VARCHAR(30) NOT NULL, Filler CHAR(1900) NOT NULL DEFAULT '');
CREATE TABLE MallClustered (UnitNo INT NOT NULL PRIMARY KEY CLUSTERED, StoreName VARCHAR(30) NOT NULL, Filler CHAR(1900) NOT NULL DEFAULT '');
GO

INSERT INTO MallHeap (UnitNo, StoreName) VALUES
  (410, 'Tea Corner'), (118, 'Book Nook'), (305, 'Kite Shop'), (412, 'Toy Barn'),
  (101, 'Spice Market'), (233, 'Shoe Repair'), (411, 'Sari House'), (120, 'Plant Nursery'),
  (340, 'Music Box'), (202, 'Bike Shop'), (119, 'Pet Supplies'), (315, 'Stationery');
INSERT INTO MallClustered (UnitNo, StoreName) VALUES
  (410, 'Tea Corner'), (118, 'Book Nook'), (305, 'Kite Shop'), (412, 'Toy Barn'),
  (101, 'Spice Market'), (233, 'Shoe Repair'), (411, 'Sari House'), (120, 'Plant Nursery'),
  (340, 'Music Box'), (202, 'Bike Shop'), (119, 'Pet Supplies'), (315, 'Stationery');
GO

Where the Rows Land

The helper sys.fn_PhysLocFormatter turns the hidden %%physloc%% value into a file, page and slot. It’s undocumented, so keep it in test databases. The heap returns the stores in arrival order, four to a page, on pages 376, 377 and 378.

SELECT UnitNo, StoreName, sys.fn_PhysLocFormatter(%%physloc%%) AS Location FROM MallHeap;

The clustered table returns the stores sorted by unit number, and units 101, 118 and 119 share page 384. Now look at the page numbers further down. Unit 120 is on page 389 and unit 233 is on page 388. Page numbers don’t follow key order. The index links each page to the next, and a page number is only an address.

SELECT UnitNo, StoreName, sys.fn_PhysLocFormatter(%%physloc%%) AS Location FROM MallClustered;

How Many Pages

The heap needs three pages for twelve rows of four. The clustered table needs five. Its rows arrived out of order. A row that belongs in the middle of a full page forces a split. To compare, create a third table and insert the same rows already sorted, then count pages.

CREATE TABLE MallSorted (UnitNo INT NOT NULL PRIMARY KEY CLUSTERED, StoreName VARCHAR(30) NOT NULL, Filler CHAR(1900) NOT NULL DEFAULT '');
INSERT INTO MallSorted (UnitNo, StoreName) VALUES
  (101, 'Spice Market'), (118, 'Book Nook'), (119, 'Pet Supplies'), (120, 'Plant Nursery'),
  (202, 'Bike Shop'), (233, 'Shoe Repair'), (305, 'Kite Shop'), (315, 'Stationery'),
  (340, 'Music Box'), (410, 'Tea Corner'), (411, 'Sari House'), (412, 'Toy Barn');

SELECT OBJECT_NAME(object_id) AS TableName, index_type_desc AS StorageType, page_count AS Pages, record_count AS Records
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED')
WHERE index_level = 0 AND OBJECT_NAME(object_id) LIKE 'Mall%'
ORDER BY TableName;

MallSorted needs only three pages, the same as the heap. Arrival order changes the layout, so loading a clustered table in key order keeps it compact. The transaction log confirms the cause. This query uses an undocumented function, so run it in a test database only.

SELECT PARSENAME(AllocUnitName, 2) AS TableName, COUNT(*) AS PageSplits
FROM sys.fn_dblog(NULL, NULL)
WHERE Operation = 'LOP_DELETE_SPLIT' AND AllocUnitName LIKE 'dbo.Mall%'
GROUP BY PARSENAME(AllocUnitName, 2)
ORDER BY TableName;

It lists four page splits for MallClustered. MallSorted and MallHeap show none.

One Clustered Index per Table

Rows can be in only one order, so each table gets one clustered index. A second one fails with Msg 1902: SQL Server cannot create more than one clustered index on table MallClustered.

CREATE CLUSTERED INDEX CIX_MallClustered_StoreName ON MallClustered (StoreName);

A primary key creates the clustered index for you unless you write NONCLUSTERED. A table with no clustered index is a heap. The catalog view sys.indexes shows both: MallClustered has a CLUSTERED index, and MallHeap shows HEAP with no index name.

SELECT OBJECT_NAME(object_id) AS TableName, ISNULL(name, '(none)') AS IndexName, type_desc AS IndexType, is_primary_key AS IsPrimaryKey
FROM sys.indexes
WHERE object_id IN (OBJECT_ID('MallHeap'), OBJECT_ID('MallClustered'))
ORDER BY TableName;

Adding an Index to a Heap

Add a clustered index to MallHeap and SQL Server rebuilds the table in key order. The twelve rows now sit sorted by unit number, still on three pages.

CREATE CLUSTERED INDEX CIX_MallHeap_UnitNo ON MallHeap (UnitNo);

SELECT UnitNo, StoreName, sys.fn_PhysLocFormatter(%%physloc%%) AS Location FROM MallHeap;

Don’t Count on Order

Without ORDER BY, the clustered table returned its rows in key order. That is how this small scan ran, and it isn’t a promise. Only ORDER BY guarantees an order. The full story is in Why a Clustered Index Does Not Guarantee Order Without ORDER BY.

SELECT UnitNo, StoreName FROM MallClustered ORDER BY UnitNo DESC;

Clean Up

Remove the three sample tables when you finish.

DROP TABLE MallHeap;
DROP TABLE MallClustered;
DROP TABLE MallSorted;

What to Remember

A clustered index puts the rows themselves in key order, and a table can have only one. A heap keeps arrival order. Load rows in key order when you can, and write ORDER BY whenever the order matters.

To go further, read Heap or Clustered Index: When a Heap Table Is the Right Choice, Narrow Index Keys: Why the Clustered Key Width Matters Everywhere and RID Lookups on Heaps vs Key Lookups on Clustered Tables.

A clustered index is not a sorted copy of the table, it is the table stored in key order.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.