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 );
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
You should see the following output – which you’ll observe states that Customers is a heap.
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
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!)