SQLScript

Why Do We Need SQLScript?

As a consultant you may want to perform tasks as querying data out of database table, granting or revoking privileges, or altering the tables. Knowledge of SQL will let you perform these tasks.

The main goal of SQLScript is to allow the execution of data intensive calculations inside SAP 
HANA.
There are two reasons why this is required to achieve the best performance:

• • Moving calculations to the database layer eliminates the need to transfer large amounts of data from the database to the application
• • Calculations need to be executed in the database layer to get the maximum benefit from SAP HANA features such as fast column operations, query optimization and parallel execution.
• If applications fetch data as sets of rows for processing on application level they will not benefit from these features

SAP HANA CE Functions - Calculation Engine Plan Operators

SAP HANA CE functions, which is also known as Calculation Engine Plan Operators (CE operators) are alternatives to SQL statements.

• • CE functions are specially optimized for HANA and can perform better than a normal SQL statement.
• • CE functions are executed in calculation engine.
• • CE functions can also help structure the code in a simpler way.

Types of CE Functions:

There are 2 types of CE functions:

1. Data Source Access operators
It binds a column table or a column view to a table variable.

2. Relational operators 
It allows a user to bypass the SQL processor during evaluation and to directly interact with the calculation engine.

Examples of Data Source Access Operators are:

• • CE_COLUMN_TABLE
• • CE_JOIN_VIEW
• • CE_OLAP_VIEW
• • CE_CALC_VIEW

Examples of Relational Operators are:

• • CE_JOIN
• • CE_LEFT_OUTER_JOIN
• • CE_RIGHT_OUTER_JOIN
• • CE_FULL_OUTER_JOIN
• • CE_PROJECTION
• • CE_CALC
• • CE_AGGREGATION
• • CE_UNION_ALL

Never Mix SQL and CE Functions:

Try not to mix up normal SQL and CE functions in a single procedure/scripted calculation view. This will decrease the performance even more.

Table Type in SAP HANA

A table type is

• • Similar to a database table but do not have an instance
• • Used to define parameters for a procedure that represent tabular results.

In HANA, with the help of SQLScript, we can create a Table Type.

How to Create a Table Type:

A table type is created using statement CREATE TYPE and can be deleted using statement DROP TYPE.

Syntax:

CREATE TYPE [schema.]name AS TABLE
          (name1 type1 [, name2 type2,...])

DROP TYPE [schema.]name [CASCADE]

Example:

1. Open HANA studio and run the below SQL statement to create a table type.
   Replace SCHEMA_NAME with your schema.
   
   CREATE TYPE SCHEMA_NAME.TT_SALES AS TABLE (
               SALES_AMOUNT DECIMAL,
               NET_AMOUNT DECIMAL,
               PRODUCT_NAME NVARCHAR(20),
               REGION_NAME NVARCHAR(20),
               SUB_REGION_NAME NVARCHAR(20)
   );
2. After executing the statement you can go to THE schema and find the table type under Procedures -> Table Types section.
   
   
3. Double click on the table type to see the definition.
   
   
4. Remember that we cannot add record to table type. If you try to insert record, you will get an error.
   INSERT INTO SCHEMA_NAME.TT_SALES VALUES (100, 'PROD-1', 'ASIA', 'INDIA');