I have wrote two articles about clustered index as below
Today, I would like to talk about physically ordering or sorting of rows in clustered index.
In a few days ago, my friend(he is a native english speaker) asked me why our clustered index got more fragmentation when he updated or inserted a clustered index key in sql2014.
Is this as expect?
My short answer as below
Yep and I will make a simple test to prove it.
create table testCIordering ( c1 int not null primary key clustered ) insert into testCIordering values(1),(2),(3),(4),(6),(7) --Consultando a DMF select allocation_unit_type_desc,extent_file_id,extent_page_id,is_iam_page, is_allocated,allocated_page_page_id,is_mixed_page_allocation,page_type_desc, next_page_page_id,previous_page_page_id,is_page_compressed,has_ghost_records from sys.dm_db_database_page_allocations(DB_ID(),OBJECT_ID('testCIordering'),null,null,'DETAILED')
okay, I got file and page ID of testCIordering in Table
So, I will enable traceflag 3604 first then call DBCC PAGE command to show more information for me.
DBCC TRACEON(3604); DBCC PAGE (DemoQueryStore, 1, 47776, 1);
No surprise, physically ordering of rows point to our clustered index key.
Now, I will insert a new row into our exists clustered index as below
--I did not keep ordering of rows insert into testCIordering values(5) select * from testCIordering
DBCC PAGE (DemoQueryStore, 1, 47776, 1);
As you can see very clearly in the above output that the new row which is not in between and greater than the address of the last row.
The memory address of the last 2 rows is unchanged(140 and 151),
but new row is not accommodated in between as per the sort order but at the end.
dbcc showcontig('testCIordering') with tableresults
Nice count(BestCount = ActualCount) and no LogicalFragmentation.
Let’s added more records of random into the table and see what happened
declare @max int=3000 declare @curval int=0 while (@max>0) begin set @curval=ROUND(((1000600 - 7 -1) * RAND() + 7), 0) if not exists(select 1 from testCIordering where c1= @curval) begin insert into testCIordering values(@curval) set @max=@max-1; end end
As you can see two points in the above output,
First is remember to keep ordering of rows to avoid more fragmentation when you run insert or update.
Second is clustered index do not guarantee physically ordering or sorting of rows.