SQL Server Performance

Updating sql 2000 tables with an unchanged foreign key

Discussion in 'Getting Started' started by davisonc, Jul 23, 2008.

  1. davisonc New Member

    If you are updating a table using a generic update (e.g. generated by codesmith) and you are passing in all the column values for a selected row, if there are foreign keys or indexes on the table and these have not changed i.e. the values passed in are the same as the values present does sql try to update them or is it clever enough to know that they haven't changed and only updated the values that have ?
    The reason for the question is one of performance. Of course I am sure a bespoke update to only affect the columns that have changed would be ideal but I am keen to understand the effect of a generic update where foreign keys exist.
  2. TommCatt New Member

    Yes, if you have an update with a clause like "set col1 = @somevalue, fk1 = fk1" then the system will still check the target table of the foreign key. So if you have a tool that generates update statements for all of the table columns, and a few of those columns are foreign keys, you will encur the overhead of the system RI checking. This may not be too pronounced unless there is some locking of one of the target tables (which is how I tested this!).

Share This Page