SQL Server 2016 Express Edition in Windows containers

We are excited to announce the public availability of SQL Server 2016 Express Edition in Windows Containers! The image is now available on Docker Hub and the build scripts are hosted on our SQL Server Samples GitHub repository. This image can be used in both Windows Server Containers as well as Hyper-V Containers. SQL Server… Read more

Columnstore Index- Should I partition my columnstore Index?

Table partitioning is a perfect way to manage large tables especially in the context DataWarehouse (DW) which can be very large (think TBs). The table partitioning can help both in managing large amount of dara as well as improving the query performance by eliminating partitions that are not required.  For example, if a FACT table stores SALES… Read more

Columnstore Index: In-Memory Analytics (i.e. columnstore index) Videos from Ignite 2016

I presented two talks on columnstore index during Microsoft Ignite Conference 2016 in Atlanta, GA. The talks focused on describing new enhancements to columntore index in SQL Server 2016 as well as sharing three customer success stories. Here is a high level overview of each session and the video recording of the session. Review ColumnStore… Read more

In-Memory OLTP Videos: What it is and When/How to use it

In-Memory OLTP is the premier technology for optimizing the performance of transaction processing in SQL Server. Last week at Microsoft Ignite 2016 we presented two session about the In-Memory OLTP technology in SQL Server and Azure SQL Database. For those of you who did not attend the conference or did not make it to the… Read more

SQL Server 2016 – It Just Runs Faster: Always On Availability Groups Turbocharged

When we released Always On Availability Groups in SQL Server 2012 as a new and powerful way to achieve high availability, hardware environments included NUMA machines with low-end multi-core processors and SATA and SAN drives for storage (some SSDs). Performance issues surrounding Availability Groups typically were related to disk I/O or network speeds. As we… 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

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