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.
Please share your ideas and opinions about this topic with me, your contribution will add true value to this topic.
If anyone has doubts on this topic then please do let me know by leaving comments or send me an email.
If you like this post, then please share it with others.
Please follow dbrnd.com, I will share my experience towards the success of Database Research and Development Activity.
I put up a post every day, please keep reading and learning.
Discover Yourself, Happy Blogging !
Anvesh M. Patel.