SAP HANA Admin - Table Management
SAP HANA supports all the database functions that can be performed in a conventional database. You can create database tables, views, triggers, synonyms, stored procedures, and other database functions. In SAP HANA, you can create two types of tables −
SAP HANA column store tables are suitable to perform performance optimized read operations and also to perform write operations. You can achieve a data compression up to 11 times, and searches and calculations can be performed much faster as compared to column store tables. Data Partitioning feature of SAP HANA is only available to column store tables and SAP HANA Data modeling can be used only on column store tables.
Row store tables are more suitable for performing INSERT and UPDATE SQL statements on small size tables.
In SAP HANA database, it is possible to join different store table types - Row store table can be joined to a column store table but it is recommended to join similar table types together to keep the performance high.
Creating a Table in HANA Database Using GUI Option in HANA Studio
Right-click on Table tab under Schema → Select ‘New Table’ option as shown in the following screenshot.
Once you click on New Table, it will open a window to enter the Table name. Choose Schema name from the dropdown → Define Table type from the dropdown list: Column Store or Row Store.
Define data type as shown in the following screenshot, columns can be added by clicking the (+) sign. Primary Key can be chosen by clicking the cell under Primary key in front of Column name. Not Null will be active by default. Once columns are added, click Execute.
In the following screenshot, you can see the Table Type as Column Store. You have an option to create a Row store table by selecting Row Store from the dropdown list.
While using SQL statement to create a table, you need to mention “Column” keyword in Create Table command. By default, it creates a Row store table using SQL Editor.
Once you Execute (F8), right-click Table Tab → Refresh. The new table will be reflected in the list of tables under the chosen Schema.
Note − You can also change the table type in SAP HANA database using the following Alter command in SQL Editor.
Alter table_name Column;
Benefits of Using Column Store Tables
Following are the advantages of using Column store tables as compared to Row store −
Performance Optimized Column Operations
You can perform complex calculations and aggregations much faster as compared to Row Store tables. This removes the need to store aggregated tables in HANA database and hence also saves the memory space.
Built-In Indexes for Column Tables
Using columnar data structure for tables removes the need of Indexes as when you store data in columns it works like a built-in index for each column. This saves the memory space and also improves the performance during write operations.
Data Compression
When you store column based tables in HANA database, similar data types are stored continuous in memory. It allows you to apply various data compression techniques such as - Run Length Compressed, Dictionary Compressed, hence reducing the space required to store the tables. You can achieve a data compression as high as 11 times as compared to conventional database.
Parallel Processing
With the use of multi core processors, you can perform parallel processing on column store tables. As data is stored vertically, column operations can easily be processed on column based tables.
