CODEWITHSUNDEEP
sql-serversql-server-2012
James Rhoat
James Rhoat

Reputation: 170

Analyzing the data structure in SQL Server

I am having a hard time understanding why I am getting multiple entries on the following when analyzing the row structure.

First, I set up two tables.

I am digging into the Row/Page structure using the below query. 

CREATE TABLE [Table1]
(
    [Column1] INT IDENTITY PRIMARY KEY,
    [Column2] VARCHAR (100),
    [Column3] VARCHAR (20)
)

CREATE TABLE [Table2]
(
    [Column1] INT IDENTITY 
        FOREIGN KEY REFERENCES [Table1]([Column1]),
    [Column4] VARCHAR (1000)
)

Then, I insert 1 million rows using the following

BEGIN TRANSACTION

INSERT INTO [Table1] ([Column2], [Column3])
VALUES (REPLICATE ('2', 50), REPLICATE('3', 20))

INSERT INTO [Table2] ([Column4])
VALUES (REPLICATE ('4', 1000))
GO 1000000

COMMIT TRANSACTION

Then, using the following query, I try to see how many pages make up the table.

SELECT
    [alloc_unit_type_desc] AS [Data Structure],
    [page_count] AS [pages],
    [record_count] AS [Rows]
FROM 
    SYS.dm_db_index_physical_stats (DB_id(), OBJECT_ID (N'Table1'), NULL, NULL, N'detailed')

Here is my question: where are these other 41 pages coming from? When they clearly do not contain the same data? I do not see them on the select table either.

Upvotes: 1

Views: 635

Answers (1)

Martin Smith
Martin Smith

Reputation: 453327

If you add index_level to your query you will see

SELECT [alloc_unit_type_desc] AS [Data Structure],
       [page_count]           AS [pages],
       [record_count]         AS [Rows],
       index_level
FROM   sys.dm_db_index_physical_stats (DB_id(), 
                 OBJECT_ID (N'Table1'), NULL, NULL, N'detailed')

enter image description here

The table is created with a primary key. This will be the clustered index by default. The B-tree index to support this has a single root page and 40 intermediate pages between the root and the 11,112 leaf level pages at level 0.

(Structure of a clustered index from here)

enter link description here

Upvotes: 4

Related Questions