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

ASP.NET Session State with SQL Server In-Memory OLTP

ASP.NET session state enables you to store and retrieve values for a user as the user navigates the different ASP.NET pages that make up a Web application. Currently, ASP.NET ships with three session state providers that provide the interface between Microsoft ASP.NET’s session state module and session state data sources: InProcSessionStateStore, which stores session state… Read more

In-Memory OLTP Indexes – Part 2: Performance Troubleshooting Guide.

Introduction In the previous blog post In-Memory OLTP Indexes – Part 1: Recommendations, we gave you an update on the latest features of In-Memory OLTP technology. We also summarized the key characteristics of memory-optimized indexes and shared some guidelines and recommendations on how to best choose and configure an index for your memory-optimized table. At… Read more

Azure SQL Database: Ingesting 1.4 million sustained rows per second with In-Memory OLTP & Columnstore Index

As Internet of Things (IoT) devices and sensors are becoming more ubiquitous in consumer, business and industrial landscapes, they introduce a unique challenge in terms of the volume of data they produce, and the velocity with which they produce it. The challenge is to ingest and analyze this data at the speed at which it… Read more

In-Memory OLTP Indexes – Part 1: Recommendations.

In-Memory OLTP Indexes In-Memory OLTP technology, available in Azure SQL Database and SQL Server, can significantly help you in improving the performance of applications that require high throughput and low latency online transaction processing, high volume concurrent data ingestion (Internet of Things – IoT), high volume data load and transformation (Extract Transform Load – ETL)… Read more

In-Memory OLTP Updated Overview and Case Studies

We just published updated slide decks about In-Memory OLTP. In-Memory OLTP overview: this is an overview of the technology and details what’s new in SQL Server 2016/2017 and Azure SQL Database In-Memory OLTP case studies: this discusses when you and do not want to use In-Memory OLTP, as well as a number of application patterns… 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