Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

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

Comments

Posted by vittalraghu on 13 April 2011

The article is good and precise.

Thanks.

Posted by Matty Brown on 13 April 2011

Very well explained.

Thanks.

Posted by Nakul Vachhrajani on 13 April 2011

A great to explain this otherwise abstract concept! Very well explained.

Thanks & Regards,

Nakul Vachhrajani,

beyondrelational.com/.../default.aspx

Be courteous. Drive responsibly.

Posted by jignesh.patel on 13 April 2011

Good Article.

Thanks.

Posted by Anil Kumar Kubireddi on 13 April 2011

Really good one, very nice explanation.

Thanks.

Posted by mitesh on 13 April 2011

Really nice article. Easy to understand with step by step example

Thanks.

Posted by Stephen J. Oesterreicher on 13 April 2011

A very nice simple explanation of a heap.

Posted by Charles Kincaid on 13 April 2011

Agreed.  Very good article that is a mini-skirt example (Short enough to be interesting yet covers the important parts.)

Now for the nits:

In one of your SQL examples your FROM is

sys.indexes i

which aliases sys.indexes but never uses the alias. Not a great teaching example, that.

You answer the question of what a heap IS without touching on why it might be a bad thing or a good thing.  That might have been the intent.

I wish that I had the time to write "Heaps are great things." and show the fallacy of “every table MUST have a clustered index but the coffee has finished brewing.

Posted by Martin Catherall on 13 April 2011

Thanks for all the positive comments guys.

Charles, you are absolutely correct, on both counts.

Firstly, I think the unused alias is just an over sight on my part - but like you say, not a good teaching example. So I'll proof read more carefully next time :)

The second point - about why heaps may be used - is possibly something I'll take up in another post. I agree, in the right circumstances, heaps are a good things :)

Thanks for all the feedback.

Posted by pl80 on 16 April 2011

Nakul,

If the concept of a heap is so abstract to you, then I hope you're not a DBA or a Database Developer.  This is "a great article" for someone who has very little to do with databases.

Leave a Comment

Please register or log in to leave a comment.