Columnstore Index: Parallel load into clustered columnstore index from staging table

SQL Server has supported parallel data load into a table using BCP, Bulk Insert and SSIS. The picture below shows a typical configuration of a Data Warehouse where data is loaded from external files either using BCP or SSIS. SQL Server supports parallel data load. Another common scenario of loading data is via a staging… Read more

Columnstore Index: Differences between Clustered/Nonclustered Columnstore Index

SQL Server 2016 provides two flavors of columnstore index; clustered (CCI) and nonclustered (NCCI) columnstore index. As shown in the simplified picture below, both indexes are organized as columns but NCCI is created on an existing rowstore table as shown on the right side in the picture below while a table with CCI does not… Read more

Columnstore Index: Differences between Columnstore Index vs BTree index

In earlier blog why columnstore index, we had discussed what is a columnstore index and why do we need it. The columnstore storage model in SQL Server 2016 comes in two flavors; Clustered Columnstore Index (CCI) and Nonclustered Columnstore Index (NCCI) but these indexes are actually quite different than the traditional btree indexes. Here are… Read more

Increased Memory Size for In-Memory OLTP in SQL Server 2016

We are happy to announce that SQL Server 2016 removes the size limitation on user data in memory-optimized tables. You can grow your memory-optimized tables as large as you like as long as you have enough available memory. This means that with Windows Server 2016 you can leverage all 12TB (Terabytes) of available memory in… Read more

IoT Smart Grid code sample

This code sample simulates an IoT Smart Grid scenario where multiple IoT power meters are constantly sending measurements to a SQL Server 2016 in-memory database. The sample is leveraging the following features: Memory Optimized Tables, Table valued Parameters (TVPs), Natively Compiled Stored Procedures, System-Versioned Temporal Tables (for building version history), Clustered Columnstore Index, Power BI… Read more

Announcing availability of SQL Server 2014 Express Docker image

We are excited to announce the public availability of the sql server 2014 express Docker image for Windows Server Core based Containers! The public repo is hosted on Docker Hub and contains the latest docker image as well as pointers to the Dockerfile and the start PS script (hosted on Github). We hope you will find this… 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

Encapsulate JSON parsing with inline table value functions

If you are storing JSON fields in SQL Server 2016 or Azure SQL Database, you would probably need to use OPENJSON to parse JSON and extract fields. As an example, in new SQL Server 2016 WideWorldImporters sample database, we have Application.People table with several standard columns and one JSON column that contains custom fields. If you want… Read more