Understanding SQL Server indexes- Heap table vs Clustered Index

 We often hear about Heap, Clustered Indexes in our day to day job. I will try to explain them here in a simpler way.

Heap Table: 

What we know about heap table is “A heap is a table without a clustered index”. Or you can say “without any clustered index our data gets stored in a table in non-specific order”.

In my view, a table without any clustered index looks like as below (I have heard money makes life easier so let’s see if following picture makes indexes easier to understand or not). In this situation SQL Server Engine is our cashier and it will be quite easy for our cashier to deposit new coins/money in the room, however what will happen if we ask him to withdraw £2000 and that too in £2 coins each or give us all £2 coins from room. He would need to scan through all coins and then pick the required ones only.

As we know it is going to be very time taking and resource intensive. Obviously we would not want our SQL Server to give up and start processing slow.

heap

Two points to take away from above example:

  • Data Inserts run faster in case of heap as data being inserted can be placed anywhere within the table.
  • Read operations get slow.

Clustered Index

Clustered indexes sort and store the data rows in the table or view based on their key values.

Following points may describe it better:

  • The data rows are sorted in order based on the clustered Index key
  • Indexes are commonly based on B-tree structures
  • Top node is called the root node
  • Bottom level nodes are called leaf nodes
  • Data pages in leaf level, are linked in doubly linked list

If we take our previous example, it is like organising your randomly stored money in cash drawers in specific order. Cashier may need to do extra work when he is depositing the money in following drawers however he can quickly withdraw out of it.

heapFew points to take away from here:

  • Inserts may run slower.
  • Reads operations get super fast.
  • Bulk inserts operations (in case of big tables) get slow.

Thanks for spending your time to read it. Please provide your feedback. I will be covering clustered index and non-clustered in next article.

12 thoughts on “Understanding SQL Server indexes- Heap table vs Clustered Index

Add yours

  1. I was always got confused between clustered index and heap table but now all my concepts are fully cleared.
    Thanks for examining in such a way as the example you have taken cleats all my doubts.

    Like

  2. This is really nice and easy to understand. Specifically with the example, the concept becomes crystal clear. Thanks , waiting for the next one…

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Website Powered by WordPress.com.

Up ↑

%d bloggers like this: