Advanced Application of Database statements to modify compatible database versions

Databases are almost an essential element in project development. I believe SQL Server is very familiar to everyone. Have you ever encountered such a problem:

Database incompatibility issues occur when you analyze or deploy a database. I got a database of version 08 or version 12. As a result, the deployed server only has an SQL Server of version 05 or version 08. is a small compatibility problem a headache.

A common solution is to export database scripts and back up databases. Then, create a database on the SQL Server 2005 or 08 server and restore the data through the backup file. This is too troublesome. Since SQL is so powerful, it is certainly easier to consider compatibility issues. If you modify the compatibility, it will save a lot of time. I have been searching for it online for a long time.

Modify compatible database versions

Syntax
Alter database database_name
Set compatibility_level = {80 | 90 | 100 | 110}
 
Parameters
Database_name
The name of the database to be modified.

Compatibility_level {80 | 90 | 100 | 110}
The SQL Server version compatible with the database. This value must be one of the following values:
= SQL Server 2000
= SQL Server 2005
= SQL Server 2008
= SQL Server 2012

Remarks
For all SQL Server 2012 installations, the default compatibility level is 110. Unless the model database has a lower compatibility level, the databases created in SQL Server 2012 are set to this level. When you upgrade a database from any earlier version of SQL Server to SQL Server 2012, if the database compatibility level is not below 80, the database retains its existing compatibility level. Databases with a compatibility level lower than 80 set the database Compatibility Level to 80. This applies to both system databases and user databases. You can use alter database to change the database compatibility level. To view the current Compatibility Level of the database, query
The compatibility_level column in the SYS. Databases directory view.

Backward compatibility with the Compatibility Level
The Compatibility Level only affects the behavior of the specified database, but does not affect the behavior of the entire server. The compatibility level is only partially backward compatible with earlier versions of SQL Server. The compatibility level can be used as a temporary migration tool to solve version differences between the behaviors controlled by the relevant compatibility level settings. If the existing SQL Server application is affected by the behavior differences in SQL Server 2012, convert the application to make it run properly. Then, use alter database to change the compatibility level to 110. The new compatibility settings of the database will become the current database next time (whether it is used as the default database or
Statement.

Best practices
If the compatibility level is changed when the user connects to the database, the activity query may generate an incorrect result set. For example, if the compatibility level changes during the preparation of a query plan, the plan may be based on both the old and new compatibility levels, resulting in incorrect plan, the results may be inaccurate. In addition, if you place the plan in the Plan cache for subsequent query reuse, the problem may be more complicated. To avoid inaccurate query results, we recommend that you use the following process to change the database Compatibility Level:

. Use alter database set single_user to set the database to single-user access mode.
. Change the database compatibility level.
. Set the database to multi-user access mode by using alter database set multi_user.
For more information about setting the database access mode, see alter database (TRANSACT-SQL ).

-- Obtain the database compatibility level select name, compatibility_level, recovery_model_desc from sys. databases with (nolock) -- set the user to single-user access mode alter database Test Set single_user -- modify the database compatibility level alter database Test Set compatibility_level = 90 -- or exec sp_dbcmptlevel test, 90; go -- set the user to multi-user access mode: Alter database Test Set multi_user

Syntax

Sp_dbcmptlevel [[@ dbname =] Name]
[, [@ New_cmptlevel =] version]
 

Parameters
[@ Dbname =] Name
To change the name of a compatible database. The database name must comply with the identifier rules. The data type of name is sysname, and the default value is null.

[@ New_cmptlevel =] version
The version of the SQL Server to which the database is compatible. The data type of version is tinyint, and the default value is null. This value must be one of the following values:
= SQL Server 2000
= SQL Server 2005
= SQL Server 2008
= SQL Server 2012

Return code value
0 (successful) or 1 (failed)

Result set
If no parameter is specified or the name parameter is not specified, sp_dbcmptlevel returns an error.

If the name is specified but no version is specified, the database engine returns a message that displays the current Compatibility Level of the specified database.

Remarks
For more information about the compatibility level, see alter database Compatibility Level (TRANSACT-SQL ).

Permission
Only members of the database owner, SysAdmin fixed server role, and db_owner fixed database role can perform this process only if you want to change the current database.