In the last few years, JSON has been established as a standard format for data exchange among applications and services. XML is still the exchange standard (and will remain so), but many applications communicate by exchanging JSON data instead of XML documents. Therefore, the most important relational database management system products need to support JSON.

Two release cycles after the feature was requested by the community, Microsoft has implemented built-in JSON support in SQL Server 2016. The support is not as comprehensive as for XML, but for most databases and workloads, it will be quite fine.

This chapter explores how SQL Server stores and processes JSON data, with a comprehensive comparison between JSON and XML support in SQL Server.

The most important actions related to JSON data in SQL Server are demonstrated in detail:

• Formatting and exporting JSON data from SQL Server
• Converting JSON data to a tabular format
• Importing JSON data to SQL Server
• Validating, querying, and modifying JSON data

Finally, you will be made aware of limitations caused by missing JSON data types and indexes, and given advice on how you can improve the performance of JSON queries despite these limitations.