So, what’s this heap thing?

G’day,

I’ve been asked a few times lately to explain what a heap is.

I’m quite happy to do this, but I generally inquire of the person who posed the question, what exactly they think a heap is.

It’s at this point that I seem to mostly get one of either two answers.

  • The first is “I don’t know, that’s why I asked” – which is cool.
  • But the second seems to be “a table without any form of index” – While this is true, it is also possible for a heap to have indexes – but only of the non clustered variety – and it’s this that i’d like to prove in this post.

Firstly, let me state exactly what a heap is and then we’ll look at some evidence to back that up.

A heap is a table that does not have a clustered index. The table can have as many non clustered indexes as you like (obviously within the limit of sql server – but if you are touching on that limit then I’d suggest you had much bigger issues than simply wanting the definition of a heap :) )

So, let’s have a look at this. The code in listing 1 below, simply creates a database called “HeapTestDB” (dropping the database if it already exists)  containing a single table, “Customers”

USE tempdb;
GO

IF EXISTS(SELECT 1 FROM sys.databases WHERE [name] = 'HeapTestDB')
BEGIN
ALTER DATABASE HeapTestDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE HeapTestDB;
END
GO

CREATE DATABASE HeapTestDB;
GO

USE HeapTestDB;
GO

CREATE TABLE Customers
(
PK_ID INT IDENTITY(1,1) NOT NULL,
FirstName NVARCHAR(20) NOT NULL,
LastName NVARCHAR(20) NOT NULL
);

Listing 1

Firstly notice that the table has absolutely no indexes or keys of any kind.

So now, lets have a look at sys.indexes and see what information that gives us

SELECT
OBJECT_NAME([OBJECT_ID]) AS [table_name] ,
type_desc
FROM
sys.indexes i
WHERE
OBJECT_NAME([OBJECT_ID]) = 'Customers';
GO

Listing 2

You should see the following output – which you’ll observe states that Customers is a heap.


Listing 3

Now lets add a non clustered index to the Customers table


CREATE UNIQUE NONCLUSTERED INDEX unique_name ON customers(FirstName , LastName ASC);
GO

Now run the code in Listing 2 again and you should see the following output.Again, you’ll observer that Customers is still a heap even though the table now has a non clustered index.

Now, run the code in listing 4 below.


ALTER TABLE Customers
ADD CONSTRAINT PK_Customers PRIMARY KEY CLUSTERED (PK_ID ASC);
GO

Listing 4

Now, run the code in Listing 2 again. This time you’ll observer the results shown below. This time the Customers table is shown as a having a clustered index.

So from this little test we’ve observered a few things.

  • A table with no indexes is a heap.
  • A table with non clustered indexes ONLY is a heap.
  • As soon as a clustered index is added the table it is no longer a heap.
  • Your table will either be a heap or a clustered index – (it’ll never be both!)
Tags: ,

About Martin Catherall

Martin Catherall is Senior SQL consultant at SQL Down Under, based in Melbourne, Australia. Martin is also a Microsoft Data Platform MVP and Regional Mentor (RM) for the Professional Association for SQL Server. Prior to relocating to Australia he was extremely active in the Christchurch, New Zealand data community - founding the local SQL Server user group and organizing SQL Saturday Christchurch - which later became SQL Saturday South Island. He likes learning interesting stuff about processing and storing data and passing on his knowledge. In his spare time he likes to learn guitar and hang out with his two young sons Callum Glen and Robert Michael.