Checking Compatibility Level Of All Databases In SQL Server
SQL Server is a relational database management system (RDBMS) developed by Microsoft. It is a server-based system that can be accessed by multiple users simultaneously. It offers many features, including data security, scalability, and high performance.
One important feature of SQL Server is its compatibility level. This refers to the ability of the database to run different versions of SQL Server. For example, if a database has a compatibility level of SQL Server 2012 (110), it can run on any version of SQL Server from 2012 onwards. However, it cannot run on an earlier version of SQL Server.
The compatibility level can be changed by using the ALTER DATABASE command. This can be useful if you want to upgrade to a new version of SQL Server but you’re not ready to make all your databases compatible with the new version. You can change the compatibility level back to the previous version at any time.
It’s important to check the compatibility level of all databases in SQL Server because it can impact performance and stability. Incompatible databases can cause errors and crashes. Therefore, it’s best to ensure that all databases are compatible with the current version of SQL Server before upgrading.
Checking the compatibility level of all databases in SQL Server
As organizations move to newer versions of SQL Server, they should check the compatibility level of all their databases. The compatibility level determines which version of SQL Server a database can be used with. To check the compatibility level of all databases on a server, run the following query:
Solution
Here is a simple TSQL to simplify the work:
Query
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
SELECT name, compatibility_level, SQL_version_name = CASE compatibility_level WHEN 80 THEN 'SQL Server 2000' WHEN 90 THEN 'SQL Server 2005' WHEN 100 THEN 'SQL Server 2008/R2' WHEN 110 THEN 'SQL Server 2012' WHEN 120 THEN 'SQL Server 2014' WHEN 130 THEN 'SQL Server 2016' WHEN 140 THEN 'SQL Server 2017' WHEN 150 THEN 'SQL Server 2019' ELSE 'new but not known yet - ' + CONVERT(varchar(50), compatibility_level) END FROM sys.databases |
Steps
To run a script on a Microsoft SQL Server using SSMS.
- Open Microsoft SQL Server Management Studio
- Select [New Query] from the toolbar
- Copy the ‘Example Query’
- Select the database to run the query against
- Paste the ‘Example Query’ into the query window
- Click the [Execute] button
Result
Scenario
To alter the compatibility level of a database
Solution
Here is a simple TSQL to simplify the work:
Query
|
1 2 |
ALTER DATABASE [your db name] SET COMPATIBILITY_LEVEL = 140 -- 140 Compatibility level is for SQL Server 2017 |
