I need to upgrade the SQL Server 2008 to SQL Server 2014 . SQL Server 2008 is currently running on compatibility level 100. I need to upgrade to SQL Server 2014 with compatibility level 100 this year and to compatibility level 110 next mid year.
If I deploy SQL Server 2014 with compatibility level 100 will I able to see the new features of SQL Server 2014 or I need to upgrade the compatibility level also in order to use new features of SQL 2012 and SQL 2014?
Best Answer
Compatibility levels are the way to tell SQL Server how to interpret the T-SQL and how the database will behave. Paul Randal wrote up a short comparison about the differences between version and compatibility levels about 9 years ago.
In a nutshell he says there is no relationship between compatibility versions and a database versions. But you may have access to the new features in an older compatibility version of a database they just may not behave as expected. One extra thing of note is that if you have multiple databases on the same server you can set each of them to their own compatibility level.