4

What do you think is better. Update every time all columns of an table even if there are not all values changes, or update only the changed columns with multiple updates. The idea is, rather than to update every single change immediately, waiting for a few changes and then update all columns, but i don't want to implement a logic who determines which columns has been changed.

UPDATE myTable 
SET col1 = newVal1, 
    col2 = oldVal2,
    col3 = newVal3,
   ... 
WHERE x = y

vs.

UPDATE myTable SET col1 = newVal1 WHERE x = y
UPDATE myTable SET col3 = newVal3 WHERE x = y
...

I am using SQL Server 2014 Express.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
mitch
  • 115
  • 3
  • 8

2 Answers2

8

The first query will perform much better because, it just needs to do a single scan/seek on the table. But in the first query you can update the column which has new values to be updated:

UPDATE myTable 
SET col1 = newVal1, 
    col3 = newVal3,
... 
where x = y

But the second query has to scan/seek the table for each Update which will take more time than the first query.

TT.
  • 15,774
  • 6
  • 47
  • 88
Pரதீப்
  • 91,748
  • 19
  • 131
  • 172
4

Doing one update is better. But, this is really a question of what semantics you want to implement in your code. Here are two important questions:

  • Do you want the new value of a column to be immediately available to other users of the table?
  • Do the two values have to change "at the same time" for the data to remain consistent?

In the first case, you might do separate updates in separate transactions.

In the second case, you would want to do all the updates in a single transaction, ideally in a single statement.

As for performance, a single update is faster. An index on x or y (whichever is the actual column in the table) would speed all versions of the query.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786