I ran the following query on my database to identify the fragmented indexes. However I surprised when saw the Indexes belong to some empty tables among the highly fragmented indexes! How can it be possible? I assume if there is no data in a table, the index should be blank and can not be fragmented.
SELECT a.index_id, name, avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats(NULL, NULL, NULL, NULL, N'Limited') AS a JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id
Could someone give me an explanation?
Thank you in advance.