I'm checking on how fragmented my clustered indexes and nonclustered indexes on Test2 table, I starting to get confuse why it return me two values in clustered indexes where I only have one clustered index with only one column be specify:
SELECT object_id=object_name(object_id),index_id,index_type_desc,alloc_unit_type_desc,avg_fragmentation_in_percent,avg_fragment_size_in_pages,page_count,
avg_page_space_used_in_percent, record_count,min_record_size_in_bytes, max_record_size_in_bytes, avg_record_size_in_bytes,forwarded_record_count
FROM sys.dm_db_index_physical_stats
(DB_ID(N'FFDS'), OBJECT_ID(N'Test2'), NULL, NULL , 'DETAILED');
results me below
object_id index_id index_type_desc alloc_unit_type_desc avg_fragmentation_in_percent avg_fragment_size_in_pages page_count avg_page_space_used_in_percent record_count min_record_size_in_bytes max_record_size_in_bytes avg_record_size_in_bytes forwarded_record_count
--------- ----------- ----------------------------------------- ---------------------------- -------------------------- -------------------- ------------------------------ -------------------- ------------------------ ------------------------ ------------------------ ----------------------
Test2 1 CLUSTERED INDEX IN_ROW_DATA 0 6.6 33 78.9713367926859 5000 33 42 40.2 NULL
Test2 1 CLUSTERED INDEX IN_ROW_DATA 0 1 1 6.49864096861873 33 14 14 14 NULL
Test2 3 NONCLUSTERED INDEX IN_ROW_DATA 0 12 24 95.7252285643687 5000 28 37 35.2 NULL
Test2 3 NONCLUSTERED INDEX IN_ROW_DATA 0 1 1 12.8119594761552 24 34 43 41.291 NULL
ASKER