SQL Server 2008: number of pages in an index

Question

I am attempting to investigate into the number of pages per index and have formulated the following query:

SELECT              
        pyi.index_type_desc as [index type],
        i.name as [index name],
        sum(pyi.page_count) as [number of pages]
FROM 
    sys.dm_db_index_physical_stats(DB_ID('testdb'),OBJECT_ID('surrogatekeys'),NULL,NULL,'DETAILED') as pyi
INNER JOIN 
    sys.indexes as i ON pyi.object_id = i.object_id     
GROUP BY 
    i.name, pyi.index_type_desc

When I run this query, I get the following result:

CLUSTERED INDEX PK_TableWithSurrogateKeyAndUniqueContraints 14342
CLUSTERED INDEX UNIQUE_TableWithSurrogateKeyAndUniqueContraints 14342
NONCLUSTERED INDEX  PK_TableWithSurrogateKeyAndUniqueContraints 707
NONCLUSTERED INDEX  UNIQUE_TableWithSurrogateKeyAndUniqueContraints 707

This table was created as follows:

CREATE TABLE [dbo].[SurrogateKeys]
(
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [TimeStamp] [datetime2](7) NOT NULL,
    [Username] [nvarchar](20) NOT NULL, 
    [Message] [nvarchar](500) NULL,

    CONSTRAINT [PK_TableWithSurrogateKeyAndUniqueContraints] 
       PRIMARY KEY CLUSTERED ([Id]),
    CONSTRAINT [UNIQUE_TableWithSurrogateKeyAndUniqueContraints] 
       UNIQUE ([Username], [TimeStamp]),
) ON [PRIMARY]
GO

and populated with 100,000 rows of random data.

I expected to see two entries: a non-clustered index for the unique constraint and a clustered index for the primary key column.

What do these double entries mean?

Answer 1

It means you got the JOIN conditions wrong - you also need to include the index_id in your JOIN condition - like this:

INNER JOIN 
    sys.indexes as i ON pyi.object_id = i.object_id 
                      AND pyi.index_id = i.index_id