locked
What is the compatibility level and the internal version of the database? RRS feed

  • Question

  • For example, when I create a database with Sql Server 2014, the compatibility level is 120, and has a internal version too.

    When I want to migrate the data from Sql Server 2014 to Sql Server 2016, a user advised me to backup the database in sql server 2014 and restore in the sql server 2016. But the database still has the compatibilty version 120, instead of 130 of the compatibility version of sql server 2016.

    Also this user told me that I can use ALTER DATABASE Compatibility Level (Transact-SQL) to change the compatibilty level of the database.

    My doubt is, what is a difference between a database with compatibility level 120 and another with 130? Becuse if I can change the compatibility version and I would have all the features of sql server 2016, why sql server has different compatibilty versions? Why don't use always the same version?

    And what is the difference between compatibility level and internal database version?

    Thanks.

    Monday, June 13, 2016 8:23 PM

Answers

  • The compatibility level doesn't change automatically to match the version of SQL Server, so if you restore backup of the SQL Server 2014 database on SQL Server 2016, the compatibility level will remain the same. You would need to change it manually.

    Also, you will not be able to restore database from SQL Server 2016 back to SQL Server 2014. With SQL Server you can only restore backup from lower version to the upper version but not vs. versa. Keep that in mind.

    The compatibility level exists so you should be able to support older versions of SQL Server. However, this is not 100% guarantee that if everything works in the particular compatibility level, it will be working the same in that particular SQL Server version. So, that's the main idea of compatibility level - to be able to develop for earlier versions that clients may be using.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Monday, June 13, 2016 8:29 PM

All replies

  • The compatibility level doesn't change automatically to match the version of SQL Server, so if you restore backup of the SQL Server 2014 database on SQL Server 2016, the compatibility level will remain the same. You would need to change it manually.

    Also, you will not be able to restore database from SQL Server 2016 back to SQL Server 2014. With SQL Server you can only restore backup from lower version to the upper version but not vs. versa. Keep that in mind.

    The compatibility level exists so you should be able to support older versions of SQL Server. However, this is not 100% guarantee that if everything works in the particular compatibility level, it will be working the same in that particular SQL Server version. So, that's the main idea of compatibility level - to be able to develop for earlier versions that clients may be using.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Monday, June 13, 2016 8:29 PM
  • And this things that could not work for example from 2016 to 2014, is it new features, for example, better performance?

    What is the difference between 120 and 130 compatibility versions?

    EDIT: I have find this documentation:

    https://msdn.microsoft.com/en-us/library/bb510680.aspx

    I can see the differences between version 120 and 130 version.

    My quiestion is, if I have a backup of a version 120 and restores in sql server2016, and change the version from 120 to 130, would I have the advantage of "The Insert in an Insert-select statement is multi-threaded or can have a parallel plan" (version 130) or I still have the behavior "The Insert in an Insert-select statement is single-threaded" (version 120)?

    Thanks.


    Monday, June 13, 2016 9:29 PM
  • You would have to test that, but I think if you adjust compatibility level, then you will get all new enhancements of SQL 2016 which are applicable.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Monday, June 13, 2016 9:56 PM
  • And how could I check this features?

    Thanks.

    Monday, June 13, 2016 10:00 PM
  • Well, you would need to capture actual execution plan (say, using set statistics command) in both compatibility levels and  compare.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Tuesday, June 14, 2016 1:55 AM