Analytical queries that scan huge amounts of data are always problematic in relational databases. Nonclustered balanced tree indexes are efficient for transactional query seeks; however, they rarely help with analytical queries. A great idea occurred nearly 30 years ago: why do we need to store data physically in the same way we work with it logically, row by row? Why don't we store it column by column and transform columns back into rows when we interact with the data? Microsoft played with this idea for a long time and finally implemented it in SQL Server.
Columnar storage was first added to SQL Server in the 2012 version. It included nonclustered columnstore indexes (NCCI) only. Clustered columnstore indexes (CCIs) were added in the 2014 version. In this chapter, readers can revise columnar storage and then explore huge improvements for columnstore indexes in SQL Server 2016 and 2017—updatable nonclustered columnstore indexes, columnstore indexes on in-memory tables, and many other new features for operational analytics.
In the first section, you will learn about SQL Server support for analytical queries without using columnar storage. The next section of this chapter jumps directly to columnar storage and explains the internals, with the main focus on columnar storage compression. In addition, batch execution mode is introduced.
Then it is time to show you columnar storage in action. The chapter starts with a section that introduces nonclustered columnstore indexes. The demo code shows the compression you can get with this storage and also how to create a filtered nonclustered columnstore index.
Clustered columnstore indexes can have even better compression. You will learn how these clustered and nonclustered columnstore indexes compress data and improve the performance of your queries. You can also combine columnar indexes with regular B-tree indexes. In addition, you will learn how to update data in a clustered columnstore index, especially how to insert new data efficiently. Finally, the chapter introduces method to use columnar indexes, together with regular B-tree indexes, to implement a solution for operational analytics.
This chapter will cover the following points:
- Data compression in SQL Server
- Indexing for analytical queries
- T-SQL support for analytical queries
- Columnar storage basics
- Nonclustered columnstore indexes
- Using clustered columnstore indexes
- Creating regular B-tree indexes for tables with CCI
- Discovering support for primary and foreign keys for tables with CCI
- Discovering additional performance improvements with batch mode
- Columnstore index query performance
- Columnstore for real-time operational analytics
- Data loading for columnstore indexes