Advanced Application of Database statements to modify compatible database versions

Databases are almost an essential element in project development. I believe everyone is familiar with sqlServer. Have you ever encountered such a problem: when analyzing or deploying databases, the database is incompatible. Modify database compatibility version syntax ALTERDATABASEdatabase_nameSETCOMPATIBILITY_LEV

Databases are almost an essential element in project development. I believe everyone is familiar with SQL Server. Have you ever encountered such a problem: when analyzing or deploying databases, the database is incompatible. Alter database database_name SET COMPATIBILITY_LEV

Databases are almost an essential element in project development. I believe everyone is familiar with the Hong Kong Server and SQL Server. Have you ever encountered such a problem:

Database incompatibility issues occur when you analyze or deploy a database.

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 and U.S. servers, 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 database compatibility settings will take effect the next time the database becomes the current database (whether it is specified as the default database at login or in the USE 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 is changed during the preparation of a query plan, the plan may be based on the old and new compatibility levels at the same time, resulting in incorrect plan, Hong Kong Space, 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 ).

Name, compatibility_level, recovery_model_desc FROM sys. databases WITH (NOLOCK) test SET SINGLE_USER TESTsp_dbcmptlevel TEST, 90; 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
(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.