how columnstored index are compressed

Question

Are there any documents talks about how columnstored index are compressed and stored ?
I found there are compression method for ROW and PAGE.......from microsoft .

Answer 1

There is plenty of documentation out there about columnstore. The very brief description is that since values are stored column-wise and not row-wise, this permits for a very good compression rate. If there are 1000 rows where FirstName = 'John', why store John 1000 times?

If you want to learn about columnstore, Data Platform MVP Nico Neugebauer has many blog posts on the topic: http://www.nikoport.com/columnstore/

Google can surely give you more information as well.

Answer 2

HI @sakuraime ,

There are two data compression types : Columnstore and Columnstore Archive Compression.

By storing data grouped by columns, like values can be grouped together and therefore compress very effectively. Columnstore compression will often reduce the size of a table by 10x and offers significant improvements over standard SQL Server compression.

Columnstore Archive Compression shrinks the data footprint of a columnstore index further but incurs an additional CPU/duration cost to read the data.

Please also refer below links for more details:
Hands-On with Columnstore Indexes: Part 1 Architecture
Hands-On with Columnstore Indexes: Part 2 Best Practices and Guidelines

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Hot issues October--Users always get connection timeout problem when using multi subnet AG via listener. Especially after failover to another subnet