Updating column with it's current value

Question

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

So I'm 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

Question - is SQL Server (2008) smart enough to not physically update a field if it's been assigned its own value, like in case if @field1dirty = 0?

Answer 1

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

a field if it's been assigned its own value, like in case if @field1dirty = 0?

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

This doesn't seem like a big deal at first, but in truth it can create a massive amount of overhead. One example, think about triggers. If that updates every field in the table, that trigger will fire for every row. YIKES, that's a lot of code execution that's needless, especially if that code is say, moving updates rows to a logging table. I'm sure you get the idea.

Remember, you're updating the field, it just happens to be the same value it was before. It's actually good that this happens, because that means that you can still count the field as modified (think timestamp etc.). If it didn't think updating the field to the same value was modifying the row, you wouldn't know if someone inadvertently (or deliberately) tried to change data.

Update due to comments: Link to the coalesce function

Example: For handling null parameter values in your stored procedure

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

This doesn't get around what I was talking about before with the field being updated to the same value, but it does allow you to check parameter and conditionally update the field.