Ok, I'm at a crossroads in my program.

I've got a program that needs to throw an SQL update command to update
some individual records.

differently if the field is the same as the old field?

IE

Is it worth doing a string comparision on old vs new data in the
program, or should I just code to update all fields regardless and then
will the server optimize based on whether or not the data actually
changed?

Thanks,
Josh McFarlane

Logically SQL Server doesn't consider the diff at all.

If you want to only update small part of a large volume rows, doing string
comparision mostly yields better performance since that consumes less log
space.

For single line, I still suggest you "update when necessary", since some
triggers may sit there to enforce biz logic.

James

Show quoteHide quote

Josh McFarlane (dars***@gmail.com) writes:
Show quoteHide quoteSince it's a bit of work, I'm not sure that it's worth the effort, but
there are at least two scenarios where you can gain some performance.

One case is if you use merge replication. I'm not into replication myself,
but I got a question from a guy who is very good at replication, and he
wanted to reduce an update, so that only columns that were actually
changed were to be updated. Apparently, this made replication more
effective.

The other case concerns indexed columns. Consider this:

   SELECT * INTO Orders FROM Northwind..Orders
   create unique clustered on Orders
   create index ix On Orders(CustomerID)
   go
   BEGIN TRANSACTION
      UPDATE Orders
      SET    EmployeeID = 18
      WHERE OrderID = 11000

At this point runs query from another window:

    select count(*) from Orders WHERE CustomerID = 'RATTC'

RATTC is the customer id for order 11000. This query returns the
value 18 instantly, was not blocked. Now in the first window do this:

   UPDATE Orders
   SET    EmployeeID = 118,
          CustomerID = 'RATTC'
   WHERE OrderID = 11000

and now try the SELECT COUNT(*) again. This time it will block.

If you are generatnig the UPDATE statement dynamically, and in client
code, then filtering on columns that have actually changed is probably
manageable. In a stored procedure it is just painful.

generic audit trail best practice questions
append a string to all the values in a column SQL
Extracting from linked tables into pivot
What's a good SQL server naming convention
Optimizing an IN clause