Updating a column with its current value

Question

I have a saved proc that should conditionally update a bunch of fields in one table. Conditionally, because for each field I also pass the dirty flag, and the field should be updated only if the flag is set to 1.

So, I am going to do the following:

create proc update @field1 nvarchar(1000), @field1Dirty bit, ...other fields... as begin update mytable set field1 = case when @field1dirty = 1 then @field1 else field1 end, ... same for other fields end go 

The question is SQL Server (2008), smart enough not to physically update the field if it was assigned its own value, for example, if @ field1dirty = 0?

Answer 1

 Question - is SQL Server (2008) smart enough to not physically update 

field, if it was assigned a value, as in the case if @ field1dirty = 0?

No, you should add a where clause that says ... where field <> the value you are updating to .

It doesn't seem like a big deal at first, but it can actually create a huge amount of overhead. One example, think of triggers. If this is an update of each field in the table, this trigger will fire for each row. YIKES, that a lot of code execution, which is unnecessary, especially if this code says, moves update lines to the log table. I'm sure you understand this idea.

Remember that you are updating the field, it is just the same value as it was before. It is really good that this happens because it means that you can still consider the field changed (think about a timestamp, etc.). If he did not think that updating the field to the same value changed the line, you would not know if someone was unintentionally (or intentionally) trying to change the data.

Update due to comments: link to coalesce function

Example: To handle null parameter values ​​in a stored procedure

 Update Table SET My_Field = COALESCE(@Variable, My_Field) 

This does not mean what I said before when the field is updated to the same value, but it allows you to check the parameter and conditionally update the field.

Answer 2

SQL does not check the value before writing to it. It will overwrite anyway.

Answer 3

SQL Server will perform the upgrade. The row will be updated as a whole row, so if one column in the row has FieldxDirty = 1, updating is still necessary. There is no optimization in the SET clause.

@Kevin's answer will help more than optimize a SET clause.

Answer 4

Sorry to have come here with an opinion, but I have nowhere else to write :-) At least there should be some kind of “hint” to tell UPDATE instructions, as a rule, DO NOT update to the same value.

There are at least 2 reasons I can think of: 1st: the update value can be a complex expression, and it is a waste of runtime (not to mention maintaining expression changes) to express it again in the WHERE clause. Think about NULL values ​​too! Ex. UPDATE X SET A = B WHERE ISNULL (A, '') <> ISNULL (B, '')

2nd: we have a synchronous mirroring scenario where the "backup" server is physically located in another part of the city. This means that writing to disk is performed first when the backup server has written. There is a huge time difference between recording and skipping. When the developers created the application, they worked in a test environment without mirroring. Most UPDATE statements simply did not change the value, but it did not matter in the test environment. After decomposing the application into production with mirroring, we would very much like to receive the prompt “only changed value”. Reading the initial value and checking it does not take time compared to writing