I was working on STRING_SPLIT function for
one of the query building programs and the function seemed not working for me. While
checking the internal SQL conditions, I happen to find that the cause was due
to the compatibility of the database to the SQL Server.
The list of compatibility levels to the corresponding SQL Server version is showing in the picture below:
Now, I had a very hard time to find out which all databases were on my server with different compatibility levels. I wrote a quite simple query to find that out to get those findings very simple for us.
select name, compatibility_level ,
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
If you find that the compatibility level of a database is not the actual value recommended by Microsoft but set on your engine, then please follow the syntax to alter the compatibility level of a database.
ALTER DATABASE [your db name]
SET COMPATIBILITY_LEVEL = 140 -- 140 Compatibility level is for SQL Server 2017
Please change the compatibility level of your database as per your SQL Server version using this script.
Added Server 2022 to the Script:
ReplyDeleteselect name, compatibility_level , 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'
WHEN 160 THEN 'SQL Server 2022'
ELSE 'new but not known yet - '+CONVERT(varchar(50),compatibility_level)
END
from sys.databases