Getting Started with Columnstore Index in SQL Server 2014

Column Store Index, which improves performance of data warehouse queries several folds, was first introduced in SQL Server 2012. Though it had several limitations, now SQL Server 2014 enhances the columnstore index and overcomes several of those earlier limitations. In this article series I am going to talk about how you can get started with using this enhanced columnstore index feature in SQL Server 2014 and do some performance tests to understand the benefits.

If you are new to columnstore index, you can refer my earlier articles on the topic to understand it better.

• Understanding New Columnstore Index of SQL Server 2012
• Getting Started with the New Columnstore Index of SQL Server 2012
• New Enhanced Columnstore Index in SQL Server 2014

For more information click here for part 1 and click here for part 2.

New Enhanced Column Store Index in SQL Server 2014

Column Store Index, which improves performance of data warehouse queries several folds, was first introduced in SQL Server 2012. Unlike regular indexes or heaps, which store data in B-Tree structure (in row-wise fashion), the column store index stores data in columnar fashion and uses compression aggressively to reduce the disk I/O needed to serve the query request. Apart from the benefits it provides, it has several limitations in SQL Server 2012. SQL Server 2014 enhances the columnstore index and overcomes several of the earlier limitations. In this article, I am going to talk about the new enhanced columnstore index feature in SQL Server 2014. For more information click here.

SQL Server Data Tools in SQL Server 2012

As developers, we have always wanted to have a single Integrated Development Environment (IDE) for not only writing application code but to also to write database code in a single place without switching the environments. SQL Server Data Tool (SSDT) is a new feature in SQL Server 2012 that provides this capability primarily intended for developers and lets developers write their application as well as database code in a single development environment of Visual Studio. For more information, click here for part 1 and click here for part 2.

 

Managing Slowly Changing Dimension with MERGE Statement in SQL Server

In my last article I talked about Slowly Changing Dimension (SCD) Transformation, which is available out of box in the SSIS toolkit and can be used easily and configured quickly for managing smaller slowly changing dimension. The Slowly Changing Dimension Transformation is good if you want to get started easily and quickly but it has several limitations (I talked about these limitations in my last article, Managing Slowly Changing Dimension with Slow Changing Transformation in SSIS) and does not perform well when the number of rows or columns gets larger and larger.

In this article I am going to talk about some of the alternatives that you can take for managing larger slowly changing dimensions. For more information click here.

Managing Slowly Changing Dimension with Slow Changing Transformation in SSIS

As a data warehouse expert or as an ETL developer you will often come across scenarios in which you need to maintain and manage slowly changing dimensions. There are multiple ways to implement that in SQL Server and the easiest of those is using Slowly Changing Dimension Transformation in the data flow task of SSIS packages.

In this article I am going to provide you the steps and guidance needed to manage Slowly Changing Dimension with Slowly Changing Dimension Transformation in data flow task with an example. For more information click here.

Partitioning in SQL Server: Managing Sliding Window Scenario

Earlier articles of this series discussed what partitioning in SQL Server is; the different kinds of partitioning options, why and when we should go for partitioning and the benefits a partitioned table/index provides. Then I talked about different partitioning concepts like partition function, partition scheme, guidelines on choosing a partitioning column and creating a partition on table or index. We also learned about partitioned index and how an index is aligned and storage aligned with the base table partitioning, with an example.

This article provides you the steps and guidance needed to manage a sliding window scenario, with an example. For more information, click here.

Partitioning Using the Wizard in SQL Server

In my earlier articles of the series, I discussed what partitioning in SQL Server is; the different kinds of partitioning options that we have, why and when we should go for partitioning and what benefits partition table/index provides. Then I talked about different partitioning concepts, such as partition function, partition scheme, guideline on choosing partitioning column and creating partition on a table or index. We also learned about partitioned index and how an index is aligned and storage aligned with the base table partitioning.

In my last article of this series, I provided a step-by-step guide on creating a partition table/index using T-SQL commands. In this article I am going to provide you a step-by-step guide on creating partition table/index using the Wizards in SQL Server Management Studio (SSMS) and talk about Partition Aligned Index Views. For more details, click here.