How to rebuild a heap Index? Do we need to worry about heap index when rebuild or reorg indexes.
Thank You in advance.
A heap table by definition does not have a clustered index. It may or may not have nonclustered index(es).
Given the way SQL manages tables, it's best to create a clustered index on a table, even if it's not needed based on how the table is used. In particular, if a heap has one nonclustered index, just drop that index and recreate it as a clustered index instead.
IIRC, such a thing is a new feature in SQL Server 2014. Do you have 2014?
As Suggested in the below given link. Create a clustered index and then drop the index to rebuild heap. Please refer
Oh, be real careful now... I know that's a Microsoft document but creating a clustered index and then dropping it to return the table to a HEAP is a really, really bad idea especially if the table has any bulk to it. First, if in the FULL recovery model, it will cause a major log file explosion because EVERY non-clustered index will be rebuilt twice along with the build of the clustered index and then the build of the HEAP. It'll be similar to deleting the rows in the table and then adding all the rows back. If not in the FULL recovery model, then it will "only" be half that bad which is still very bad for heavy tables.
Second, it just perpetuates the problem. If you needed to rebuild the HEAP to recover space in the table due to forwarded rows or deleted rows, then it will happen again and you'll need to do it all again someday in the future,
Third, it'll cause an MDF file blowout. Any HEAP or INDEX of just 128 extents in size (that's just 8MB, folks) will cause the new index to be built first and then the old index to be dropped. That means that the space in the MDF file caused by the table will double. Yes, it returns to free space once the old index is dropped but it does remain in the MDF and that could suddenly push a "tight" system over the edge for space.
If you have a table that's large enough to justify defragging, then do it the right way. Determine what the correct candidate key column(s) should be (taking in the potential for page splits and the fact that the clustered index will be added to the behind the scenes definition of every NCI), build the clustered index, and LEAVE IT as a part of the table. If the are no reasonable candidate keys for the CI (narrow, unique, immutable, ever-increasing, etc), then consider adding a new column to the table.