• My advice: Set compatibility mode to 120 for all DBs until you find a CE problem. You should be doing a lot of testing as part of your move to SQL2014 regardless of DB compatibility mode, so there is no reason why this testing should not be using all the features of the new SQL version.

    If you have a problem during testing, then troubleshoot it as normal. If it is a CE problem, then decide how you will address the problem - either live with it, adjust SQL code or indexes, etc to overcome it, or last of all lower the compat mode. If you do lower the compat mode, then re-test everything that accesses that database to see if anything new is affected.

    The most time-efficient and risk-efficient place to test out improvements to existing features is when you are upgrading. You get a budget allowance for testing at this time so make the most of it. You will struggle to get budget at a later date to spend time testing improvements like CE.

    FWIW my old place were very much into automated testing so regression testing of new SQL versions was standard. We often found a small number of SQL statements that ran significantly slower in the new version of SQL, as well as a much larger number that ran faster. Sometimes we fixed the SQL before going live, more often we lived with the problem and added it to the Technical Debt queue, but we never took the view we should not fully exploit the new SQL version.

    Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

    When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara