JSON storage in SQL Server 2017

Since 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 gave 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...