What is a heap
Imagine a library where every book is just placed in any available space. To find a particular book, you need to scan through all the bookshelves. From the database perspective, there is a structure with the same properties called a heap. A heap is the simplest table structure available in SQL Server.
A heap is a table without a clustered index. The data rows are not stored in any specific order, and there is no specific order to quickly find a particular data page. Data rows are added to the first available location within the table's pages that have sufficient space. If no space is available, additional pages are added to the table and the rows placed in those pages.
Consider using a heap for tables that:
- Contain volatile data where rows are added, deleted, and updated frequently: The overhead of index maintenance can be costlier than the benefits
- Contain small amounts of data: Using a table scan to find data can be quicker than maintaining and using an index
- Contain data that...
