Updating multiple databases Freeadultchatroom only
The compatibly level setting is used by SQL Server to determine how certain new features should be handled.This was setup so you could migrate your databases to a later release of SQL Server without having to worry about the application breaking.This setting can be changed forward and backwards if needed, so if you do change your compatibly level and find that there are problems you can set the value back again until you resolve all of the issues that you may be facing during the upgrade.In addition, there are certain features that only work if the database is set to the latest compatibly level, therefore to get all of the benefits of the version of SQL Server you are running you need to make sure you are using the latest compatibly level.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.
This tip will show you how to check the current compatibly level, how to change the compatibly level and also some of the differences between earlier versions and SQL Server 2005.
A requirement arises in many systems to update multiple SQL database rows.
For small numbers of rows requiring updates, it can be adequate to use an UPDATE statement for each row that requires an update.
The dominant factor in the time taken to complete the overall operation tends to be the “admin” work in conveying the application’s intention to the database server rather than the actual updates to the database.
A more effective solution to this problem is to attempt to reduce the number of UPDATE statements.
The first thing that you need to do is to check the compatibility level that your database is running under.