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. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

A co-worker is trying to sync table B with the contents of table A, both tables have the same structure, we wrote 3 statements, one for update, one for delete and one for inserts.

I have a question when it comes to updating rows:

tables structure:

        [id] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
        [value1] [int] NULL,
        [value2] [int] NULL,

We could have this:

update tb_B 
set tb_B.value1 = tb_A.value1,tb_B.value2 = tb_A.value2 
from tb_B inner join  b_A on =

but each time we would be writing all the values again and again regardless if the values changed, so I thought about something like this:

update tb_B
    tb_B.value1 = tb_A.value1,
    tb_B.value2 = tb_A.value2
from tb_B
inner join tb_A on =
    tb_B.value1 <> tb_A.value1 or
    tb_B.value2 <> tb_A.value2

The last update checks if the value of any column has changed, if true, then it proceeds to update all of the values for that row.

Which do you think it's faster ? writing all the values again and again VS comparing the values and writing ONLY if at least one value is different ?

I think that the farther away the updated column is, the more SQL will take to compare because of the way the OR operator works, this is how I think it works:

think of the numbers as columns: if ( 1 <> 1 OR 2 <> 2 OR 3 <> 3 OR 4 <> 5) then UPDATE

the program will make 4 comparisons in order to realize the row needs to be updated, whereas in this case:

if ( 1 <> 2 OR 2 <> 2 OR 3 <> 3 OR 4 <> 5) then UPDATE

the program will make 1 comparison and proceed with the update. The comparison in my opinion will vary tremendously because the number of comparisons vary for each row !

I hope I am on the right track here,

I appreciate your comments, thanks

share|improve this question

migrated from Aug 30 '12 at 1:29

This question came from our site for professional and enthusiast programmers.

You might be interested in the MERGE statement. – HABO Aug 28 '12 at 19:10
up vote 3 down vote accepted

For a question like this, you really need to time the queries to check performance.

My guess is that the logging overhead on the update is going to be more expensive than any reasonable list of "or" statements. If you are going to use the comparisons, though, you should have a correct statement that takes NULLs into account:

where (tb_B.value1 <> tb_A.value1) or
      (tb_b.value1 is null and tb_a.value1 is not null) or (tb_b.value1 is not null and tb_a.value1 is null) or
      tb_B.value2 <> tb_A.value2 or
      (tb_b.value2 is null and tb_a.value2 is not null) or (tb_b.value2 is not null and tb_a.value2 is null)

You can make the query more efficient by defining an index on each table with (id, value1, value2). This will allow all the logic to use the index. A page would only be necessary in the page cache when a mismatch is found.

share|improve this answer
OK I did some tests...With 523,776 records, the update with the comparisons takes 5 seconds to update 86 thousand rows, and the update with no comparisons at all takes 1 second to update the 523,776 records. by the way this is SQL Server 2005. – Alex Aug 28 '12 at 18:09
This is an interesting result. You need to run the tests multiple times (or somehow be sure that caching is not affecting the timing). How many records are filtered by the condition? If you are updating all or almost all the records anyway, then additional filtering probably won't help. – Gordon Linoff Aug 28 '12 at 18:14
thanks for your input Gordon. this was really helpful. – Alex Aug 28 '12 at 18:17

You can write a trigger on update, insert and delete that would sync the table B with the contents of INSERTED/DELETED tables. You also benefit from the update(column_name) function and columns_updated(), one may not be available if this is SQL Server 2005 or below.

share|improve this answer

Your Answer


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.