DBA 101: What’s the Difference Between a Table and a Heap?

If you are a database administrator working in a SQL environment, and are also intent on becoming a proficient or power SQL user there is one very basic concept you’ll need to understand: what is the difference between a table and a heap?

Knowing the difference between these two objects will help you understand how SQL works. When you understand what tables and heaps are, you will understand the very foundations of how data is stored. This will allow you to make informed choices when working with a database, such as how to select columns as you create an index for a database.

The Basics of Tables and Heaps

A table is a data structure in which the data is stored in columns and rows. The table is typically named, as are the columns, with succinct names describing the content.

Tables can be stored one of two ways: with a clustered index or without. The index provides a definition of how the records are stored, organizing them. When a table is stored without a clustered index, that’s a heap.

Tom Carpenter, writing in Microsoft SQL Server 2012 Administration: Real-World Skills for MCSA Certification and Beyond, explains:

Think of it as a pile of unsorted clothes in the laundry room…The term heap, in this context, means a disorganized pile, and that’s what a table is without a clustered index. Heaps are stored without any assigned structure for the rows. Tables stored as clustered indexes are organized according to the clustered index column.

Database Structure and Performance

Unlike a heap, a clustered index uses a balanced-tree structure to organize and regulate data. Knowing how these indexes operate, including how the data in the database is accessed, can help you make adjustments in the database that are ultimately able to improve its performance.

This is particularly true with a task such as a query. Without a clustered index, a single query looks at every row [like “Table Scan”]. When searching data with a clustered index, a query can look at a specific column for a value and retrieve the results much faster.

Changing a database structure—whether from a heap to a table or vice versa—is not a trivial task. As Microsoft notes:

There are sometimes good reasons to leave a table as a heap instead of creating a clustered index, but using heaps effectively is an advanced skill. Most tables should have a carefully chosen clustered index unless a good reason exists for leaving the table as a heap.

Next Steps in Database Organization

You should now understand the fundamentals of what a heap and a table are and how they may affect your database performance.

Datavail can work with you and your organization on any number of SQL-related projects. To learn more about our remote database services and how our experts can help improve the performance of your SQL database, please contact Datavail to discuss a custom solution designed for your enterprise. We also have a frequently updated blog with helpful resources for DBA questions.

Image: 123RF/semisatch