Worth Corner, Crawley, RH10 7SL

Support Hours - Mon-Sat, 8.00-18.00

Welcome to our SQL Server blog

This blog was created in order to pass on some of our knowledge and advice in the hope that the community might find it useful.

Obviously Microsoft SQL Server is our area of expertise and therefore this is our most thorough blog with everything from the basic through to the the incredibly advanced and, as such, we should have something for everyone within our pages.

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

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

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

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

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

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

Now let’s find our data page:

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

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)

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

(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.

SQL  World  CEO
Kevin  Urquhart

iPhone Selfie

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


© Copyright 2020 SQLTraining Ltd.