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.
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.
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:
Post a Comment