What is a SQL Server Heap?
Before we get into the different types of indexes that are available in SQL Server we should first describe the basic structure of a table. Tables with no clustered index defined (more on that later) are stored in a heap structure which essentially means that the data is stored as an unordered data set in each page.
SQL Server Heap Benefits and Usage
The main use case for implementing a heap structure is when you require fast INSERT performance on your table. Think of a log or audit table where new data is constantly being written. With a heap structure, there is no need for the database engine to figure out where to insert the new data. It simply adds data to the last page or if full, allocates a new page and writes the data in that page.
SQL Server Heap Disadvantages
Querying a heap table can be very slow. Especially if there aren’t any non-clustered indexes defined on the table. Without any indexes, every query that accesses the heap table must perform a full table scan and we all know how expensive that can be if the table is large.
Create a table with no primary key or clustered index, and you have a heap table.