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, 0th entry value changed into new record distance
value (173 to 193) and 1st
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.
