In last post post #54 we understood what is a Heap table. Now the challenge is what if this table is highly fragmented so.
Now our task is to run maintenance of this highly fragmented table. For performance it is require to maintain this type of heap table also.
So you have to run following command
“ALTER TABLE TABLENAME REBUILD;
For example I run the Rebuild for Adventureworks2012 database’s heap table which we found in post #54.
See below snap for detail
So, with this REBUILD command the table is rebuild and the fragmentation issue resolved.
I hope it may help you some where.
Thanks & Enjoy!!!
A table without cluster index is called Heap table. Now you are thinking why we are talking about this.
So as SQL developer we should aware and In next post I will explain which is related to maintenance how to Rebuild a heap table.
Now our next question is how to determine all the heap table from a database
so below is simplest query to determine all the heap tables from a database
SELECT T.Name ‘Heaptable’
FROM sys.indexes I
INNER JOIN sys.tables T
ON I.object_id = T.object_id
WHERE I.type = 0 AND T.type = ‘U’
I ran this query on Adventureworks database and 2 tables with heap as shown in below figure
now in next post we will discuss how to rebuild fragmented heap table which is a maintenance activity.
I hope this article may help you.