Qs. In HANA which type of tables should be preferred - Row-based or Column-based?
SQL queries involving aggregation functions take a lot of time on huge amounts of data because every single row is touched to collect the data for the query response.
In columnar tables, this information is stored physically next to each other, significantly increasing the speed of certain data queries. Data is also compressed, enabling shorter loading times.
To enable fast on-the-fly aggregations, ad-hoc reporting, and to benefit from compression mechanisms it is recommended that transaction data is stored in a column-based table.
Few more important points about column table:
The SAP HANA data-base allows joining row-based tables with column-based tables. However, it is more efficient to join tables that are located in the same row or column store. For example, master data that is frequently joined with transaction data should also be stored in column-based tables.
1. HANA modeling views are only possible for column tables. Row based tables cannot be used in modeling views.
2. For that reason Replication Server creates SAP HANA tables in column store by default.
3. Data Services also creates target tables in column store as default for SAP HANA database
4. The SQL command to create column table: “CREATE COLUMN TABLE Table_Name..”.
5. The data storage type of a table can be modified from Row to Column storage with the SQL command “ALTER TABLE Table_Name COLUMN“.