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...