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:
nvarchar
is supported in all SQL Server components, so JSON will also be supported everywhere (memory-optimized tables, temporal tables, and Row-Level Security)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 member methods that can use properties from that format. You can find more details about their decision at https://blogs.msdn.microsoft.com/jocapc/2015/05/16/json-support-in-sql-server-2016.
Although these arguments make sense, a native JSON data type would be better, especially from a performance point of view. However, this will require more effort and longer time frames for development and the time between the release of new features is shorter than that. This should also be taken into account when you judge the feature. JSON support in SQL Server 2016 would be complete with a native data type, but built-in support is a respectable implementation and this is a very useful feature.
Since there is no JSON data type, JSON data is stored as text in NVARCHAR
columns. You can use the newly added COMPRESS
function to compress JSON data and convert it to a binary format.