Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
debradeng
Starting Member
46 Posts |
 Posted - 2006-11-10 : 22:40:10
|
| In brief,Creating indexes appropriatedly,will speed up the system performance.while some people said creating indexes only help for selecting operations and will slow down inserting,updating and deleting operations?I don't understand why it would not help for updating and deleting.I think index will help for the two operations too.Am I right? |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-11-11 : 01:23:55
|
| Lets say you have separate indexes on every column in your table.If you insert a new record then the index for each column also needs to be updated. The same if you change one or more columns in a record. And then when you delete the record all the index entries need to be found and removed.If you had retrieved that record 10,000 times before it was deleted, and only updated it once or twice, then the indexes would have been more useful than the "cost" of maintaining them.So there is a balance.Having an index on a column that is, in practice, never used is adding to the "cost" of keeping it updated as records are modified - so no point having that index. But obviously an index representing the Primary Key is going to have lots of use!So create indexes that speed up queries (in general this will also include indexes that cover Foreign Key JOINs) and which will be used often enough to justify the index, but don't create indexes that won't be used.Kristen |
 |
|
|
pootle_flump
1064 Posts |
Posted - 2006-11-11 : 03:12:31
|
quote:
Originally posted by debradeng
while some people said creating indexes only help for selecting operations and will slow down inserting,updating and deleting operations?
It is not strictly true that an index necessarily harms update performance. Remember that the cost is to do with updating the indexes. So - an index on a primary key column(s) may speed up updating since the optimiser could use the index to find the column that requires updating but (assuming the primary key does not change) does not need to alter that index. |
|
|
|
|
|
|
The advantages and disadvantages of Creating index