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.

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.

Few 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.