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 −

Row Store
Column Store

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.

sap 62

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.

SAP HANA Admin – Table Partition

Using Partitioning, you can split column store tables into smaller tables in a multiple host system. You can even divide tables into smaller more manageable parts using partitioning. Partitioning can be done using DML SQL statements.

When a table is partitioned it contains different set of rows for each part and Partitioning can be done based on different algorithms – Hash Partitioning, Single Level or multilevel Partitioning.

Following are the advantages of using Table Partitioning −

Partitioning Pruning

You can determine if a query can be analyzed in a particular partition. Using this method, you can reduce the load on the system and hence improve the response time.

Example − When you partition a table based on the year, a query is executed to analyze the data for a particular year. You can use specific partition and hence query performance is improved.

Load Balancing

You can divide individual partitions on multiple hosts and hence a query is not processed by a single server, results in better load balancing on all the servers hosting table partitions.

Size Limitation

In a column store table, maximum number of rows that can be entered is around 2 billion. You can overcome this by distributing the rows of a column table on multiple partitions and hence the size limit is increased to 2 billion for each partition.

Improved Delta Merge Operation

During delta merge, if data is only modified for some partitions, you need to merge less partitions in delta merge.

Parallel Processing of Queries

Using partitions, you can run multiple queries in parallel and hence improve the response time.

Let us now discuss the types of partionining.

Single Level Partitioning

There are different types of single level partitioning available in SAP HANA system −

Hash Partitioning
Round Robin Partitioning
Range Partitioning

In Hash partitioning, rows are distributed equally to perform load balancing. You don’t require the detailed information about table content in this Partitioning type.

As compared to Hash Partitioning, in Round Robin Partitioning rows are equally distributed to each partition and new rows are equally assigned to each partition.

To create 4 partitions of a table using Round Robin, you can use the following SQL queries. No primary keys should be defined.

CREATE COLUMN TABLE TABLENAME (a INT, b INT, c INT)

PARTITION BY ROUNDROBIN PARTITIONS 4

This command will create 4 partitions of a table.

sap 63

CREATE COLUMN TABLE Table_Name (a INT, b INT, c INT, PRIMARY KEY (a,b))

PARTITION BY HASH (a, b) PARTITIONS 4

This will create 4 partitions on column a and b, and you need to specify at least one column.

Range Partitioning

In Range partitioning, you create dedicated partitions for certain values and you should have in-depth knowledge of table contents for partitioning columns.

Example − Creating one partition for each calendar month.

CREATE COLUMN TABLE TABLE_NAME (a INT, b INT, c INT, PRIMARY KEY (a,b))
PARTITION BY RANGE (a)
(PARTITION 1 <= VALUES < 10, PARTITION 10 <= VALUES < 20,
PARTITION VALUE = 50, PARTITION  OTHERS)

This will create 4 partitions, one for partition range 1 to 10, 2^nd from 10 to 20, 3^rd for value 50, and 4^th for all other values.

Multilevel Partitioning

In multilevel partitioning, you can overcome the limitation of HASH and RANGE single level partitioning to use key columns as partitioning columns. Using multilevel partitioning, you can partition a column that is not a primary key. The most common type of multilevel partitioning is HASH-Range Partitioning.

In Hash-Range multilevel partitioning, you implement Hash partitioning at the first level to implement load balancing and Range partitioning at the second level to implement timebased partitioning.

CREATE COLUMN TABLE Table_name (a INT, b INT, c INT, PRIMARY KEY (a,b))
PARTITION BY HASH (a, b) PARTITIONS 4,
RANGE (c) (PARTITION 1 <= VALUES < 10, PARTITION 10 <= VALUES < 20)

Instead of using Hash partitioning at the first level, you can also use Round Robin partitioning and it is known as Round Robin-Range multilevel partitioning.

CREATE COLUMN TABLE Table_name (a INT, b INT, c INT)
PARTITION BY ROUNDROBIN PARTITIONS 4,
RANGE (c) (PARTITION 1 <= VALUES < 10, PARTITION 10 <= VALUES < 20)

SAP HANA Admin – Table Replication

In SAP HANA system, it is also possible to replicate tables on multiple hosts. When you need to join the tables or partition tables on multiple hosts, table replication is useful to improve the performance, to reduce the load on the network in a distributed environment.

