What is a heap table

A HEAP table in a sql server is a table without a clustered index. It can have non clustered index on it. A Table with a clustered index is called clustered table. Since it does not have clustered index the data in this table won’t be in sorted order. When you want to insert data in the heap table it will be fast as the SQL server does not have to ensure that the data is in sorted order. Retrieving the data from SQL server will be slow as the query plan will have a full table scan.
Heap table is preferred when the table is a small table and the entire table scan is acceptable However do not use heap table when you want to return the data in sorted order frequently or when the table is large which will cause table scan to read all rows or when you are grouping the data which will be sorted before.Also do not use heap when you are filtering the data by any range.
Example of heap table will be:
1. Small Transaction log table.
2. Dimension table which is small table and never changes ( For example : Country region Mapping table).
3. Staging tables where you truncate and insert new data for processing.

 1,837 total views,  1 views today