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.

Suppose I am running multiple column update on one row. Does the database engine physically write new data to the database if some of the columns contain the same values as existed previously in the database? If so, how can it be avoided?

I'm about to implement a job which will run an update on a large table and most of the values will be the same but still, it has to recalculate them all. I am wondering if the update will also rewrite each column even if there is no need to, because the storage media will degrade faster.

share|improve this question

closed as too broad by Shawn Melton, billinkc, Marian, dezso, RolandoMySQLDBA Jul 23 '13 at 18:34

There are either too many possible answers, or good answers would be too long for this format. Please add details to narrow the answer set or to isolate an issue that can be answered in a few paragraphs.If this question can be reworded to fit the rules in the help center, please edit the question.

3 Answers 3

up vote 5 down vote accepted

You should assume the UPDATE is always writing the new value, even when identical. Write the query to eliminate no-ops with an appropriate WHERE clause. Approach it as a large table update, ie. do it in batches and make it idempotent. Is the only safe approach that is not victimized by assumptions and implementation specific details.

Case in point if you test prior to SQL Server 2012 you may conclude that an update that generates an identical in-place row image does not update the page and generates no log, while SQL 2012 under identical conditions you may conclude that it does not update the page but does generate log.

share|improve this answer

I can't speak for all database engines, but most will perform an update if told to even if the result is no real change. MSSQL operates this way in some circumstances, though as pointed out in comments below it has since SQL2005 had some optimisations in the area, and I would be surprised if that isn't the case for most if no all.

To avoid the extra writes you would need to add extra conditional clauses to your UPDATE statements as needed. Ensure that this is what you want though: for instance if you have any auditing triggers operating on that table you might want the save operation to be seen by them even if no change is actually made to some (or all) of the rows.

share|improve this answer
1  
SQL Server 2005 added an optimization to skip nonclustered index maintenance if none of the updated columns stored in the index actually change. –  Paul White Jul 23 '13 at 20:15
    
@PaulWhite: but it would still update the actual table data, it just skips the index updates if I'm not mistaken –  a_horse_with_no_name Jul 24 '13 at 12:16
    
@a_horse_with_no_name I wrote a whole blog post about the details of that. The details could change, of course :) –  Paul White Jul 24 '13 at 14:20

I can't answer on the implementation for a specific database engine, as you haven't specified what exactly you are using. My experience is with Sql Server, so I can't speak for Oracle. However, you can use the where clause to update only when the value is different. The database engine will do the value comparison first, and only update rows which weren't excluded by the where clause.

An example:

UPDATE employees SET first_name = 'new_name'
WHERE COALESCE(first_name, '') <> COALESCE('new_name', '')
share|improve this answer
    
Fixed! Thanks for pointing that out. –  Adam B Jul 23 '13 at 15:23
3  
Following @PaulWhite's pickyness, that won't work as expected, either. Try WHERE first_name <> 'new_name' OR first_name IS NULL. And if that new value ('new_name') can be null, it will have to be even more complicated. –  ypercube Jul 23 '13 at 15:28
2  
I might humbly suggest WHERE NOT EXISTS (SELECT new_value INTERSECT SELECT old_value) though the OR will likely simplify just as well with a literal. –  Paul White Jul 23 '13 at 15:43
1  
Thank you for that post. Very informative. –  Adam B Jul 23 '13 at 17:27
2  
I meant that it may not work in Oracle, where the empty string '' is in certain cases treated as NULL. And COALESCE(NULL, '') <> COALESCE('new_name', '') would not evaluate to true. –  ypercube Jul 23 '13 at 18:16

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