SQL SERVER – Fix Error: Invalid object name STRING_SPLIT

May 3, 2018

Just the other day one of the readers sent me following email after reading my earlier blog post: STRING_SPLIT Function Performance Comparison. He really liked the new feature of SQL Server 2016 and decided to start using this new feature. However, as soon as he started to use this feature, he received following error. He was very much confused why he was not able to use the STRING_SPLIT function even though he had SQL Server 2016.

He attempted to run following command in SQL Query Editor.

DECLARE @VarString NVARCHAR(400) = 'Mike,John,Miko,Matt';
SELECT value
FROM STRING_SPLIT(@VarString, ',');

When he tried to run the command he got following error:

Msg 208, Level 16, State 1, Line 7
Invalid object name ‘STRING_SPLIT’.

He immediately wrote to me asking if I know the reason for the error and if there is any way I can help him to fix the error. Honestly, I immediately knew the reason what was the problem for him as many of my customers often faces similar issues.

WORKAROUND/SOLUTION

The primary reason for the error to show up is the compatibility level. The new function STRING_SPLIT is introduced in SQL Server 2016 and if your database compatibility is of SQL Server 2014 or earlier version, you may end up with this error. If you want to use this function, you need to set your compatibility of the level to SQL Server 2016 or later version of SQL Server.

Here is the quick script which can help you to change your compatibility to SQL Server 2016.

ALTER DATABASE [AdventureWorks2014]
SET COMPATIBILITY_LEVEL = 130 -- For SQL Server 2016
GO

You should change the compatibility level value to 140 if you want to set your database compatibility to SQL Server 2017.

SQL SERVER - Fix Error: Invalid object name STRING_SPLIT split_string-c-800x453

Once you change your database compatibility level to either SQL Server 2016 or SQL Server 2017, the script just worked fine and produced following results.

Please note that you should set your database compatibility to the level which is appropriate for your database. Changing the compatibility of the database without proper testing may result in errors.

Reference: Pinal Dave (https://darkslategrey-bat-805937.hostingersite.com)