This article is half-done without your Comment! *** Please share your thoughts via Comment ***
You can find an N number of articles on Heap Tables. But still, I would like to write a theory on Heap Table.
In simple term, Table without a clustered index is called as Heap Table. Data stored in a heap without specifying an order.
Initially, data stored in the order of its inserted, but later database engine can move data around in the heap so you cannot predict the order of data, and because of this you have to write an ORDER BY clause for each and every use.
When you can use Heap Structure?
Heap table is not advisable to use, but still, sometimes database architectures are using the Heap table.
You can use Heap structure when the table is tiny. If a table has a slight amount of data, then you can use a Heap structure because in that condition Table Scan performs faster than Index Scan.
Heap Structure use RID (Row Identifier) to fetch data from file and data page. RID contains file number, data page number and slot details of the page.
When you should not use heap table?
Simple, when the amount of data is higher and require a predefined ORDER BY to the SELECT bulk of data.
You cannot use a heap table when queries are frequently executing.
You cannot use a heap table when data are grouped together because a group of the data is required proper ordered of data.
