Microsoft is constantly improving SQL Server. There are new features in each new version and there are some old features that have been eliminated from the newer version. One reason for the eliminations is to move SQL Server closer to ANSI compliance for relational databases.
This is one reason that when a SQL database instance is upgraded we sometimes find that the applications that worked well in the previous version do not run well or can’t run at all in the new version.
To take an example from long ago, when a SQL 2000 instance was upgraded to SQL 2005 a lot of applications could not run properly and threw a constant stream of error messages. In this particular case, the problem was that the old syntax for outer joins ( *= =*) had been dropped to get SQL Server closer to ANSI compliance and to avoid the problems caused by the old syntax.
In some cases, this old syntax results in an ambiguous query that can be interpreted in more than one way and it could also cause an unexpected Cartesian join if you are careless with it.
If developers of the application had used the old outer join syntax (sometimes called “the Oracle syntax“) the application would throw a critical error and exit. That was bad enough, but there were a lot of other changes that caused problems when upgrading from SQL 2008.
Developers had two choices:
- Rewrite the application, which sometimes amounted to starting over.
- Put the SQL instance in SQL2000 compatibility mode (80).
This would allow a later version to imitate an earlier. this was a simpler way to solve the problem. However, this comes at a big price. This instance, in SQL compatibility level 80 cannot use any of the features added to later versions.
As time went on, new dba’s forgot that the database was in compatibility level 80. They were locked out of all features that came later than SQL 2000. That resulted in poor performance in many cases.
Now we are looking at SQL 2016 and the future. These new versions will not support compatibility levels older than two versions behind the current version.
If your SQL instance is set to a different compatibility mode than the version you are running on you might want to start thinking now how you are going to catch up to SQL Server
If you want to check your current compatibility level here is a simple query:
select name, compatibility_level from sys.databases
Here is a table of compatibility levels.
130 SQL Server 2016
120 SQL Server 2014
110 SQL Server 2012
100 SQL Server 2008
90 SQL Server 2005
80 SQL Server 2000