[SQL Server]talk about clustered index again

I have wrote two articles about clustered index as below

[SQL SERVER][Memo]Clustered VS NonClustered Indexes

[SQL SERVER][Memo]再談 Clustered Index.

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

--Consultando a DMF
select allocation_unit_type_desc,extent_file_id,extent_page_id,is_iam_page,
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 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)
    set @curval=ROUND(((1000600 - 7 -1) * RAND() + 7), 0)
	if not exists(select 1 from testCIordering where c1= @curval)
	  insert into testCIordering values(@curval)	 
      set @max=@max-1;

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.



[SQL SERVER][Memo]Clustered VS NonClustered Indexes

[SQL SERVER][Memo]再談 Clustered Index

SQL Server 2012: sys.dm_db_database_page_allocations

SQL SERVER 2012 – sys.dm_db_database_page_allocations – dynamic management function

Inside the Storage Engine: What’s in the buffer pool?


More undocumented fun: DBCC IND, DBCC PAGE, and off-row columns

SQL SERVER – Random Number Generator Script – SQL Query

Create Your Own RANDBETWEEN Function in T-SQL