July 24, 2002 at 1:44 am
An order is an UPDATE of all columns from a table. If column's values doesn't change, does SQLSERVER (2000) locks the indexs linked with this column ?
example : UPDATE table1 SET column1='xxx', column2 = 'yyyy', column3 = 'zzz' WHERE column_ident = 1;
By supposing that column1, column2, column3 are indexed, and that only the value of 'column2' changes, the question is :
does SQLSERVER update the index linked with column1 and column3 ?
And by supposing that the table had 50 columns and 15 index, what's the consequence (in term of performance) by updating always the 50 columns ?
Thanks for your help
July 24, 2002 at 7:18 am
First with if a column that is part of a clustered index is updated then all indexes are updated so if you have 15 indexes it will take a moment but it will not move any items in the other indexes but will move items in the table order. Nonclustered index entries contain the clustering key which they use to find the datas location.
Now if the table does not have a clustered index and then a row pointer is used instead. But in this situation if you update a column then it should register only the indexes related to that column need to be updated. Similar to how you can check if a particular column was updated in a trigger. So columns 1 and 3' indexes should not be affected. Same is true if there is a clustered index but column 2 is not part of it.
Edited by - antares686 on 07/24/2002 07:18:32 AM
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply