|
<< 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.
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
https://dba.stackexchange.com/questions/175267/how-to-clear-space-after-deleteing-rows (see first answer)
https://www.sqlskills.com/blogs/paul/a-sql-server-dba-myth-a-day-2930-fixing-heap-fragmentation/
https://www.mssqltips.com/sqlservertip/1254/clustered-tables-vs-heap-tables/
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
https://www.sqlpassion.at/archive/2010/08/19/uniquenon-unique-clustered-indexes/
Uniquifier
https://blogs.msdn.microsoft.com/luti/2018/02/16/uniqueifier-details-in-sql-server/