Post reply

Compatibility mode

  • January 28, 2019 at 2:49 pm

    #409467

    When the database is upgraded from SQL 2008 to SQL 2014 and the database is still in backward compatibility mode. Will having the backward compatibility mode could limit the use of 2014 features or having backward compatibility mode will allow to work for both old and new features? What options i could be loosing if my database is still pointing to backward compatibility mode? Please advise?

  • January 29, 2019 at 8:05 am

    #2020411

    Hi there, 

    There are a number of items to consider when running in older compatibility modes...

    Main point to consider is SQL 2014 introduced a new cardinality estimator and query optimiser which help improve query performance, therefore a DB running in the older compatibility mode SQL 2008R2 (100) would not be able to take advantage of those new features. 

    Other items are also affected when updating compatibility modes, however these can always be ruled out by performing the update in a test environment and running an end to end test to ensure  any application is not affected and overall database performance is either equivalent or better.

    MS Link below to a relevant document. on the subject may also help..

    https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-compatibility-level?view=sql-server-2017#differences-between-lower-compatibility-levels-and-level-120

    Hope that helps

    James....

  • January 29, 2019 at 8:39 am

    #2020423

    You do lose some SQL 2014 features. New keywords don't work, as the purpose of compatibility mode is to allow older code that might have a keyword, such as the introduction of throw in 2012. If you run in 2008R2 compat mode, you can't use THROW in code. It will flag as a keyword.

    In 2014, using 2012 or earlier mode means you don't get new cardinality estimation.

    This is intended to make the engine work as that version did, although some things, like a code change in backup throughput in the engine, does still work. Don't count on new features working until you switch modes.

    Follow me on Twitter: http://www.twitter.com/way0utwest
    Forum Etiquette: How to post data/code on a forum to get the best help
    My Blog: www.voiceofthedba.com

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply