To store a table in memory, two option exists: 1. Row based storage and 2. Column based storage.
In row based storage, A table is stored as a sequence of records, i.e. one full row in a data page/or consecutive data pages. It means all columns values of a table stored sequentially per row.
In column based storage, column values of a column are stoger in contiguous memory location.
Advantages of column based table storage in following circumstances
1. Calculations are typically executed on a single column or few columns only.
2. Table is searched based on values of a few columns.
3. Table has large number of columns.
4. Table has large number of rows, so that columnar operations are required (aggregate, scan etc)
5. High compression rates can be achieved because the majority of the columns contain only few distinct values (compared to the number of rows)
Advantages of row based storage in following circumstances
1. The application needs to only process a single record at one time. (This applies to many selects and or updates of single record)
2. The application typically needs to access a complete record (or row)
3. The columns contain primarily distinct values so that compression rate would be low.
4. Neither aggregations nor fast searching is required.
5. The table has small number of rows (e.g. configuration tables)