{"id":14671,"date":"2016-01-06T09:30:00","date_gmt":"2016-01-06T17:30:00","guid":{"rendered":"https:\/\/blogs.technet.microsoft.com\/dataplatforminsider\/2016\/01\/06\https://www.microsoft.com/json-in-sql-server-2016-part-2-of-4\/"},"modified":"2024-01-22T22:52:29","modified_gmt":"2024-01-23T06:52:29","slug":"json-in-sql-server-2016-part-2-of-4","status":"publish","type":"post","link":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/2016\/01\/06\https://www.microsoft.com/json-in-sql-server-2016-part-2-of-4\/","title":{"rendered":"JSON in SQL Server 2016: Part 2 of 4"},"content":{"rendered":"

Storing JSON Data in SQL Server<\/h1>\n

Since external systems format information as JSON text, JSON is also stored in SQL Server as text. You can use standard NVARCHAR columns to store JSON data. A simple table where some information stored as JSON is shown in the following example:<\/p>\n

CREATE TABLE<\/span> Person (<\/span><\/span>
\u00a0Id int IDENTITY PRIMARY KEY NONCLUSTERED<\/span>,<\/span><\/span>
\u00a0FirstName nvarchar<\/span>(<\/span>100)<\/span> NOT NULL,<\/span> <\/span>
\u00a0LastName nvarchar<\/span>(<\/span>100)<\/span> NOT NULL,<\/span><\/span><\/p>\n

\u00a0InfoJson nvarchar<\/span>(<\/span>max<\/span>)<\/span><\/span>
\u00a0<\/span>
)<\/span> WITH<\/span> (<\/span>MEMORY_OPTIMIZED<\/span>=<\/span>ON<\/span>)<\/span><\/span><\/p>\n

Here you can see the main difference between traditional relational-only and document-only systems and the hybrid model that SQL Server 2016 provides. In SQL Server 2016, you can combine both scalar columns (FirstName and LastName in this example) and columns that contain JSON (InfoJSON in this example).<\/p>\n

In SQL Server, you can organize your data as one or many scalar or JSON columns and combine them with Spatial or XML columns if needed. In the same table, you can combine standard relational columns that enable faster access and JSON columns that provide flexibility and rapid application development. Unlike relational-only or document-only storage where you need to choose between two principles of data modeling, SQL Server offers you a hybrid data storage model where you can use the best of both data modeling methods.<\/p>\n

Although JSON is stored in a text column, it is not just \u201ca plain text.” SQL Server has built-in support for optimizing storage of text columns using various compression mechanisms such as UNICODE compression<\/a> that can provide up to a 50% compression ratio. You can also store JSON text in column store tables or compress it explicitly using the built-in COMPRESS<\/a> function that uses the GZip algorithm.<\/p>\n

JSON is fully compatible with any SQL Server component or technology that works with NVARCHAR data. In the example above, JSON is stored in an In-memory OLTP (Hekaton) table that provides extreme processing performance. You can store JSON in standard tables, column store indexes, or in FILESTREAM. You can also load it from Hadoop using Polybase external tables, read it from file systems, stretch it to Azure SQL, use any replication method, and more. If you combine tables that store JSON documents with other SQL Server features, such as Temporal or Row-Level Security, you might find some powerful features that are not available in the existing document databases.<\/p>\n

If you don\u2019t want to keep JSON as a free text format, you can add a validation that verifies that JSON in the text column is properly formatted using standard CHECK constraints and ISJSON function:<\/p>\n

ALTER TABLE<\/span> Person<\/span>
ADD CONSTRAINT<\/span> [Content should be formatted as JSON]<\/span>
\u00a0CHECK<\/span> (<\/span> ISJSON<\/span>(<\/span> InfoJSON )><\/span> 0 )<\/span><\/span><\/p>\n

This is a standard SQL Server check constraint that enables you to validate whether the text stored in the JSON column is properly formatted. This constraint is optional \u2013 you can leave a plain text column as in the previous example; however, your queries might fail at runtime if your JSON text in a row is not properly formatted or if you don\u2019t add the ISJSON condition in the WHERE clause to exclude invalid JSON columns.<\/p>\n

Since JSON is represented as text, you don\u2019t need to make any changes in your client applications, wait for new drivers, or change protocol. You can read or write JSON documents in your C#, Java, and Node.js applications as standard string values. JSON can be loaded in ORM models as string fields and be directly sent to JavaScript client-side code via Ajax requests. Any ETL tool can also load or read JSON because there is no new format or interface.<\/p>\n

Built-in functions for JSON processing<\/h1>\n

SQL Server 2016 provides functions for parsing and processing JSON text. JSON built-in functions that are available in SQL Server 2016 are:<\/p>\n