Select Page

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

Bill Mitalski | | May 21, 2015

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

12c Upgrade Bug with SQL Tuning Advisor

This blog post outlines steps to take on Oracle upgrade 11.2 to 12.1 if you’re having performance problems. Oracle offers a patch and work around to BUG 20540751.

Megan Elphingstone | March 22, 2017

Oracle EPM Cloud Vs. On-Premises: What’s the Difference?

EPM applications help measure the business performance. This post will help you choose the best EPM solutions for your organization’s needs and objectives.

Bobby Ellis | April 10, 2018

Scripting Out the Logins, Server Role Assignments, and Server Permissions

Imagine there are over one hundred logins in the source server and you need to migrate them all over to the destination server. Wouldn’t it be awesome if we could automate the process by generating the scripts for the required tasks?

JP Chen | October 1, 2015

Work with Us

Let’s have a conversation about what you need to succeed and how we can help get you there.

CONTACT US

Work for Us

Where do you want to take your career? Explore exciting opportunities to join our team.

EXPLORE JOBS