Clustered Index Orders Pages Not Rows
Published: Apr 16, 2017
This is something I’ve had mentioned to me a lot recently… that a clustered index ensures that records must be stored in order on the data pages.

But it’s just not true.

A clustered index will likely store records on the data page in clustered key order when rebuilt or if they’ve been inserted in key order, BUT a clustered index does not mean that they are definitely in order on the data page.

In each 8k page there is a page header and footer. The data obviously goes between these.

The footer consists of something called the Slot Array which you can think of as co-ordinates telling you the starting point of each record on the data page. Now it’s this slot array that HAS to be in the correct clustered key order and not the data itself.

Therefore, for example, the data page could have records written in the order Clustered Key 1, 3, then 2… but the slot array would have Clustered Key 1 in Slot ID 0 (it starts at 0), Clustered Key 2 in slot ID 1, and Clustered Key 3 in slot ID 2.

Hopefully that makes sense and will stop people from stating that records must be written in Clustered Key order… it’s the slot array that’s ordered, not the data on the page.

But just for one final clarification, here’s a quick demo:

use tempDB
go

if object_id('slotArray') is not null drop table slotArray
go

create table slotArray
(
  
id int,
  
value char(100),
  
constraint pk_slotArray primary key clustered(id)
)
go

insert into slotArray
select 1, replicate('a', 100)
go

insert into slotArray
select 3, replicate('a', 100)
go

insert into slotArray
select 2, replicate('a', 100)
go


Now let’s find our data page:

dbcc ind ('tempDB', 'slotArray', 1)
go


We’re only interested in the data pages (PageType = 1… 10 is an IAM page… there’s an excellent post by Paul Randal explaining DBCC Ind and DBCC Page here):

dbcc traceon (3604)
go

dbcc page('tempDB', 1, 257, 2)
go


(Note that we use TF 3604 to send the output of DBCC Page to the Messages window… I’ve covered this in another post. Also that we use output format 2 in order to best show the Slot Array).

At the very bottom you’ll find the slot array (called the Offset Table in the output) and you can clearly see that record ID 1 is 96 bytes into the data page, ID 2 is at 318 bytes, and ID 3 is 207… therefore the records were written to the page in the exact order that we entered them (NOT clustered order), however the slot array was updated to accommodate this because it’s the Slot Array that has to be in Clustered Key order and not the data itself.

Hope that clears that up?
Leave a Comment
Your email address will not be published. All fields are mandatory.
NB: Comments will only appear once they have been moderated.

Your SQL Trainer
Kevin Urquhart

I am a SQL Server DBA, Architect, Developer, and Trainer for SQL Training. This is my blog in which I’m simply trying to share my SQL knowledge and experiences with the world.

Kevin Urquhart
SQL Server Consultant, London

Categories


Archives


Copyright © 2018 London SQL Server Training Courses @ SQL Training | Website Design From Scott Heron