question

amfineandyou123 avatar image
amfineandyou123 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.
sql-server-2008sql-server-2008-r2sql-server-2012sql query
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

JohnM avatar image
JohnM answered
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!
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

amfineandyou123 avatar image
amfineandyou123 answered
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.
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

JohnM avatar image JohnM commented ·
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.
0 Likes 0 ·
amfineandyou123 avatar image
amfineandyou123 answered
Got it,Thank you John
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.