Clustered index Vs. non-clustered index

PROBLEM:

When to use clustered and non-clustered indexes?

SOLUTION:

Non-clustered index

• Data is not stored in any particular order
• Specific data can not be retrieved quickly
• Data pages are not linked, so sequential access needs to refer back to the index allocation map (IAM) pages
• Since there is no clustered index, additional time is not needed to maintain the index
• Since there is no clustered index, there is not the need for additional space to store the clustered index tree

Clustered index

• Data is stored in order based on the clustered index key
• Data can be retrieved quickly based on the clustered index key, if the query uses the indexed columns
• Data pages are linked for faster sequential access
• Additional time is needed to maintain clustered index based on INSERTS, UPDATES and DELETES
• Additional space is needed to store clustered index tree

Read more here http://www.mssqltips.com/sqlservertip/1254/clustered-tables-vs-heap-tables


If you find this solution useful, you are welcome to press one of the ads in this page.. Thanks!