SAP HANA row and column store table

SAP HANA Modeler Views can only be created on the top of Column based tables. Storing data in Column tables is not a new thing. Earlier it was assumed that storing data in Columnar based structure takes more memory size and not performance Optimized.

With the evolution of SAP HANA, HANA used column-based data storage in Information views and presented the real benefits of columnar tables over Row based tables.

Column Store

In a Column store table, Data is stored vertically. So, similar data types come together as shown in the example above. It provides faster memory read and writes operations with help of In-Memory Computing Engine.

In a conventional database, data is stored in Row based structure i.e. horizontally. SAP HANA stores data in both row and Column based structure. This provides Performance optimization, flexibility and data compression in HANA database.

Storing Data in Columnar based table has following benefits −

•  Data Compression
• Faster read and write access to tables as compared to conventional Row based storage
• Flexibility & parallel processing
• Perform Aggregations and Calculations at higher speed

Functional Difference - Row vs Column Store

It is always advisable to use Column based storage if SQL statement has to perform aggregate functions and calculations. Column-based tables always perform better when running aggregate functions like Sum, Count, Max, Min.

Row-based storage is preferred when output has to return complete row. The example given below makes it easy to understand.

While running an Aggregate function (Sum) in sales column with Where clause, it will only use Date and Sales column while running SQL query so if it is column based storage table then it will be performance optimized, faster as data is required only from two columns.

While running a simple Select query, the full row has to be printed in output so it is advisable to store the table as Row based on this scenario.