asked
Heap table with Non-Clustered Index
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.
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!
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.
So what you are seeing are two different objects. There isn't a name for the heap, thus it's NULL in the report, however sys.indexes still tracks the physical stats for it. In your example you have a heap and in addition a non-clustered index called ix_ABC. Yes, even with a heap, the optimizer can certainly use a non-clustered index to generate a good enough plan to satisfy the query.
Got it,Thank you John
18 People are following this question.
