Have you ever wondered what features may or may not be available when your database is running in a different compatibility level than your SQL Server version? I recently came across a situation where a database was on SQL Server 2014 (compatibility level 120), but the database compatibility level was set to SQL Server 2012 (110). This led me to wonder, what features of SQL Server 2014 are disabled in SQL Server 2012 compatibility mode? Certainly the new cardinality esitmator is unavailable, but what about in-memory OLTP? Or backup encryption?
Thankfully, Microsoft has all of that documented here: https://msdn.microsoft.com/en-us/library/bb510680.aspx You can scroll down the page and see the differences between each of the compatibility levels, all the way fromSQL Server 2005 (compatibility level 90) to SQL Server 2016 (compatibility level 130).
As it turns out, many of the new features of SQL Server 2014 are still available when your database compatibility level is set to 110. So, even though you can’t take advantage of the new cardinality estimator, you do still have access to cool features like in-memory OLTP and backup encryption!