Does Clustered Index physically orders the data within a page?

I got new topic (for me) from our user group meeting (17/10/2012). There was a question from Dinesh Priyankara (MVP), what he asked from audience “Does Clustered Index physically order the data within a page? “ Majority of the audience said  'yes' including myself. Then he said it is another myth in SQL server world.  

Then I came and searched on Internet and did small work to break that myth. Here the truth..

“Clustered Index doesn't physically order the data within a page “

This is the script :

SET NOCOUNT ON

USE tempdb

GO

-- Create dbo.Without_Clustered_Index table (Heap Table)

CREATE TABLE dbo.Without_Clustered_Index

       (

              ID int ,

              Test varchar(10)

       )

-- Create  dbo.With_Clustered_Index table

CREATE TABLE dbo.With_Clustered_Index

       (

              ID int ,

              Test varchar(10)

       )

      

-- Create Unique Clustered index on dbo.With_Clustered_Index

CREATE UNIQUE CLUSTERED INDEX UCI_With_Clustered_Index_ID_PK ON dbo.With_Clustered_Index

       (

              ID

       )

-- Insert data into dbo.Without_Clustered_Index table

INSERT INTO dbo.Without_Clustered_Index(ID,Test) VALUES ( 10 ,'Ten')

INSERT INTO dbo.Without_Clustered_Index(ID,Test) VALUES ( 8 ,'Eight')

INSERT INTO dbo.Without_Clustered_Index(ID,Test) VALUES ( 7 ,'Seven')

-- Insert data into dbo.With_Clustered_Index table

INSERT INTO dbo.With_Clustered_Index(ID,Test) VALUES ( 10 ,'Ten')

INSERT INTO dbo.With_Clustered_Index(ID,Test) VALUES ( 8 ,'Eight')

INSERT INTO dbo.With_Clustered_Index(ID,Test) VALUES ( 7 ,'Seven')

SELECT ID,Test

FROM dbo.Without_Clustered_Index

SELECT ID,Test

FROM dbo.With_Clustered_Index

-- Set to instruct the engine to send output to the console;

DBCC TraceOn (3604)

GO

-- List all of a table's data and index pages

DBCC IND ('TempDB','dbo.Without_Clustered_Index',1)

GO

DBCC IND ('TempDB','dbo.With_Clustered_Index',1)

GO

-- 288 and 290 are the data page ID for dbo.Without_Clustered_Index and dbo.With_Clustered_Index tables respectively

-- Look at the contents of database pages

DBCC PAGE ('TempDB',1,288,2) --with tableresults

GO

DBCC PAGE ('TempDB',1,290,2) --with tableresults

GO

-- Insert data into dbo.Without_Clustered_Index table

INSERT INTO dbo.Without_Clustered_Index(ID,Test) VALUES ( 4 ,'Four')

INSERT INTO dbo.Without_Clustered_Index(ID,Test) VALUES ( 3 ,'Three')

-- Insert data into dbo.With_Clustered_Index table

INSERT INTO dbo.With_Clustered_Index(ID,Test) VALUES ( 4 ,'Four')

INSERT INTO dbo.With_Clustered_Index(ID,Test) VALUES ( 3 ,'Three')

-- Look at the contents of database pages

DBCC PAGE ('TempDB',1,288,2) --with tableresults

GO

 

DBCC PAGE ('TempDB',1,290,2) --with tableresults

GO

   

  

-- Update Existing Record from each record

UPDATE dbo.Without_Clustered_Index SET ID = 1 WHERE ID = 4

UPDATE dbo.With_Clustered_Index SET ID = 1 WHERE ID = 4

-- Look at the contents of database pages

DBCC PAGE ('TempDB',1,288,2) --with tableresults

GO

 

DBCC PAGE ('TempDB',1,290,2) --with tableresults

GO

 

  

Conclusion :

In Row offset,  0^th  entry value changed into new record distance value (173 to 193) and 1^st  entry value changed into 173 by removing 154 (154 is the distance value of the ID = 4 value record). We updated ID = 4 into ID= 1

Instead of modifying existing record it adds new record with same value by updating required value.