Blog Post

SQL Server Myth on Clustered Index page allocation or B-Tree formation

Here is another SQL Server myth which I realised while understanding anatomy of index pages.

For a long time I thought data and Index pages will be allocated once We create a table.

Its a myth. but the reality is

1. Once you create a table and if a table is empty, no data and index pages will be allocated.
2. A table gets its first data page only when we insert our first record in a table
3. Clustered index page will be allocated only when a table gets second data page (there is no need to have index page for only one data page right.)
4. NonClustered Index page is allocated even if single/first data page is available.

Note: And by the way SQL Server uses B+Trees and not B-Trees. its a another good myth.
(Reference MS Press : SQL Server 2008 Internals, Chapter 6, Page Number 300)

here is the script to demonstrate my understanding:

1. Lets create a simple table having clustered index and one non clustered index

create table Employee(
intEmpId int identity(10001,1
),
strFirstName varchar(100
),
strLastName varchar(100
),
strDeptId varchar(6
),
strSSNId int
)

go

Create Clustered index CI_employee_intEmpId on employee(intEmpId)
GO
Create NonClustered index NCI_employee_strSSNId on employee(strSSNId
)
GO

2. Lets check data and index page allocation.

DBCC TRACEON(3604
)
DBCC IND('CRS_ISG', 'Employee', -1
)
DBCC TRACEOFF(3604
)

Finding: No Data and Index page is allocated for a empty table

3. Lets Insert 10 records in sample table with some dummy values

set nocount on
insert into employee(strFirstName, strLastName, strDeptId, strSSNId) values ('AAA','AAA','DEPT01',round(rand()*1000000,0
))
go 10

4. Lets check data and index page allocation again

DBCC TRACEON(3604)
DBCC IND('CRS_ISG', 'Employee', -1
)
DBCC TRACEOFF(3604
)

Finding: 
a. Two IAM pages are allocated (one for Clustered Index and another for Non Clustered Index)
b. One data page is allocated
c. Index page for Non Clustered Index is allocated
d. Index page for Clustered Index is not allocated so far. Previous and Next page pointers shows 0 in data page (as we have only one data page)

5. Now lets try to make this table consume one more data page by inserting 200 more records

set nocount on
insert into employee(strFirstName, strLastName, strDeptId, strSSNId) values ('AAA','AAA','DEPT01',round(rand()*1000000,0
))
go 
200

Lets see page allocation now:

DBCC TRACEON(3604)
DBCC IND('CRS_ISG', 'Employee', -1
)
DBCC TRACEOFF(3604
)

set nocount
on
insert into employee(strFirstName, strLastName, strDeptId, strSSNId) values ('AAA','AAA','DEPT01',round(rand()*1000000,0
))
go
200

Finding: 
a. Second data page is allocated.
 
b. Clustered index page is allocated once a clustered index table gets its second data page.
c. Previous and Next page pointers are updated

Conclusion:
First Clustered Index page is allocated only when a table gets its second data page and there is no point of assuming b-tree for a single data page.

Reference: Paul S. Randal blogs

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating