Blog Post

ISJSON() restriction in SQL Server 2016 and not in SQL Server 2017

,

You should have a database on SQL Server 2016 instance with an In-Memory file group so that you can create tables with the MEMORY_OPTIMIZED option set to ON. Like in the example below

--table
CREATE TABLE dbo.People (
 Id int primary key nonclustered,
 InfoJson nvarchar(max) -- nvarchar(100), nvarchar(1000)
) WITH (MEMORY_OPTIMIZED=ON)

try to create the next constraint

--constraint
ALTER TABLE dbo.People
ADD CONSTRAINT [CK_People_InfoJason]
 CHECK ( ISJSON( InfoJson )> 0 )

you’ll end up with the following error message

Msg 10794, Level 16, State 95, Line 10
The function ‘isjson’ is not supported with memory optimized tables.

This tip is about the  ISJASON() function that is not currently possible to apply on memory optimized tables in a check constraint.

I set a “Question of the Day” on sqlservercentral.com about it. You can find the discussion here http://www.sqlservercentral.com/Forums/Topic1792292-3103-1.aspx.

Update (June 14, 2017):

Later, in SQL Server 2017, some new features were added to the product so this restriction from is not present anymore. You can see the details in the official documentation link.

Original post (opens in new tab)
View comments in original post (opens in new tab)

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating