Logical Order NOT Physical Order

There is a common misconception, that your data will physically be stored in the order determined by your Clustered Index, but this is not true. A CI in SQL will logically order your data, but not physically order it. Take the table below as an example...

CREATE TABLE LogicalOrderDemo
(
Entry_ID  INT,
Large_Column NCHAR(500)
);

CREATE CLUSTERED INDEX CI__LogicalOrderDemo ON LogicalOrderDemo(Entry_ID);

INSERT INTO LogicalOrderDemo
VALUES (2,'This is a large column'),
(3,'This is a large column'),
(4,'This is a large column'),
(5,'This is a large column');

...If we now use sys.fn_physlocformatter (see this post for more details) to find the page number, and then look inside the page using DBCC PAGE, to view the row offset table...

SELECT sys.fn_PhysLocFormatter(%%physloc%%), *
FROM LogicalOrderDemo;

DBCC TRACEON(3604)

DBCC PAGE(Adventureworks2008,1,29012,1);

...If you scroll to the bottom of the output, you will see a row offset table, similar to the one below. The row offset table tells SQL the physical offset of each slot, from the beginning of the page. A slot is a logical container for a row. The offsets are in reverse order, so Slot 0 holds the first row (With an Entry_ID of 2)...

...So now, lets go ahead and insert another row. This time, we will insert a row with an Entry_ID value of 1. As we have a Clustered Index on this column, then we know that SQL will insert it into Slot 0, in order to enforce a logical order to the rows, but will it physically re-order the data on the page? Lets see...

...No! You can see from the offset table that it has inserted the row at the end of the page. You can interpret this because Slot 0 will contain our new data, but its offset value is the largest in the table, meaning that it is furthest away from the beginning of the page.

In reality, it has inserted it in the first free row space on the page, but as we have not deleted any data, in our case, that is at the end.

Find my book, Pro SQL Server Administration on Amazon -

America

United Kingdom