JSON storage in SQL Server 2016

As XML support was introduced in SQL Server 2005, the native XML data type has been implemented as well. SQL Server 2016 introduces built-in support for JSON but unlike XML, there is no native JSON data type. Here are the reasons that the Microsoft team exposed for not introducing a new data type:

• Migration: Prior to SQL Server 2016, developers already had to deal with JSON data

• Cross feature compatibility: The data type nvarchar is supported in all SQL Server components, so JSON will also be supported everywhere (memory-optimized tables, temporal tables, and Row-Level Security)

• Client-side support: Even if a new data type were introduced, most of the client tools would still represent it outside SQL Server as a string

They also noted that, if you believe that the JSON binary format from PostgreSQL, or a compressed format, such as zipped JSON text is a better option, you can parse JSON text in UDT, store it as JSONB in a binary property of CLR UTD, and create...