Restoring AdventureWorks2014 Database to SQL Server 2017

Question

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?

Answer 1

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')