Friday, November 18, 2016

SQL Server Columnstore Index

Column-oriented DBMS - Wikipedia

Columnstore Indexes Guide
(Available since SQL Server 2012)

"The columnstore index is the standard for storing and querying large data warehousing fact tables. It uses column-based data storage and query processing to achieve up to 10x query performance gains in your data warehouse over traditional row-oriented storage, and up to 10x data compression over the uncompressed data size. Beginning with SQL Server 2016, columnstore indexes enable operational analytics, the ability to run performant real-time analytics on a transactional workload."
A columnstore is data that is logically organized as a table with rows and columns, and physically stored in a column-wise data format.

A rowstore is data that is logically organized as a table with rows and columns, and then physically stored in a row-wise data format. This has been the traditional way to store relational table data. In SQL Server, rowstore refers to table where the underlying data storage format is a heap, a clustered index, or a memory-optimized table.
For high performance and high compression rates, the columnstore index slices the table into groups of rows, called rowgroups, and then compresses each rowgroup in a column-wise manner. The number of rows in the rowgroup must be large enough to improve compression rates, and small enough to benefit from in-memory operations.

Column segmentClustered Columnstore Index

"ID Last First Bonus
1 Doe John 8000
2 Smith Jane 4000
3 Beck Sam 1000

row-oriented database: 1,Doe,John,8000; 2,Smith,Jane,4000; 3,Beck,Sam,1000;
column-oriented database: 1,2,3; Doe,Smith,Beck; John,Jane,Sam; 8000,4000,1000;"

OpenStax: free textbooks online

Subjects - OpenStax
"Open source. Peer-reviewed. 100% free.
And backed by additional learning resources. Review our OpenStax textbooks and decide if they are right for your course. Simple to adopt, free to use. We make it easy to improve student access to higher education."

OpenStax logo