Tuesday, November 1, 2016

Change compatibility level of the database and JSON in SQL Server 2016

Working with SQL Server and  JSON  requires a compatibility level of 130 (SQL Server 2016). The post here describes how you can convert a JSON String and format a table in SQL Server.

The TestPubs (copy of pubs legacy database from Microsoft) database in an instance of SQL Server 2016 brought over by a data-tier related process does not change its compatibility level. However you can change its compatiblity level from 100 to 130 and with compatibility 130, the following query takes in a JSON string and converts it to a table with defaults. Here type 5 means it is an object.

declare @json nvarchar(150)
SET @json=N'[{"color": "red","value":"#f00"},{"color":"green","value":"#0f0"},{"color":"blue","value":"#00f"},{"color":"cyan","value":"#0ff"}]';


Now if I revert the compatibility level to 100 and try the same query as before, I spawn an error as shown.