Advanced

SQL Server 2012: Nonclustered Columnstore Indexes

By Joe Sack
Learn how nonclustered columnstore indexes in SQL Server 2012 can introduce incredible performance benefits when querying very large data sets in relational data warehouses, applicable to developers and DBAs responsible for application data tiers, from SQL Server 2012 onwards
Course info
Rating
(211)
Level
Intermediate
Updated
May 8, 2013
Duration
1h 16m
Table of contents
Course Introduction
Course Introduction 1m Course Structure 1m
Nonclustered Columnstore Index Fundamentals
Module Introduction 1m Demo: Expanding the AdventureWorksDW2012 Database 4m Nonclustered Columnstore Indexes Defined 1m Demo: Nonclustered Columnstore Indexes - Hello World 5m Targeted Workloads and Environments 2m Non-ideal Candidates 2m B-Tree Row Storage 0m Just Accessing a Few Columns? It Doesn't Matter... 0m Columnar Data Storage 0m Segments 0m Demo: Segments 5m Row Groups 1m Syntax (1) 1m Syntax (2) 1m Demo: Syntax 2m This is NOT a B-Tree Index 2m Supported and Unsupported Data Types 1m How Performance is Achieved 2m Dictionary Encoding 1m Batch Execution Mode 1m
Leveraging Batch-Execution Mode
Module Introduction 1m Traditional Row Execution Mode 1m Batch Execution Mode 1m Batch Mode Operator Support 1m Demo: Verifying Batch Execution Mode 3m Batch Execution Inibitors (1) 1m Demo: Serial Plan Batch Execution Inhibitor 2m Batch Execution Inhibitors (2) 1m Demo: NOT IN Batch Execution Inhibitor 3m Demo: Scalar Aggregate Batch Execution Inhibitor 2m
Benefiting from Segment Elimination
Module Introduction 1m Segment Elimination (1) 1m Segment Elimination (2) and (3) 1m Demo: Segment Elimination 4m
Managing Data Modifications
Module Introduction 1m Restricted Data Modifications 0m Demo: Restricted Data Modifications 1m Delta Table 2m Demo: Delta Table 2m Drop, Load, Create 1m Partition Switching 1m Demo: Partition Switching 3m
Deployment Planning
Module Introduction 1m Interoperability Limitations 1m Index Creation Memory Requirements (1) 1m Index Creation Memory Requirements (2) 1m Demo: Index Creation Memory Requirements 3m Deployment Considerations (1) 2m Deployment Considerations (2) 1m vNext Clustered Columnstore Indexes 1m Course Summary 1m
Description
Course info
Rating
(211)
Level
Intermediate
Updated
May 8, 2013
Duration
1h 16m
Description

If you need to develop a solution to meet your business intelligence needs but don't have the in-house SQL Server Analysis Services skills to make it happen, don't rule out a SQL Server database engine solution just yet. SQL Server 2012 nonclustered columnstore indexes provide a compelling new option for extremely fast querying of very large data sets found in relational data warehouses. This course will cover the benefits of this new functionality and will also clearly articulate the current limitations and planning considerations along the way. By the end of this course, you'll be equipped with enough information to help decide if nonclustered columnstore indexes will be a viable choice for your application data tier. This course is perfect for developers, DBAs, and anyone responsible for SQL Server performance. The information in the course applies to SQL Server 2012 onwards.

About the author
Joe Sack
About the author

Joe Sack is a Principal Program Manager in the SQL Server and Azure SQL Database product team at Microsoft, with a focus on query processing. With over 19 years of experience in the industry, Joe is an author and speaker, specializing in performance tuning and optimization.

More from the author
SQL Server: Common Query Tuning Problems and Solutions - Part 2
Intermediate
2h 17m
Aug 11, 2014
SQL Server: Common Query Tuning Problems and Solutions - Part 1
Intermediate
2h 30m
Jul 7, 2014
SQL Server: Transact-SQL Common Table Expressions
Intermediate
1h 20m
Feb 14, 2014
More courses by Joe Sack

You're the smartest person in the room

PROVE IT

Your team won't just close the skills gap

THEY'LL HURDLE IT