It seems that many people don’t fully understand what clustered index is and why use it.
For performance purpose, clustered index and non- clustered indexes can be used. A clustered index actually creates a linked data structure for a table (or physically sorting the table); so only one clustered index can be used in a table (You cannot physically sort data in two or more orders). If the clustered index is created for a table, looping up data will rely on it. Otherwise, it relies on IAM (Index Allocation Map) pages.
The non-clustered indexes can be created for tables no matter it’s clustered table or heap.
Although it is said that INSERT operation is quicker in heap than in clustered table, the clustered table has many benefits over the heap. In most cases, people should choice clustered table.
In addition, though performance can be improved by creating indexes, maintenance (defragment) is also important. After inserting/deleting/updating frequently, a table becomes fragmented. It causes poor performance. Defragment then can solve the problem.
Greg Robidoux's Clustered Tables vs Heap Tables explains these ideas very clearly.
For performance purpose, clustered index and non- clustered indexes can be used. A clustered index actually creates a linked data structure for a table (or physically sorting the table); so only one clustered index can be used in a table (You cannot physically sort data in two or more orders). If the clustered index is created for a table, looping up data will rely on it. Otherwise, it relies on IAM (Index Allocation Map) pages.
The non-clustered indexes can be created for tables no matter it’s clustered table or heap.
Although it is said that INSERT operation is quicker in heap than in clustered table, the clustered table has many benefits over the heap. In most cases, people should choice clustered table.
In addition, though performance can be improved by creating indexes, maintenance (defragment) is also important. After inserting/deleting/updating frequently, a table becomes fragmented. It causes poor performance. Defragment then can solve the problem.
Greg Robidoux's Clustered Tables vs Heap Tables explains these ideas very clearly.