I restored AdventureWorks2014 database from SQL Server 2014 to SQL Server 2017.

(I restored it twice, I changed the name on the 2nd restore, of course)

It works fine.

Now I tried to change the second database's compatibility to 140.

No errors.

Now I want to test if I really change its compatibility by trying a function that is only available in SQL Server 2017.

I ran:

SELECT CONCAT_WS(',','Count numers', 'one', 'two', 'three', 'four' ) AS counter;

No error.

The problem is, when I tried to ran it on the first database (which was still in compatibility 120), the script also worked. I looked at its compatibility but it still SQL Server 2014.

Is there something that I missed? Like SQL Server 2017 does something to older version databases that being restored to them that made the new function work in 2014 version databases?


The availability of commands or functions is not necessarily affected by the database compatibility mode.

CONCAT_WS was introduced in SQL Server 2017 and is not affected by changing the compatibility mode to 120. SPLIT_STRING is affected by changing the compatibility mode as indicated in the documentation for ALTER DATABASE (Transact-SQL) Compatibility Level.

Under SQL Serer 2017, this works under compatibility mode greater than 120

select * from string_split('a,b,c',',')

However, if you change the compatibility mode to 120, you will receive an error

Msg 208, Level 16, State 1, Line 1
Invalid object name 'string_split'.

Databases restored to later versions of SQL Server (2014 -> 2017) retain their existing compatibility mode (unless the later version cannot support the previous compatibility mode, which will force the minimum compatibility mode for that version). If you want to check the compatibility mode of your databases, simply select * from sys.databases. This query will tell you any databases that are not at the latest compatibility for that instance:

select * from sys.databases d where compatibility_level<>(select compatibility_level from sys.databases where name = 'master')

  • Thanks @Scott, I tried STRING_SPLIT and it returned error on version 2014 database. Does that mean that STRING_SPLIT is one way to check if a database is under compatibility 2017 and not below? Also, does that mean that I just have to restore the database with "lower" version in order to use new function given by a "newer" version? Like in this case, I can use CONCAT_WS in 2014 database version that was restored in 2017 SQL Server but I cannot use that in 2014 database version in 2014 SQL Server. – Edgar Allan Bayron Sep 18 '18 at 12:12
  • 1
    STRING_SPLIT should work on both databases restored to a SQL 2017 instance as long as you change the compatibility mode to be above 120 – Scott Hodgin - Retired Sep 18 '18 at 12:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.