Does Clustered Index Store Records in Physical Order???
I don't know about you but frankly speaking I heard from everyone that "Clustered Index Physically sorts the data inside Table" .Today I'm going to do some R & D and will see that it's true or just a myth.
To do so I'm going to create a database called DBTEST and then I'll create a table as TestTable with
column ID with primary key and name with char. As we all know that by default primary key creates clustered index on that table. Below is the script for same.
IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'DBTEST')
DROP DATABASE[DBTEST]
CREATE DATABASE [DBTEST]
CREATE TABLE TESTTABLE(ID INT PRIMARY KEY, NAME CHAR(30))
INSERT INTO TESTTABLE VALUES(11,'AMIT'),(12,'BILLU')
Now using DBCC IND will get the pageID as below:
DBCC IND ('DBTEST','TESTTABLE', -1);
Now using DBCC PAGE command we will see the page information as below:
DBCC TRACEON(3604)
DBCC PAGE('DBTEST',1,154,2)
Look at the result in below screen shot:
Now I'm going to insert some more records and as per our assumption is should sort the data in physical order as it has clustered index on it.
INSERT INTO TESTTABLE VALUES(1,'AMIT'),(6,'BILLU'),(3,'SACHIN'),(2,'SUJAN')
Now using DBCC IND we will see the pageID as below:
DBCC IND ('DBTEST','TESTTABLE', -1);
Now using DBCC PAGE command we will see the page information as below:
DBCC TRACEON(3604)
DBCC PAGE('DBTEST',1,154,2)
From this screen shot we can see that the location of records has not been change. I mean now the row 4 and 5 has the data (11,'AMIT'),(12,'BILLU') and the location is still 96 and 137.
So,The short answer to this myth is there is something called "Row offset Array" in a PAGE which manages the order of rows in a PAGE and data resides in order in which they are dumped into memory.
Lets see the memory dump for same.
From above screen shot it is clear that the records are still in the order in which they are dumped.
Please comment here If I'm missing anything over here as I also don't have much idea about Internal memory structure of SQL Server.
Thanks!!!!!
I don't know about you but frankly speaking I heard from everyone that "Clustered Index Physically sorts the data inside Table" .Today I'm going to do some R & D and will see that it's true or just a myth.
To do so I'm going to create a database called DBTEST and then I'll create a table as TestTable with
column ID with primary key and name with char. As we all know that by default primary key creates clustered index on that table. Below is the script for same.
IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'DBTEST')
DROP DATABASE[DBTEST]
CREATE DATABASE [DBTEST]
CREATE TABLE TESTTABLE(ID INT PRIMARY KEY, NAME CHAR(30))
INSERT INTO TESTTABLE VALUES(11,'AMIT'),(12,'BILLU')
Now using DBCC IND will get the pageID as below:
DBCC IND ('DBTEST','TESTTABLE', -1);
Now using DBCC PAGE command we will see the page information as below:
DBCC TRACEON(3604)
DBCC PAGE('DBTEST',1,154,2)
Look at the result in below screen shot:
Now I'm going to insert some more records and as per our assumption is should sort the data in physical order as it has clustered index on it.
INSERT INTO TESTTABLE VALUES(1,'AMIT'),(6,'BILLU'),(3,'SACHIN'),(2,'SUJAN')
Now using DBCC IND we will see the pageID as below:
DBCC IND ('DBTEST','TESTTABLE', -1);
Now using DBCC PAGE command we will see the page information as below:
DBCC TRACEON(3604)
DBCC PAGE('DBTEST',1,154,2)
From this screen shot we can see that the location of records has not been change. I mean now the row 4 and 5 has the data (11,'AMIT'),(12,'BILLU') and the location is still 96 and 137.
So,The short answer to this myth is there is something called "Row offset Array" in a PAGE which manages the order of rows in a PAGE and data resides in order in which they are dumped into memory.
Lets see the memory dump for same.
From above screen shot it is clear that the records are still in the order in which they are dumped.
Please comment here If I'm missing anything over here as I also don't have much idea about Internal memory structure of SQL Server.
Thanks!!!!!
