Difference between database features - SQL Server 2008 R2 navtive vs SQL Server 2012 with SQL Server 2008 compatibility set?
I am investigating the impact of upgrading from SQL Server 2008 R2 to SQL Server 2012. To reduce the impact of the upgrade, I am planning to restore / create our application database onto SQL Server 2012 with compatibility level 100 (SQL Server 2008 and SQL Server 2008 R2). Are there any differences in feature support for database running in native mode on SQL Server 2008 R2 vs a database installed on SQL Server 2012 with compatibility level 100 set?
The compatibility level allows the database to effectively operate in certain capacities at a lower version of SQL Server. This is primarily syntax related although there are certain instances where certain portions of the engine will operate differently, such as the query optimizer between SQL 2014 & lower versions. Were you looking for a specific feature/component? Personally, I don't like leaving my databases in a lower compatibility level if I absolutely don't have to. This tends to leave things hanging around. I would recommend that you test the migration by upgrading the compatibility level to SQL 2012 and see if anything in the application breaks. If not, leave it in the 2012 compatibility level. Resource:
https://msdn.microsoft.com/en-us/library/bb510680(v=sql.120).aspx Hope this helps!