Hi Friends,

Today we are going to discuss on Heap versus Clustered Table. Many times we as a DBA have given the suggestions that avoid the use of Heap [Table without clustered index] in the database. There are lots of reasons for recommending the Clustered Table [Table with clustered index] rather than Heap. One of them is: As comparison to Clustered Table number of reads will be high for Heap.  The reason for high number of reads is due to forwarded records.

Forwarded record is the record in a heap that has forward pointers to another data location. (This state occurs during an update, when there is not enough room to store the new row in the original location.) Let me show you this practically.

First I would like to show you the number of reads during the use of Clustered Table:

In the below output you can see that there is no Forwarded record count in clustered index. As well as in the message tab we have number of reads for clustered table.

Clustered_Table

Clustered_Table2

Now we can see the same operation on Heap Structure:

In the below output you can see that there are Forwarded record count in the Heap. As well as in the message tab we have number of reads for this Heap structure.

HeapStructure1

HeapStructure2

Now if you will compare the IO Stats after the update statement then you will find out the  logical reads are very much higher for Heap with forwarded Records as comparison to Clustered Table.

HAPPY LEARNING!

Regards:

Prince Kumar Rastogi

Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook

Follow Prince Rastogi on Twitter | Follow Prince Rastogi on FaceBook