Various researchers started to think about columnar storage in the 80s. The main idea is that a relational database management system (RDBMS) does not need to store the data in exactly the same way we understand it and work with it. In a relational model, a tuple represents an entity and is stored as a row of a table, which is an entity set. Traditionally, database management systems store entities row by row. However, as long as we get rows back to the client application, we do not care how an RDBMS stores the data.

This is actually one of the main premises of the relational model—we work with data on the logical level, which is independent of the physical level of the physical storage. However, it was not until approximately 2000 when the first attempts to create columnar storage came to life. SQL Server added columnar storage first in version 2012.

Columnar storage is highly compressed. Higher compression means more CPU usage because the data must be decompressed when you want to work with it and recompressed when you store it. In addition, SQL Server has to transform columns back to rows when you work with data and vice versa when you store the data. Add to this picture parallelized query execution, and suddenly CPU becomes a bottleneck. CPU is rarely a bottleneck in a transactional application. However, analytical applications have different requests. SQL Server solves the CPU problem by introducing batch processing.

In this section, you will learn about SQL Server columnar storage and batch processing, including the following:

• How SQL Server creates columnar storage
• Columnstore compression
• Nonclustered columnstore indexes
• Clustered columnstore indexes
• The limitations of columnar storage in different versions of SQL Server
• Batch processing
• The limitations of batch processing in different versions of SQL Server