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

JSON data in clustered column store indexes

Clustered column store indexes (CCI) in SQL Server vNext and Azure SQL Database support LOB types like NVARCHAR(MAX), which allows you to store string with any size, including JSON documents with any size. With CCI you can get 3x compression and query speedup compared to regular tables without any application or query rewrites. In this… Read more

Implementing Product Catalogs in SQL Server and Azure SQL database

Product catalog is one of the key scenarios in NoSQL systems. In product catalog scenario, you need to store different types of products with different properties (e.g. phones have memory and CPU power; cars have number of doors and max speed, etc.) If you try to model this in relational database you will end-up either with… Read more

JSON is Generally available in Azure Sql Database!

JSON functionalities are now generally available in Azure Sql Database! All functions that are available in SQL Server 2016 are also available in Azure Sql Database. Azure Sql Database enables you to get values from JSON documents using JSON_VALUE function, modify values in JSON text using JSON_MODIFY function, transform JSON to table using OPENJSON function… Read more

IoT code sample – loading messages from Event Hub into Azure SQL Database

Paolo Salvatori created an example that simulates an Internet of Things (IoT) scenario where thousands of devices send events (e.g. sensor readings) to a backend system via a message broker. The backend system retrieves events from the messaging infrastructure and store them to a persistent repository in a scalable manner. Solution has the following components: Event… Read more

Appending JSON arrays using JSON_MODIFY function

Sql Server 2016 and Azure Sql Database enables you to easily modify JSON object and arrays. JSON_MODIFY updates the value of a property in a JSON string and returns the updated JSON string. Here I will show how to append objects in JSON array. In new SQL Server 2016 WideWorldImporters sample database you can find ReturnedDeliveryData  JSON… Read more

Handling inheritance with JSON

JSON in SQL Server 2016 and Azure SQL Database enables you to handle custom fields and inheritance. As an example, imagine People/Employee/Salespeople structure where Employee is a kind of Person, and Sales person is a kind of Employee. This is a standard inheritance structure of entities. In earlier versions of SQL Server, you had several options… Read more