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.
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;"
column-oriented database: 1,2,3; Doe,Smith,Beck; John,Jane,Sam; 8000,4000,1000;"
No comments:
Post a Comment