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.

  • 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 '14 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 '14 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 '14 at 21:25

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.


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

  • 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 '14 at 16:24
  • 2
    @SomeFreeMason You'll have to test it. As my article demonstrates, it depends. – Aaron Bertrand Jan 13 '14 at 16:25
  • I'll post my results from the testing. – Dan Andrews Jan 13 '14 at 16:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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