updating changes rows

I have a requirement to update a couple of thousand rows in a table based on whether any changes have happened to any o开发者_如何学JAVAf the values. At the moment im just updating all the values regardless but was wondering what was more effecient. Should i check all the columns to see if there are any changes and update or should i just update regardless. e.g

 update someTable Set
   column1 = somevalue,
   column2 = somevalue,
   column3 = somevalue,
   etc....
 from someTable inner join sometable2 on
   someTable.id = sometable2.id
 where 
   someTable.column1 != sometable2.column1 or
   someTable.column2 != sometable2.column2 or
   someTable.column2 != sometable2.column2 or
   etc etc......

Whats faster and whats best practice

See two articles on Paul White's Blog.

1. The Impact of Non-Updating Updates for discussion of the main issue.
2. Undocumented Query Plans: Equality Comparisons for a less tedious way of doing the inequality comparisons particularly if your columns are nullable (WHERE NOT EXISTS (SELECT someTable.* INTERSECT SELECT someTable2.*)).

I believe this is the best way.

Tables and data:

declare @someTable1 table(id int, column1 int, column2 varchar(2))
declare @someTable2 table(id int, column1 int, column2 varchar(2))

insert @someTable1 
          select 1,10 a, 'a3'
union all select 2,20  , 'a3' 
union all select 3,null, 'a4'

insert @someTable2
          select 1,10,   'a3' 
union all select 2,19,   'a3' 
union all select 3,null, 'a5'

Update:

UPDATE t1
set t1.column1 = t2.column1,
t1.column2 = t2.column2
from @someTable1 t1
JOIN 
(select * from @someTable2
EXCEPT
select * from @someTable1) t2
on t2.id = t1.id

Result:

select * from @someTable1

id          a        b
----------- -------- --
1           10       a3
2           19       a3
3           NULL     a5

I've found that explicitly including the where clause the excludes no-op updates to perform faster, when working against large tables, but this is a very YMMV type of question.

If possible, compare the two approaches side by side, against a realistic set of data. E.g. if your tables contain millions of rows, and the updates affect only 10, make sure your sample data affects just a few rows. Or likewise, if it's likely that most rows will change, make your sample data reflect that.