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!)