Cluster Index physically stores data… Myth? Whether true, let’s do the practical!!!

I came across many people saying cluster index physically sort the data in the table with the cluster key. Is it correct, let’s see doing a practical session… 

Lets create a table ClusterIndexOrderDemo with cluster index ie with primary key and with two column sno and fname.

create table ClusterIndexOrderDemo

(

      sno int not null primary key,

      fname varchar(10) not null

)

Command(s) completed successfully.

Now we will be inserting few rows… see the data for sno field, it is not in order.

insert into ClusterIndexOrderDemo

select 15, 'Vaidik' UNION ALL

select 25, 'Aakshat' UNION ALL

select 05, 'Dhanya'

Trace Flag 3604 forces the output to the client (in our case SSMS result pane) and DBCC Page gives you the page details

dbcc TraceOn(3604)

DBCC execution completed.

Now we will be firing DBCC IND – used to know the page details of tables & indexes.

Syntax :

 DBCC IND ( {dbname}, {table_name},{index_id} )Were,

·         dbname : your database;

·         table_name: your table name

·         index_id: for cluster index 1 , for non cluster index its 2

dbcc IND ('testDemo', 'ClusterIndexOrderDemo', 1)

PageFID PagePID     IAMFID IAMPID      ObjectID    IndexID     PartitionNumber PartitionID          iam_chain_type       PageType IndexLevel NextPageFID NextPagePID PrevPageFID PrevPagePID

------- ----------- ------ ----------- ----------- ----------- --------------- -------------------- -------------------- -------- ---------- ----------- ----------- ----------- -----------

1       71          NULL   NULL        309576141   1           1               72057594039828480    In-row data          10       NULL       0           0           0           0

1       70          1      71          309576141   1           1               72057594039828480    In-row data          1        0          0           0           0           0

(2 row(s) affected)

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

You will be getting two rows, check for Page Type column where it is 1 see and note the corresponding page number and file number. Here in our case I am getting  page number as 70 and file id as 1.

Now I can run DBCC Page command. So whats the syntax ?

Dbcc page ({'dbname'|dbid}, filenum, pagenum [,printopt={0|1|2|3}]

Here in our case the database is testDemo, fileId is 1, page id is 70 and print option we are providing as 2 which means all page info dump.

DBCC PAGE ('testDemo',1,70,2)

Pick the offset table from output

OFFSET TABLE:

Row - Offset                        

2 (0x2) - 117 (0x75) (Akshat)               

1 (0x1) - 96 (0x60)  (Vaidik)               

0 (0x0) - 139 (0x8b) (Dhanya)  

Here the 117, 96, 139 are location where as the 2,1,0 are offset.            

Now we will be entering few more record and then will check the location and the offset.

insert into ClusterIndexOrderDemo

select 20, 'Radhika' UNION ALL

select 30, 'Silpa' UNION ALL

select 10, 'Sanjana'

dbcc page ('testDemo',1,70,2)

OFFSET TABLE:

Row - Offset                        

5 (0x5) - 182 (0xb6)  (Silpa)              

4 (0x4) - 117 (0x75)  (Akshat)              

3 (0x3) - 160 (0xa0)  (Radhika)              

2 (0x2) - 96 (0x60)   (Vaidik)              

1 (0x1) - 202 (0xca)  (Sanjana)              

0 (0x0) - 139 (0x8b)  (Dhanya)

Conclusion: So we can see here that the place where record got added that location is not changed at all. Instead based on the clustered index key it maintains the correct order within this Row Offset table.

So Clustered Index physically orders the data within a table? NO

Code award energy, No boost up drink required 

Post Reference: Vikram Aristocratic Elfin Share