Hi Friends,

This is a common question I face in many of my classes and consulting assignments? The answer is NO. Indexes are B-Tree structures (balanced tree) where navigation/searching happens from top to bottom (root to leaf level) and building the index happens from bottom to top (leaf level to root level).

Whenever you read about Indexes and B-Tree structures, you will always come across the mention of the root page; making you believe that the root page always exists irrespective of the amount of data. Well, that’s not true. If you have data that can be stored in a single page, do you still need a root page? Or do you think that root page may be needed only if you have data that needs to be stored in more than 1 page?

Let us see.

Create a database and a table.

Create a clustered index on the above table.

Insert enough records that can fit into a single page.

You will observe that the above 8 records can fit into single page, page size being 8192 bytes. Now let us see how many pages are allocated for this B-Tree structure (remember we had created a clustered inde

Run the DBCC IND command to check the page allocations:

Output is as follows:


You can see that only 2 pages are allocated, where Page 154 is the IAM page and 153 is the data page which means that there is no root page as of now.

You can also prove this by a very popular DMV:

Output is as follows:


Observe that I used the ‘DETAILED’ option in the DMV which gives me output for all the levels in the B-TREE structure and right now there is a single level (index_depth) with a single page (page_count is 1) and this is level 0 (index_level).

Now, let us add another record.

Run the DBCC IND command.

Output is as follows:


You can now see that page 155 & 156 has been added. We just needed one more page to hold this additional record; then why 2 more pages?

Run the DMV to get the answer:

Output is as follows:


You can observe that index_depth is 2 which means that there are 2 levels now. Level 0 has 2 pages which are the data pages and Level 1 is the root level with a single page. Thus, now we need the root page which has pointers to both the data pages.