February 4, 2016 at 5:24 am
From a customer, I am asked:
Highest compatibillity level in SQL server 2014 supported?
90 (sql 2005)
100(sql 2008)
110(sql 2012)
120(sql2014)
As iI am running SQL server 2014, I belive that the answer is 120, but how to find the figure?
some scripting on the DB, I think.
Best regards
Edvard Korsbæk
February 4, 2016 at 5:30 am
Found:
SELECT compatibility_level
FROM sys.databases WHERE name = 'Easyplan_drift';
GO
So, I found the answer myself.
Best regards
Edvard Korsbæk
February 4, 2016 at 6:12 am
You seem to have found the correct answer, using the wrong technique 🙂
Different versions of SQL Server support different compatibility levels (see here).
Within a SQL Server instance, databases can have different compatibility levels, though none higher than that supported by the version of SQL Server which is running.
Your script found one such compatibility level, which just happened to be the maximum available on that instance.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
February 4, 2016 at 6:19 am
Well, I found the correct answer in the end.
checked on the DB, which was used by the customer - it had level 110.
Next question is of cause, what is to be expected with setting the compability level up to 120. i.e. what is not backward compatible?
Best regards.
Edvard Korsbæk
February 4, 2016 at 6:26 am
In this article https://msdn.microsoft.com/en-us/library/bb510680(v=sql.120).aspx you'll find a comparison between compatibility level 110 and 120.
February 4, 2016 at 6:30 am
Edvard Korsbæk (2/4/2016)
Well, I found the correct answer in the end.checked on the DB, which was used by the customer - it had level 110.
Next question is of cause, what is to be expected with setting the compability level up to 120. i.e. what is not backward compatible?
Best regards.
Edvard Korsbæk
You need to look at the differences in behaviour between the versions (see further down the page in the link I included in my previous post) and assess whether any of these differences is likely to have an impact in your situation. The difference most likely to affect you is the new cardinality estimator, in my opinion. This may mean that you need to tune some queries which previously ran perfectly satisfactorily. I strongly suggest that you do not increase the compatibility level without testing.
PS, it is 'compatibility', not 'compability'.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
February 4, 2016 at 6:32 am
Phil Parkin (2/4/2016)
The difference most likely to affect you is the new cardinality estimator, in my opinion. This may mean that you need to tune some queries which previously ran perfectly satisfactorily. I strongly suggest that you do not increase the compatibility level without testing.
Seconded.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply