What’s New in HANA 2.0 SPS04 – SQL

I would like to provide deeper insight from a developer’s perspective of what is newly available for SQL & SQLScript language features. One of the strategic focus is becoming more general-purpose supporting various use cases and applications by extending the coverage of SQL standards functionality and easier development for SAP HANA. With SPS04, there is a long list of new features to support it.

We will go through a series of blog posts to cover what is newly available in SQL and SQLScript. Let’s start looking into the new features for the SQL language extension.

Transactional Savepoint

If we have to name the most important feature for SPS04, I believe transactional savepoint will be at the top of the list. This feature has been requested for a long time from various customers for more fine controlled transactional processing and now finally available.

Savepoint allows rollback of a transaction to a defined point allowing partial rollback. Several savepoints can be named at different positions and transaction can be rolled back to any specific named position allowing partial rollback.

The following SQL statements are supported for Transactional Savepoint.

SAVEPOINT <name>             -- Savepoint with user defined name
ROLLBACK TO SAVEPOINT <name> -- Rollback to a Savepoint by name
RELEASE SAVEPOINT <name>     -- Release a Savepoint by name

Regular COMMIT or ROLLBACK will release any savepoints defined for the transaction. The savepoint identifier should be unique within a transaction and if a savepoint is defined with a same name, existing savepoint will be released and the newly defined savepoint will be kept.

The transactional savepoint is also supported as part of the JDBC client interface protocol. As part of the java.sql.Connection class, the following methods are supported.

Savepoint setSavepoint( ) throws SQLException
Savepoint setSavepoint(String <name>) throws SQLException
void rollback(Savepoint <savepoint>) throws SQLException
void releaseSavepoint(Savepoint <savepoint>) throws SQLException

Statement Level Triggers

Another item top on the list is statement level triggers. The trigger is fired after executing each of the INSERT, UPDATE, and DELETE trigger defined statement. Previously, this was only supported for row store table but now extended to also support column store.

Statement level triggers are very useful for processing bulk data where computation is processed for the entire statement for the whole processed data instead of individual computation for each record. The following are examples of how statement level tri

After INSERT statement

The data that has been inserted can be referenced through table <transition_table_new> during the trigger execution.

CREATE TRIGGER <trigger_name>
AFTER INSERT ON <table_name>
REFERENCING NEW TABLE <transition_table_new>
FOR EACH STATEMENT
BEGIN
  -- Triger Body
END;

After UPDATE statement

The original data can be referenced by table <transition_table_old > and updated data by <transition_table_new> during the trigger execution.

CREATE TRIGGER <trigger_name>
AFTER UPDATE ON <table_name>
REFERENCING NEW TABLE <transition_table_new>
            OLD TABLE <transition_table_old>
FOR EACH STATEMENT
BEGIN
  -- Triger Body
END;

After DELETE statement

The original data can be referenced by table <transition_table_old > during the trigger execution.

CREATE TRIGGER <trigger_name>
AFTER DELETE ON <table_name>
REFERENCING OLD TABLE <transition_table_old>
FOR EACH STATEMENT
BEGIN
  -- Triger Body
END;

Application-time and Bi-temporal tables

System-versioned table, Application-time table, and Bi-temporary table is part of the SQL:2011 standard. With SPS03, system-versioned table was first introduced and now fully supported for the remaining.

Application-time table

captures the time, in which a record is valid in the business world.

◈ Validity periods are determined by the application.
◈ Application can update validity period of a record, e.g., to correct errors.
◈ Temporal information can arbitrarily reflect the past, present or future at any granularity.

Application-time period tables in SAP HANA:

◈ Based on regular column-store tables.
◈ Only one table.
◈ Validity period can be data type DATE, TIME, TIMESTAMP, …

Special DML operations for updating a records temporal information

UPDATE <table> FOR PORTION OF APPLICATION_TIME …

Special SQL syntax, allowing access to time-dependent information

… FOR SYSTEM_TIME AS OF <timestamp>

example of application-time table

CREATE COLUMN TABLE EMPLOYEE
(
empl_no int,
Empl_name nvarchar(200),
Empl_department int,
…
valid_from <some date type> not null,
valid_to <some date type> not null,
period for APPLICATION_TIME (valid_from, valid_to),
)

ALTER TABLE EMPLOYEE ADD PERIOD
FOR APPLICATION_TIME(<VALID_FROM_COLUMN_NAME>, <VALID_TO_COLUMN_NAME>)

SELECT * FROM EMPLOYEE FOR APPLICATION_TIME AS OF ‘<timestamp>’;

UPDATE EMPLOYEE FOR PORTION OF
APPLICATION_TIME FROM <point in time 1> TO <point in time 2>
SET <set clause list>
WHERE <search condition>

System-versioned table

allow change tracking on database tables.

◈ A new record version is inserted for every change to preserve a records history.
◈ Validity periods are automatically maintained by the database whenever a record is changed.

System-versioned Tables in SAP HANA:

◈ Based on regular column-store tables.
◈ Two structurally equivalent tables: CURRENT and HISTORY.
◈ Tables are automatically combined in the SQL layer.
◈ Validity periods are based on data type TIMESTAMP.
◈ Inherent support for table partitioning and associated features.

Special SQL syntax, allowing access to historic data

SELECT … FOR SYSTEM_TIME AS OF <timestamp>
SELECT … FOR SYSTEM_TIME FROM <t1> TO <t2>
SELECT … FOR SYSTEM_TIME BETWEEN <t1> AND >t2>

Bi-temporal table

combine system-versioned tables for tracking changes and application-time period table for valid data in the business world.

Lateral Join

Allows correlation between table subqueries put side by side in the context of JOIN operation horizontally from left table to right table in subquery

◈ Functioning as a for each loop of the left table joining to the right table.
◈ Useful when cross referencing column is used as computing the rows to be joined
◈ Cross Product, INNER and LEFT OUTER joins are only supported

As shown in the example above, the subquery need to traverse all TR against TL to check column b2 value is less that a2.

Collations

Define a sorting order based on language collation settings

◈ The collation supported can be verified by select * from COLLATIONS

◈ Currently, 144 collation type is supported

◈ Special collations such as German Phonebook, Chinese Stroke, Case Insensitive (CI), Accent Insensitive (AI), etc. are supported

◈ Accent Insensitive is superset of Case Insensitive

To get a full list of collations supported

SELECT * FROM <table_name>

ORDER BY COLLATE <collation_name>

Sample collation that is supported to list just a few

SELECT…FOR JSON

Converts a tabular data to JSON document string format

CREATE TABLE LANG (id int, name nvarchar(20));

INSERT INTO LANG VALUES (1, NULL);

INSERT INTO LANG VALUES (2, 'en');

INSERT INTO LANG VALUES (3, 'de');

SELECT id, name from LANG for JSON;

The return data is JSON document string

[{"ID":1},{"ID":2,"NAME":"en"},{"ID":3,"NAME":"de"}]

Additional options for formatting is allowed

SELECT id, name from LANG for JSON ('format'='yes');

and the return JSON is formatted

"[{

  ""ID"": 1

}, {

  ""ID"": 2,

  ""NAME"": ""en""

}, {

  ""ID"": 3,

  ""NAME"": ""de""

}]"

additional options to also display null value and omit array wrap

SELECT id, name from LANG for JSON ('omitnull'='no', 'arraywrap'='no');

and returned value is where null value is also return and square brackets are removed.

{"ID":1,"NAME":null},{"ID":2,"NAME":"en"},{"ID":3,"NAME":"de"}

Misc Enhancements

The following are additional features

◈ RENAME SCHEMA

◈ CREATE OR REPLACE for views, triggers, and synonyms instead of drop then create.

◈ CTEs within subquery