In a previous post, we discussed the difference between a table with a clustered index and a table without a clustered index. A table with a clustered index is known as a clustered table, while a table without a clustered index is referred to as a heap table.
A heap table is a table that does not have a clustered index. Unlike a clustered table, the data in a heap table is not stored in any particular order. This means that the data cannot be retrieved quickly, as there is no specific order to follow.
Instead of being linked to each other, the data pages in a heap table are referenced through the Index Allocation Map (IAM) pages. The first IAM page in the chain of IAM pages is pointed to by the first_iam_page column in the sys.system_internals_allocation_units system view. This IAM page manages the space allocated to the heap.
Since there is no clustered index, fragmentation cannot be addressed by rebuilding the index. Instead, SQL Server uses the IAM pages to navigate through the heap structure. The data pages allocated to the heap are not in any specific order and are not linked. The only logical connection between the data pages is the information stored in the IAM pages.
When performing a table scan on a heap table, the IAM pages are scanned to find the single pages and extents that hold the data pages of the heap. To find the IAM page for a specific table, you can use the following command:
DBCC IND ('databasename', 'Tablename', -1)In the output of the above query, the records with a value of 10 in the Page Type column represent the IAM pages.
A typical heap structure can be visualized as follows:
+-------------------+ | IAM Page | +-------------------+ | Data Page | +-------------------+ | Data Page | +-------------------+ | Data Page | +-------------------+
Heap tables can be useful in certain scenarios, such as when you have a small amount of data or when you frequently insert and delete data. However, they can also lead to performance issues when dealing with large amounts of data or when performing frequent data retrieval operations.
For more SQL Server tips and tricks, follow my page on Facebook at http://www.facebook.com/practicalSqlDba or visit my website www.PracticalSqlDba.com.