What are the performance differences between noclustered index and clustered index on a table that is heavily inserted? Which one would have less contention?
Thanks in advance
Printable View
What are the performance differences between noclustered index and clustered index on a table that is heavily inserted? Which one would have less contention?
Thanks in advance
If you have many indexes on a table that is very big, say 2-3GB, rebuilding these indexes can consume a lot of power from the server for hours. In that case you might want to schedule a script that for say 10 minutes, rebuilds as many indexes as it can, then it stops. Later on it rebuilds as many it can again for 10 minutes, etc. Or just don't use that many indexes.
But anyway, that wasn't the original question.
I agree with the former posts that for sequential data clustered indexes are good.
/Magnus
------------
chris at 8/1/00 5:44:12 PM
In a high transaction environment (assuming 3NF), the most efficient way to index is to create the Clustered Index on the Primary Key of the table. Create Non-Clustered indexes for all other needed searchable/selectable columns. Especially if you use ANSI-92 T-SQL syntax to control DRI. Most of your joins and filter criteria will be on the Primary/Foriegn Key relationship.
It is best to perform maintenance at night (or off hours) that will resort the clustered indexes and update the table statistics.
------------
at 6/8/00 11:12:51 AM
Bala,
Depends on the nature of the data you're inserting and the column upon which you're creating the Clus.Idx. If you've an index on column with data that increments serially, ie : products coming off a production line wih serial numbers, then it would be appropriate to use a clustered index in this situation, as the data would always be inserted in the correct sequence and would not have to be re-organised on disk. But for non-serial data sequences, non-clustered would probably be a better choice.
------------
Bala at 6/1/00 4:38:51 PM
If you happen to update a column very often, I think non clustered index would be the right choise.
because, whenever you do the update on clustered index column, it has physically rearrange all the rows in the datafile.
For Master tables or lookups , clustered would be fine. becuase it doesn't change very often.
------------
john at 4/24/00 2:10:17 PM
What are the performance differences between noclustered index and clustered index on a table that is heavily inserted? Which one would have less contention?
Thanks in advance