Start Updating multiple databases

Updating multiple databases

The compatibly level setting is used by SQL Server to determine how certain new features should be handled.

In this case there are two distinct values, so we can do it in two UPDATE statements: So we can reduce the number of queries from five to two. But the extent to which this helps us reduces as the proportion of distinct SET values goes up.

Eventually every SET value is different, for example: UPDATE staff SET salary = 1125 WHERE name = ' Bob'; UPDATE staff SET salary = 1200 WHERE name = ' Jane'; UPDATE staff SET salary = 1100 WHERE name = ' Frank'; UPDATE staff SET salary = 1175 WHERE name = ' Susan'; UPDATE staff SET salary = 1150 WHERE name = ' John'; Since every SET clause is now different, we can’t coalesce any queries using the approaches mentioned before, so we still have one UPDATE statement for every row requiring changes. What if we had a database table which contained a representation of the updates we wanted to make, e.g.

Although SQL Server has changed its naming convention to SQL Server 2000, 2005 and soon to be released 2008 the internal version numbers still remain.

The traditional advice for improving performance for multiple UPDATE statements is to “prepare” the required query once, and then “execute” the prepared query once for each row requiring an update.

The compatibility level that is used is the compatibility level of your model database.

Here is a sample CREATE DATABASE command, but there is not an option to change the compatibility level.

We can easily contrive for an “updates” table to exist by creating a temporary table and populating it.