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.

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


Clustered vs non clustered index

from http://www.programmerinterview.com/index.php/database-sql/clustered-vs-non-clustered-index/

What is the difference between a Clustered and Non Clustered Index?

A clustered index determines the order in which the rows of a table are stored on disk. If a table has a clustered index, then the rows of that table will be stored on disk in the same exact order as the clustered index. An example will help clarify what we mean by that.

An example of a clustered index

Clustered vs non clustered index

Only one clustered index can be created on table

A clustered index sorts and stores the data rows in the table based on the index key values. Therefore only one clustered index can be created on each table because the data rows themselves can only be sorted in one order.

To create a different clustered index on the same table, change the Create as Clustered property setting on the existing clustered index before creating the second index.

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)