SQL: Heap Table

Hi All,

Last week, I attended a session on Index Architecture and asked about Heap table and Indexed table.

What is Heap Table?

In simple term - It can be define as a table created without any indexes. A table created and doesn't has any indexes on the same.

Then what is the uses of Heap Table:

A Heap Table helps to improve the performance of INSERTING data by using INSERT statements while loading data in to a table in a data-ware house or any data mart or a database.

Why it improve performance:

Beacause, while data is loading in to a heap table, it doesn't need to insert data in a order. 

I mean, Data is not stored in any particular order.Specific data can not be retrieved quickly, unless there are also non-clustered indexes. Since there is no clustered index, additional time is not needed to maintain the index.Since there is no clustered index, there is not the need for additional space to store the clustered index tree.

Then what about Indexed Table or Clustered Table:

Data is stored in order based on the clustered index key.Data can be retrieved quickly based on the clustered index key, if the query uses the indexed columns. Data pages are linked for faster sequential access.