Issues with Heap and Non Unique CI

<< Click to Display Table of Contents >>

Navigation:  View Indexes > Examples Usage > Clustered Index and Heap >

Issues with Heap and Non Unique CI

A Table or Index View table storage can be


Heap - No indexes on the table

Non unique clustered index

Unique clustered index

Clustered columnstore index


bold items above are preferred index types.


The preferred index types are unique clustered index or clustered columnstore index.



Issues with Heaps and Non Unique Clustered Index



Heap Issues

1.Heap fragmentation.

2.No natural order to a heap table.

3.Issue when deleting records with row lock. This results in space not being reclaimed.



Additional reading (see first answer)



Non Unique Clustered Index Issues

1.Additional storage. SQL Server add a hidden integer uniquifier column (4 bytes) to the table to ensure all rows in the index are unique.

2.Slower performance compared to unique clustered indexes.

3.Index size. If the non clustered index is on an integer column the key size is twice the size-integer (4 bytes)+ uniquifier (4bytes).

4.Data cleanup- You can have duplicate rows. This makes it more difficult to clean up data and remove duplicates.



Additional reading