1. Before Create an Index
----start-----
DBCC TRACEON (3604)
GO
Declare @DBID Int, @TableID Int
Select @DBID = db_id(), @TableID = object_id('DummyTable1')
DBCC ind(@DBID, @TableID, -1)
GO
----end-----
2. Create an Index
----start-----
CREATE UNIQUE NONCLUSTERED INDEX DummyTable1_empid
ON DummyTable1 (empid)
GO
----end-----
3. After Create an Index
----start-----
DBCC TRACEON (3604)
GO
Declare @DBID Int, @TableID Int
Select @DBID = db_id(), @TableID = object_id('DummyTable1')
DBCC ind(@DBID, @TableID, -1)
GO
----end-----
Now, we see two more rows than before, which now contains index page details.
Page 146 displays the page allocation details,
and pages 145 and 147 display the data page details, as before.
In regard to the new pages,
page 149 displays the page allocation details of an index page
and page 148 displays the index page details.
MS SQL generates a page (pagetype = 10) for an index
and explains the page allocation details for an index.
It shows the number of index page have been occupied for an index.
Source:
http://www.sql-server-performance.com/2004/index-data-structures/2/