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 |
|
sqldba20
Posting Yak Master
183 Posts |
 Posted - 2007-06-07 : 22:00:47
|
| Folks:I am doing lot of 'UPDATE' to a table and there are no indexes on this table. Will it cause any performance issues? If so, what kind of indexes needs to be there on a table which has UPDATE. Should I create indexes based on the WHERE clause? Sorry.. but I a newbie....Thanks ! |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-06-08 : 01:10:16
|
| Yes, better to create index based on where clause. |
 |
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2007-06-08 : 01:13:30
|
| It depends...The actual changes to the database may be slower when you have indexes because there is more to change BUT 1. Not all indexes will need to be changed by an UPDATE, all will need to be changed by an INSERT or DELETE however.2. If you are updating or deleting a small number of rows and those rows can be located using an index then the update or delete will be much faster, even though the actual database changes are slower.3. Typically you will not see a performance hit unless you are changing a large number of rows, in which case you can consider dropping the index(es) before the large operation and adding them back afterwards.4. Don't add unnecesary indexes - ie. don't just add an index because you think it might get used.5. If you add non-clustered indexes, keep your clustered index key small, usually a numeric column like an int is best.If you aren't sure, test! |
|
|
|
|
|
|
INDEXES on UPDATE STATEMENTS