SQL Server Columnstore Index question concept

Question

Hello, trying to understand the concept of Columnstore Index vs typical standard regular index and came across this interesting post:

https://stackoverflow.com/questions/39828638/what-is-a-columnstore-index-and-how-is-different-from-clustered-and-non-clustere

To simplify the concept (realize there's further technical portions involved) could Columnstore Index be viewed as transposing the typical standard index the way they store entire row on per page...instead of horizontal (typical standard) they are stored vertically (Columnstore) like the article mentions it then it would only need to read 1 page only rather than multiple rows in their example?

Thanks in advance.

Answer 1

In a columnstore data is stored columnwise, rather than rowwise. It is not really an index in the sense that it is sorted. To find a specific row where City = 'Horbart', you would need to scan all rows, but only for that column. And since all occurrences of Horbart can be stored in once place, there are fantastic options for compression.

The article you linked to, see to have many good links, and if you google, you can find even more. It's not clear what you expect us to explain here, that you cannot find on the internet already.

Answer 2

Hi @techresearch7777777

Referring to this doc: Columnstore indexes: Overview

Rowstore indexes perform best on queries that seek into the data, when searching for a particular value, or for queries on a small range of values. Use rowstore indexes with transactional workloads because they tend to require mostly table seeks instead of table scans.

Columnstore indexes give high performance gains for analytic queries that scan large amounts of data, especially on large tables. Use columnstore indexes on data warehousing and analytics workloads, especially on fact tables, because they tend to require full table scans rather than table seeks.

Also, you can combine rowstore and columnstore on the same table. Beginning with SQL Server 2016 (13.x), you can have one or more nonclustered rowstore indexes on a columnstore index and perform efficient table seeks on the underlying columnstore.

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

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.