I have an Update statement which update several columns. Some of those columns are nvarchar(max) type and not always need to be updated.
I would like to avoid updating unnecessary columns, so, the simplest solution I come up is the following:
UPDATE table1 SET column1 = @col1, Column2= @col2, ...
TextColumn = CASE SomeVar WHEN 0 THEN @NewVal ELSE TextColumn
WHERE idTable = @idTable
Does any one know if SQL is smart enough to avoid updating the TextColumn if it is going to be updated with itself?
The second option is just to split the Update as follows:
UPDATE table1 SET column1 = @col1, Column2= @col2 ...
WHERE idTable = @idTable
IF Condition
UPDATE TextColumn = @TextVal WHERE idTable = @idTable
I do not know if there is a better alternative, maybe it is possible to dynamically choose the columns in the update sentence.
I will appreciate any suggestion about this, my main target is improve the performance of the Update
This question is similar to this one. However, that question does not address the posibility of spliting the Update statement in two sentences in order to avoid unnecessary updates. Moreover, in this case the column to update is nvarchar which involves a large amount of text which brings other performance considerations.
