From my work there are 2 key advantages that using a column oriented approach bring:
1. There is far less storage required to represent data in a column oriented database due to the ability to compress like data. This storage benefit is really important when you get to tables, as it means you can hold more of your data set in memory at any one time.
2. For tables that have a lot of columns, but queries that only touch a few at a time, it means less work for the query to do, since there is no jumping from row pointed to column pointer.
For case 2, a good index in a normal RDBMS also gives the same benefit. If your query only touched the columns in the index, the query would be blasting fast. The full rows are not used at all. One trick to improve query performance is to create composite index that contains all the columns needed in the query, which is like a mini-table with only the columns in the query.
Yes but each additional index adds to the storage requirement of the data. With column oriented designs each column is often indexed with bitmap indexes which are very small (often the index + raw data is smaller than the raw data stored in an array thanks to compression). Index intersection is also much faster with bitmap indexes rather then b-trees.
One trick to improve query performance is to create composite index that contains all the columns needed in the query, which is like a mini-table with only the columns in the query.
MSSQL also allows you to create an index and include other columns with the index data while leaving them out of the index itself. I don't have time to check if this is standard SQL or an MSSQL extension.
From my work there are 2 key advantages that using a column oriented approach bring:
1. There is far less storage required to represent data in a column oriented database due to the ability to compress like data. This storage benefit is really important when you get to tables, as it means you can hold more of your data set in memory at any one time.
2. For tables that have a lot of columns, but queries that only touch a few at a time, it means less work for the query to do, since there is no jumping from row pointed to column pointer.
Cheers