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

Query Optimizer Additions in SQL Server 2016

In SQL Server 2016 we have introduced a number of new Query Optimizer improvements. This article summarizes some them and explains you can leverage the benefits of the new enhancements. Expect deep dive follow up articles for some of the enhancements. Here is the short list: Compatibility Level Guarantees Query Optimizer Improvements under Trace Flag… Read more

Increased nonclustered index key size with SQL Server 2016

SQL Server 2016 and Azure SQL Database have increased the maximum size for index keys with nonclustered indexes. The new maximum key size for nonclustered indexes is 1700 bytes. The maximum key size for clustered indexes remains 900 bytes. Consider, for example, the following script which creates a table with a 1700 byte variable-length character column,… Read more

JSON is available in Azure SQL Database

JSON functionalities that are added in SQL Server 2016 are also available in Azure SQL Database see Public preview: JSON in Azure SQL Database. All functionalities that you can use in SQL Server 2016 RC (i.e. FOR JSON, OPENJSON, built-in functions) are also available in Azure SQL Database, including JSON_MODIFY function that is added in RC… Read more

A Technical Case Study: High Speed IoT Data Ingestion Using In-Memory OLTP in Azure

In this post we look at a customer case study of an Internet of Things (IoT) scenario, where large amount of device data is ingested into an Azure SQL Database. Because the data lives in a SQL database, it can be conveniently accessed and analyzed through SQL queries. In-Memory OLTP was used to achieve significant… Read more

Improving temp table and table variable performance using memory optimization

In-Memory OLTP is a technology introduced in SQL Server 2014 that can provide phenomenal (up to 30-fold) performance improvements for transactional workloads, and SQL Server 2016 improves the performance even further. However, adopting this technology in an existing app comes at a cost, and you need to have enough available memory for the core transactional… Read more

Returning child rows formatted as JSON in SQL Server queries

In this post I will talk about one annoying thing – how to return a result set containing one to many relationships between tables (i.e. parent’child relationships such as company-products, person-address, sales order-items, etc.)? Problem I have relational structure with several one to many relationships (e.g. Person may have several phones, or several email addresses)…. Read more

Querying JSON documents in Sql Server 2016 and DocumentDB

SQL Server JSON query syntax compared to DocumentDB In this article I will show you similarities and differences between SQl Server JSON and DocumentDB query syntax. SQL Server 2016/Azure SQL Db are relational databases that will have support for handling JSON data. JSON support in SQL Server uses standard T-SQL syntax for querying JSON data… Read more

Loading non-relational data formatted as JSON text

Introduction Data load is one of the most important processes in databases. However, relational databases are not optimal for loading complex relational data. JSON can be used to improve performance and reduce complexity in data load process if you serialize some entities as JSON collections. In this post we will see how you can use… Read more