Injecting JSON objects into existing JSON text with JSON_MODIFY function

JSON_MODIFY function that is available in SQL Server 2016+ enables you to modify existing text formatted as JSON and add or update existing values. One common problem that happens is inserting unexpected value while trying to update existing JSON text and inject JSON object in the text. In this post I will show you one… Read more

Transitive closure clustering with CLR and JSON

Transitive closure is a graph algorithm that tries to follow paths in graph edges and tries to find all elements that can be reached from some element, or groups of elements that are mutually reachable. Although SQL Server still don’t provides native function for transitive closure, this algorithm can be implemented using CLR aggregates that can… Read more

Storing JSON documents in SQL Database

SQL Server and Azure SQL Database have native JSON functions that enable you to parse JSON documents using standard SQL language. Now you are able to store JSON documents in SQL Database and query JSON data like in any NoSQL database. In this post we will see what are the options for storing JSON documents in… Read more

Building SQL REST API back-end for Angular apps

JSON support in SQL Server/Azure SQL is designed to make integration between the relational database models and the modern single-page JavaScript apps easier than ever. SQL Server/Azure SQL enables you to easily format results of SQL queries as JSON text and return it to your JavaScript apps. In this post you will see how easily… Read more

JSON vs CLR UDT – performance comparison

Ability to create custom user defined types in CLR and use them in SQL Server is introduced in SQL Server 2005. In SQL Server 2016 is added JSON support that might be alternative for storing complex objects. Since JSON is stored as NVARCHAR and CLR is a type, it is questionable what would be better… Read more

JSON parsing is faster than Spatial equivalent

One of the most common assumption when people talk about JSON in SQL Server is that it is slow because it is placed in “plain text” and there is no native type. Instead of the assumption it is better to experiment and compare performance of JSON with some real native type. In this post, I… Read more

JSON parsing 10x faster than XML parsing

Usually, when people talk about the performance of JSON in SQL Server, they are starting with “JSON is not actually a native type, it is just a plain text” and imply that it is probably too slow. It is true that JSON is stored as NVARCHAR and that there is no special type like for… Read more

Use WITH clause in OPENJSON to improve parsing performance

OPENJSON function has a WITH clause where you can specify what fields should be extracted from input JSON. This might improve performance of your queries compared to the case where you use OPENJSON without schema and later extract information from the parsed JSON using JSON_VALUE function…. Read more

Extreme 25x compression of JSON data using CLUSTERED COLUMNSTORE INDEXES

CLUSTERED COLUMNSTORE INDEXES (CCI) provide extreme data compression. In Azure SQL Database and SQL Server vNext you can create CCI on tables with NVARCHAR(MAX) columns. Since JSON is stored as NVARCHAR type, now you can store huge volumes of JSON data in tables with CCI. In this post, I will show you how you can get… Read more