SQL Server - Command To Show PageID

Now, let’s execute the following commands to display the actual page information for the table we created and is now stored in SQL Server.

dbcc ind(dbid, tabid, -1) – This is an undocumented command.

DBCC TRACEON (3604)
GO

Declare @DBID Int, @TableID Int
Select @DBID = db_id(), @TableID = object_id(‘DummyTable1′)

DBCC ind(@DBID, @TableID, -1)
GO

This script will display many columns, but we are only interested in three of them, as shown below.

Here’s what the information displayed means:

* PagePID
PagePID is the physical page numbers used to store the table.
In this case, three pages are currently used to store the data.

* IndexID
IndexID is the type of index,

Where:
    0 – Datapage

    1 – Clustered Index

    2 – Greater and equal to 2 is an Index page (Non-Clustered Index and ordinary index),


* PageType
PageType tells you what kind of data is stored in each database,

Where:

    10 – IAM (Index Allocation MAP)

    1 – Datapage

    2 – Index page


Source:
http://www.sql-server-performance.com/2004/index-data-structures/