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?
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.
SQL Server 2014 is different from any previously released version of SQL Server in the way that COMPATILITY_LEVEL now changes the way how the Query Optimizer behaves!
Microsoft introduced a brand new Cardinality Estimator in SQL Server 2014 and this new Cardinality Estimator is very interesting. In most cases, customers will see benefits from using the new Cardinality Estimator in SQL Server 2014. (There are some cases where query performance is negatively impacted by using the new Cardinality Estimator though)
In order for your database queries to benefit from the new Cardinality Estimator, the COMPATIBILITY_LEVEL of your database must be at 120.
Any value lower thant 120 will cause your queries to use the old Cardinality Estimator and your query plans will not be able to take advantage of the new Cardinality Estimator.
SQL Server 2014 has a great feature which can not be compared with any other version. It is the in-memory computing feature of SQL Server 2014. So if you want to get use of this in-memory feature of SQL Server 2014, upgrade to it with converting your codes for supporting native in-memory development features of Transact-SQL. SAP is promoting the in-memory computing under the brand of HANA. SQL Server 2014 and SQL Server 2016 or any other in-memory databases combines OLTP and OLAP properties providing fast analysis, reporting, etc besides managing transactional tasks at the same time.
