SQL Server Heap

By: Ben Snaidero

Overview

In the previous section we discussed clustered indexes.  In this section we discuss what a table is called that does not have a clustered index.

What is a HEAP

A heap table is a special type of table that does not have a clustered index defined on it.  With a heap structure the table data is not stored in any particular order.  Heap tables can be used when the data coming into the table is random and has no natural order but non-clustered indexes should always be created on heap tables.  If there are no non-clustered indexes defined when the table is queried all the data would have to be scanned and sorted in almost all cases leading to very poor performance.  The following example shows how without any indexes on a heap structure the entire table is scanned and the final dataset also needs to be sorted before being returned.  Note: For this example I copied all the data from SalesOrderDetail into new table called SalesOrderDetail_HEAP.

SELECT * FROM Sales.SalesOrderDetail 
WHERE SalesOrderID BETWEEN 43755 AND 43759 
ORDER BY SalesOrderID, SalesOrderDetailID;