Added built-in support for JSON in SQL Server 2016

Original address: http://www.ncloud.hk/%E6%8A%80%E6%9C%AF%E5%88%86%E4%BA%AB/built-in-json-support-in-sql-server-2016/

Supporting JSON at the database layer is one of the top features of the request ranking, with more than 1000 votes on the Microsoft Connect website. Microsoft is committed to providing built-in JSON support in the SQL Server 2016 release. Note that this is not a replica of the native support for SQL Server 2005 features XML. Microsoft's goal is to create a simple and easy-to-use framework for working with JSON documents. In this article, I'll describe the JSON characteristics of a planned implementation in SQL Server 2016. The feature support schedule is as follows:

• SQL Server CTP2-Ability to format data as JSON export, see the blog post for more information about this feature.
• SQL Server CTP3-Ability to load JSON strings and resolve to table variables, to extract values from JSON nodes, and to set indexed properties on JSON columns.

The storage format of JSON data

The first thing we need to figure out is that the built-in JSON support does not equal the native JSON type. In SQL Server 2016, the JSON data will be stored using the nvarchar type for the following reasons:

• Mobility-we've found that people have begun to use strings to represent JSON data, and if new JSON types are introduced, people have to change the database schema and reload the data with new features.
• attribute compatibility-The nvarchar data type has been widely supported by various components of SQL Server, so JSON is supported by these components. You can put JSON into the Hekaton,temporal or column store table, apply security policies such as row-level permission control, use the B-tree and FTS indexes, and JSON as parameters and return values for stored procedures or user-defined functions. You do not have to consider whether JSON is compatible with a feature x, because JSON is compatible as long as the nvarchar is compatible with feature X. There are also some limitations, since Hekaton and column store do not support LOB values, so you function to save small JSON documents, but once we are Hekaton and column The store adds support for lobs so you can store the JSON document anywhere.
• Client code Compatibility-currently we do not have a standard JSON object type (to the Xmldom object) in the client application. Web, mobile, and JavaScript applications use the built-in parser to convert JSON text into objects. Using object in JavaScript to represent JSON data, it is not possible to provide a proxy type for a few relational database built-in JSON types. In C #. NET, a large number of developers use the Json.NET parser and the built-in Jobject, jarray types, but these are not standard schemes and may not be included in ADO, so C # applications get plain text JSON from the database and use their favorite parser for processing.

Note that you can still introduce json.net or other similar libraries using your own JSON type that can be implemented by the CLR. Even if you don't like coding for CLR user-defined types, you can download a large number of out-of-the-box implementations so you don't need to be aware of the differences between native JSON types and user-defined JSON types. As long as for most. NET applications are fast enough. If you think that the JSONB format of PostgreSQL or zipped JSON text is better, then you can use them with UDTs, and you can create member methods to take advantage of that format's properties. Currently we have not found anyone trying to encapsulate the JSONB format using a UDT, so the JSONB format is not considered in SQL Server 2016.

Our focus is on providing better functions and improved query performance, rather than saving storage space. We know that there are native JSON types and support for JSONB in PostgreSQL, but we are not sure if the performance is more advantageous than the CLR, so in SQL Server 2016 we decided to focus on the other more important aspects (you want to see the SQL Is there a native JSON class in server that does not have a related built-in function? I think the answer is no). However, we will discuss with you in the community whether it is considered better to use native JSON types than CRL JSON or text JSON, and you can create a topic for discussion with us on the Microsoft Connect website. We decided to first implement for JSON and Openjson also because this feature is needed by a large number of users and is difficult to implement through the CLR.

Therefore, our focus is to export/import some of the built-in JSON processing functions. One might say that the performance of these functions is not fast enough. Our strategy is to provide a usable implementation before continuing to optimize its performance. However, the fastest way to process JSON is to build a JSON parser in the database layer. You can use a CRL type or CLR parser as an external library but its performance will not be faster than native code parsing JSON.

Added built-in support for JSON in SQL Server 2016