### [SQL Server]talk about clustered index again

I have wrote two articles about clustered index as below

[SQL SERVER][Memo]Clustered VS NonClustered Indexes

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?

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

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.

Reference

[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?

Using DBCC PAGE

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

DBCC SHOWCONTIG (Transact-SQL)