SQL Server 2016: JSON, Part 5 – Storage and Indexation

Total: 4 Average: 3.5

In this article, we will explore how we can store and index JSON objects in a database.

As we have already discussed, developers did not add a separate type for JSON to SQL Server 2016 as they did for XML.

Thus, you can use any string data types to store JSON. VARCHAR(MAX) and NVARCHAR(MAX) are more suitable depending on whether there are Unicode symbols in JSON files. However, you can use “smaller” data types if you are sure that your JSON objects will fit those datatypes correctly.

If you worked with XML, then you should remember that SQL Server provides several types of indexes, which help to reduce the selection time. However, there are no such indexes for string data types that are supposed to store JSON. As a workaround, we can use calculated columns. They can represent particular properties of JSON files by which we are going to search. We can create indexes for these columns.

I would recommend you to create calculated columns as persisted. Otherwise, there is no sense to add indexes to those columns. Additionally, you can create both standard and full-text indexes if you want to get a flexible search by the array content or by the objects. In addition, full-text indexes have no special rules for processing JSON. They simply split the text into separate words using double quotes, commas, brackets, etc. as unit separators.

In the next article, we will compare the procedures of storing and processing JSON and XML in SQL Server. We will try to find the best format to store and transmit unstructured data.

Sergey Olontsev

Software Expert at Kaspersky Lab
Sergey has been working with SQL Server for more than a decade as a database administrator, developer, consultant, and trainer, focusing on high availability and disaster recovery solutions, ETL, developing high-performance solutions, troubleshooting and performance tuning. He is an owner of prestigious Microsoft Certified Master certification on SQL Server and MVP Data Platform award and regularly speaks at various SQL Server user group meetings, SQL Saturday events and other conferences worldwide.
Sergey Olontsev