SQL Server Index Myth 9 : Every Table Should Have a Heap/Clustered Index

The last myth to consider is twofold. On the one hand, some people will recommend you should build all your tables with heaps. On the other hand, others will recommend that you create clustered indexes on all your tables. The trouble is that this viewpoint will exclude considering the benefits that each of the structures can offer on a table. The viewpoint makes a religious-styled argument for or against ways to store data in your databases without any consideration for the actual data that is being stored and how it is being used.

Some of the arguments against the use of clustered indexes are as follows:

• Fragmentation negatively impacts performance through additional I/O.
• The modification of a single record can impact multiple records in the clustered index when a page split is triggered.
• Excessive key lookups will negatively impact performance through additional I/O.

Of course, there are some arguments against using heaps.

• Excessive forwarded records negatively impact performance through additional I/O.
• Removing forwarded records requires a rebuild of the entire table.
• Nonclustered indexes are required for efficient filtered data access.
• Heaps don’t release pages when data is removed.

The negative impacts associated with either clustered indexes or heaps aren’t the only things to consider when deciding between one or the other. Each has circumstances where they will outperform the other.

For instance, clustered indexes perform best in the following circumstances:

• The key on the table is a unique, ever-increasing key value.
• The table has a key column that has a high degree of uniqueness.
• Ranges of data in a table will be accessed via queries.
• Records in the table will be inserted and deleted at a high rate.

On the other hand, heaps are ideal for some of the following situations:

• Data in the table will be used only for a limited amount of time where index creation time exceeds query time on the data.
• Key values will change frequently, which in turn would change the position of the record in an index.
• You are inserting copious numbers of records into a staging table.
• The primary key is a nonascending value, such as a unique identifier.

Although this section doesn’t include a demonstration of why this myth is false, it is important to remember that both heaps and clustered indexes are available and should be used appropriately. Knowing which type of index to choose is a matter of testing, not a matter of doctrine.

A good resource to consider for those in the “cluster everything camp” is the Fast Track Data Warehouse Architecture white paper (https://msdn.microsoft.com/en-us/library/hh918452.aspx). The white paper addresses some significant performance improvements that can be found with heaps and also the point in which these improvements dissipate. The white paper helps show how changes in I/O system technologies, with flash and cache-based devices, can change patterns and practices in regard to heaps and clustered indexes. This helps to promote the idea of validating myths and best practices from time to time.

For all your application development needs, visit www.verbat.com for a fiscally conscious proposal that meets your needs ( So I can keep this blog going as well!!!!)

Alternatively click through the link   if you found this article interesting. (This will help the companies Search engine rankings)