Thursday, March 12, 2015

Number of records in Non-Clustered Index Leaf Level is same as records in the table?

This is one of the questions I got in last week. To make it more clearer, the question is, when we have 1,000,000 records in a clustered-structure table, are we supposed to see 1,000,000 records in leaf level of all non-clustered indexes, even when index key holds duplicates?

Answer is yes. Here is simple code for showing it. This table has 7,301,921 records and leaf level of clustered index holds 7,301,921 records too.

SELECT COUNT(*) FROM dbo.InternetSales;

SELECT index_id, partition_number, index_type_desc, index_level, record_count
FROM sys.dm_db_index_physical_stats (DB_ID('SalesDB')
 ,OBJECT_ID('dbo.InternetSales'), NULL, NULL, 'DETAILED');


Now, let's create an index on OrderDateKey which contains duplicates.


Now if I execute the following code, it will show that number of records in both indexes are same.

CREATE INDEX IX_Sales_OrderDateKey ON dbo.InternetSales (OrderDateKey);
GO

SELECT index_id, partition_number, index_type_desc, index_level, record_count
FROM sys.dm_db_index_physical_stats (DB_ID('SalesDB')
 ,OBJECT_ID('dbo.InternetSales'), NULL, NULL, 'DETAILED');


No comments: