Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

When updating in SQL Sever, it is my understanding that the entire row is deleted and then recreated (which is why you get a new timestamp). So then, if you update 1 field or 500 fields, does the performance of the update change?

I suspect that if there are indexed columns, as it has to update the index, there would be more "work" for the server to perform. When answering, assume that there are no triggers.

share|improve this question
1  
"the entire row is deleted and then recreated" is not correct. SQL Server has a mechanism called update-in-place which can be used when the new data is the same size or smaller than the old data and the updated columns are not part of a clustered index. –  Greenstone Walker Jan 13 at 19:45
    
Very interesting, I have to do some more research starting here: sqlservercentral.com/blogs/sqlinthewild/2011/06/21/… –  Dan Andrews Jan 13 at 20:21
1  
Oops, there should have been some links there. Sorry Dan. My authoritative source is the "Inside" series of books by Kalen Delaney and Itzik Ben Gan. Also Brent Ozar's Stop Worrying about Fragmentation. –  Greenstone Walker Jan 13 at 21:25

1 Answer 1

If you are trying to decide whether to build a dynamic or CASE expression, omitting columns whose values haven't changed, I'd say that it depends on your index structure, and you will need to test. In this article, I found that with a single, simple, clustered primary key, the blind update was always faster; this changed in some scenarios when multiple indexes were added.

http://www.sqlperformance.com/2012/10/t-sql-queries/conditional-updates

Where the breaking point is, I'm not sure; nor did I test with dynamic SQL.

share|improve this answer
    
Thanks for the article. My excursion is to find out if update x set name = 'x' performs as well as update x set name = 'x', age = 1, ... ... zip = '44111' assuming that all of the other columns' (age ... ... zip) values do not really change. My assertion is that if the columns change or not, the performance is the same. –  Dan Andrews Jan 13 at 16:24
2  
@SomeFreeMason You'll have to test it. As my article demonstrates, it depends. –  Aaron Bertrand Jan 13 at 16:25
    
I'll post my results from the testing. –  Dan Andrews Jan 13 at 16:27

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.