SAP HANA table replication has certain limitations −

You can’t replicate Partitioned Tables.
When you are using SAP BW on HANA, it doesn’t support Table replication.
When you perform table replication, it consumes the main memory and disk space to store persistence of each replica.
Column store tables with history tables and text columns without a primary key can’t be replicated.

Create Column Store Tables with Replica on All Hosts in Multiple Container System

CREATE COLUMN TABLE Table_Name (I INT PRIMARY KEY) REPLICA AT ALL LOCATIONS

This command will create a column store table with a replica on each host. You can also replicate an existing column base table on each available host using ALTER table command as follows −

ALTER TABLE Table_Name ADD REPLICA AT ALL LOCATIONS

sap 64

It is also possible to drop replica of an existing table using ALTER table drop replica command as follows.

ALTER TABLE Table_name DROP REPLICA AT ALL LOCATIONS

Note −

You can perform Table Replication on row store tables.
In a distributed environment, you can perform table replications on row store tables stored in master node.

In SAP HANA system, you can also perform consistency check on replicated tables using the following SQL command −

CALL CHECK_TABLE_CONSISTENCY(‘CHECK_REPLICATION’, ‘<schema>’, ‘<table’>)

So, this brings us to the end of blog. This Tecklearn ‘Table Management in SAP Hana’ blog helps you with commonly asked questions if you are looking out for a job in SAP Hana Administration and SAP Domain. If you wish to learn SAP Hana Administration and build a career in SAP domain, then check out our interactive, SAP HANA Administration Training, that comes with 24*7 support to guide you throughout your learning period. Please find the link for course details:

https://www.tecklearn.com/course/sap-hana-administraion/

SAP HANA Administration Training

About the Course

SAP HANA Administration training will help you learn SAP HANA administration tools, security and user management, system management and availability, data provisioning and integration with non-SAP systems and more. You will deploy SAP HANA Studio, automate through OS scripts, command line admin tools through hands-on projects and case studies. This course is designed to clear the HA200 SAP HANA Operations and Administration Certification conducted by SAP and helps you get the best jobs in top MNCs.

Why Should you take SAP HANA Administration Training?

The average salary for SAP HANA Database Administrator ranges from approximately $65,843 yearly for Administrator to $143,051 yearly for Enterprise Architect. – Indeed.com
SAP HANA is one of the top tools for working with real-time data due to the transformational nature of this tool. The role of an SAP HANA Administration and Operations personnel is critical of the success of SAP HANA deployment in any industrial set-up.
Worldwide Business Intelligence and Analytics Market to grow to $22.8 billion in next 2 years – Gartner.

What you will Learn in this Course?

Overview of SAP HANA

Overview of SAP HANA
Features and Benefits of SAP HANA
SAP HANA Components
Concept of SAP HANA guides
Scenarios in SAP HANA Administration

Architecture of SAP HANA

What is SAP HANA persistence?
Concept of Sizing in SAP HANA
Process of achieving scale out in SAP HANA
How SAP HANA achieves in-memory computation
Concept of columnar database
How SAP HANA is so fast with comparison to other tools

SAP HANA installation

Overview of SAP HANA tools

SAPA HANA Studio for Administration activities in SAP HANA
Accessing local and remote HANA systems using Client Tool
Monitor, Control, Configure and Administer the SAP HANA database using DBA Cockpit
Using HANA Studio Information modelling and data provisioning in HANA database
SAP HANA Transport

SAP HANA Operations

Privileges in HANA
Configure Password Policy and create user in SAP HANA
Auditing in SAP HANA

SAP HANA Smart Data Access

Data Provisioning in SAP HANA

Leveraging SAP HANA technology and integration architecture for enriching, cleansing and transforming data from disparate sources
Data replication with the SAP Landscape Transformation (SLT)
Implementing SLT Configuration with SUSPEND LOAD

SAP HANA Operations

Create Repository and Job Server
SAP GRC Architecture

Administration Operations in HANA Studio

Updating HANA database using lifecycle management
Security in SAP HANA
Sessions and Transactions in SAP HANA
Configure System Replication using HANA Cockpit

Migration to SAP HANA using DMO

Performance Monitoring and Analysis in SAP HANA

Got a question for us? Please mention it in the comments section and we will get back to you.

518