Reviewers: Joe Sack, Arvind Shyamsundar, Murshed Zaman, Sanjay Mishra The focus of this blog is to introduce SSIS changes in the Data Flow task in SQL Server 2016 that help data loading into Columnstore tables. On SQL Server relational data warehouses, Columnstore indexes are being widely used due to the immense value realized by… Read more
Reviewed by: James Rowland-Jones, John Hoang, Denzil Ribeiro, Sankar Subramanian This article explains how to assign monotonically increasing surrogate/synthetic keys to dimension tables in SQL DW (or APS) using T-SQL. We are going to highlight two possible methods of doing this: Assign Surrogate keys to the dimension tables where the dimensions are generated in a… Read more
Reviewers: Joe Sack, Denzil Ribeiro, Jos de Bruijn Many of us are very familiar with the negative performance implications of using scalar UDFs on columns in queries: my colleagues have posted about issues here and here. Using UDFs in this manner is an anti-pattern most of us frown upon, because of the row-by-agonizing-row (RBAR) processing… Read more
Authors: John Hoang, Joe Sack and Martin Lee Abstract This article provides an overview of the Microsoft Azure SQL Data Warehouse architecture. This new platform-as-a service (PaaS) offering provides independent compute and storage scaling on demand and is currently in public preview. This document provides data loading guidelines for SQL Data Warehouse. Several common loading… Read more
Are you coming to the PASS Summit 2015 in Seattle? SQLCAT will be in full force at the PASS Summit 2015. We will also bring along our colleagues from the broader AzureCAT. SQLCAT Sessions SQLCAT sessions are unique. We bring in real customer stories and present their deployments, architectures, challenges and lessoned learned. This year… Read more
A Columnstore index contains row groups that can that have a maximum of 1,048,576 rows. A row group can be closed and compressed due to multiple reasons before that maximum of 1,048,576 rows is reached. Ideally we want that row count in each row group to be as close to the maximum as… Read more
This topic explains the various Azure SQL Data Warehouse distributed table types, and offers guidance for choosing the type of distributed table to use and when. There are two types of distributed tables in Azure SQL DW at the writing of this article, hash distributed table and round-robin distributed table. Designing databases to use these… Read more
This article describes the index rebuild process as well as index maintenance for clustered Columnstore indexes and is directed towards SQL 2014. In SQL 2016, there have been several index maintenance enhancements that will be covered in a separate post. Overview of Columnstore index build or rebuild process Building (or rebuilding) a Columnstore index… Read more
If you haven’t been on a month-long holiday to a secluded island with no access to phone, email, or computer (and if you did, I truly envy you), then you must have heard that we announced SQL Server 2016 recently! And, now we’ve released a public beta (or preview) “SQL Server 2016 CTP2” last week…. Read more
The Microsoft Azure platform is evolving fast. Azure SQL Database, which is a Relational Database service running on Azure, is riding high on the cloud wave with new features enabled at a fast pace. I want to share a few Azure SQL Database security features currently in GA or public preview) that could help developers… Read more