All, Hope you are doing great!! When I getting a fragmentation report after we ran a Index rebuild , I found some table called as Heap but it has Non-Unique,Non-clustered Index.When I am going through he MS docs, found the below definition for Heap, "A heap is a table without a clustered index. One or more nonclustered indexes can be created on tables stored as a heap." Can someone clarify me "One or more nonclustered indexes can be created on tables stored as a heap".Basically I would like to know why it is showing as Heap in my report as when I am checking another table, where the table has only Non-Unique Non-Clustered index but it is showing as Non-Clustered Index in the report.Just confused on this. My understanding is, even though Non-clustered Index created on the table, it is of no use and query engine actually doing a full table scan and so the report shows as Heap for the table. Is my understanding correct,correct me if I am wrong. Any script or example will be highly appreciated. Thank you so much . Thanks.
this is the query am using for the report (took it from a forum): SELECT OBJECT_NAME(ind.OBJECT_ID) AS TableName, ind.name AS IndexName, indexstats.index_type_desc AS IndexType, indexstats.avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats INNER JOIN sys.indexes ind ON ind.object_id = indexstats.object_id AND ind.index_id = indexstats.index_id WHERE indexstats.avg_fragmentation_in_percent > 30--You can specify the percent as you want ORDER BY indexstats.avg_fragmentation_in_percent DESC The query optimizer can take advantage of the non-clustered indexes even on a heap--> and this case should be like the report will show me the index as Non-Clustered? and what if didn't, it can show as a Heap? I mean, the report can produce both at one go ,with Index Name as XYZ and Index Type as Non- Clustered with Fragmentation % as well as Index Name as Null and Index Type as Heap for the same table. This is what I am seeing in the report,John. An example of my report : Table Name Index Name Index Type AVG % ABC ix_ABC NONCLUSTERED INDEX 90 ABC Null Heap 96 Hope you got my
point.So the table is mainly serving as Heap and based on the query (Index columns), non-clustered Index can take the
advantage.is my understanding,right? Thanks and appreciating your great help and response.
I'm not sure what the report is showing you. Are you using a standard report within SSMS? The heap is going to be listed as a separate object from non-clustered indexes. You can have a single heap or a single clustered index as both are the table itself, but not both at the same time. You can have non-clustered indexes for either as well. The query optimizer can take advantage of the non-clustered indexes even on a heap. This would depend on how the query is written and what is needed to satisfy the requirements of the query. Hope that helps!