- Adam Thurgar
Backward compatibility
There have been many times when I have reviewed SQL Server installations and found databases in a backward compatibility level. Backward compatibility allows applications not to be tested or changed when the database server installation is upgraded.
https://technet.microsoft.com/en-us/library/cc707787(v=sql.110).aspx
Sometimes it is just a case that you have done an in-place upgrade of your SQL Server and you didn't change the compatibility level. Or that the restore of the database to a new server the compatibility level didn't get changed.
Or it could be just a case of being lazy and not being willing to test your applications against the current compatibility level.
The problem with backward compatibility is that your T-SQL code may be using deprecated features that at some point may longer be supported. It can also cause performance issues as the optimizer has to work differently for these different branches. Finally - sooner or later you are going to have to change that compatibility level as the version of SQL Server you upgrade to will not have that compatibility level available. You are just delaying the inevitable. It may be the case that your application will work fine, but you need to test it or check with the vendor if they support the compatibility level.
I know of one major financial institution who upgraded all of their SQL Server 2000 and 2005 servers to SQL Server 2008, but left all the databases in a backward compatibility level. They had avoided the issue of being on an unsupported version of SQL Server, but have only delayed the database level upgrade that at some time they will have to go through and test their applications against this compatibility level.