SQLScript – the SQL dialect of SAP HANA

Overview of SQLScript

SQLScript is a programming language that is an extension of the SQL standard by SAP. This language is designed to move the execution of complex business logic from the application server to the SAP HANA database.

This makes the language comparable to the extensions of other database manufacturers, such as Oracle’s PL/SQL or IBM’s SQL/PL. All of these extensions allow a sequence of SQL statements to be stored on the date bank server in the form of procedures or functions. These can then be started with a single call from the application server. Unlike individual SELECT queries, the procedure calls can also return multiple result sets.

Moving complex queries to the date bank server using brings significant benefits in execution. For example, the declarative formulation of queries in SQL allows massive optimization and parallelization of execution. In contrast, imperative, record-wise programming on the application server does not allow such optimization.

Resources

Sap’s reference documentation is definitely the most comprehensive and important source in the field.

SAP HANA Cloud QRC 02/2020

SAP HANA Cloud SQLScript Reference

SAP HANA 2.0 SPS05

SAP HANA SQLScript Reference
SAP HANA SQL and System Views Reference

The big cheat sheet to SQLScript

Blog Articles about SQLScript

Cheat Sheet – The SAP HANA SQLScript Overview

Cheat Sheet – The SAP HANA SQLScript Overview This SQLScript Overview is the perfect complement to the training of Brandeis Consulting Syntax, examples and descriptions,…

Why are scalar UDFs so slow?

When loading data from source systems, there are requirements in SAP BW that are often repeated at the field level. This includes, above all, logics…

User Defined Libraries (UDL) in SQLScript

SAP HANA 2.0 SPS03 introduced the concept of User Defined Libraries (UDL) in SQLScript. It enables us as developers to bundle several functions, procedures and…

Anonymous Functions in SQLScript

With SAP HANA 2.0 SPS04, another feature has been added to the SQLScript language: The embedded functions. These enable the use of imperative SQLScript code…

Recursive procedure calls in SQLScript

Up to SAP HANA 2.0 SPS03 recursive calls in SQLScript were not allowed. This means that a procedure may not call itself either directly or…

 

SQLScript News and Updates

The differences between the language in SAP HANA Cloud and HANA 2.0 SPS04 are described in this blog post:
Is the SQLScript of SAP HANA Cloud compatible with its predecessors?

The document What’s New in the SAP HANA Platform 2.0 contains the changes for each Support Package level of 2.0. For example, some new features in SPS04 that I covered in part in my blog are:

Versions of the language

With each new version of SAP HANA, the language is currently evolving. Accordingly, you always have to make sure that you have the right version in front of you during the documentation. In particular, there is a distinction between On Premise versions and the SAP HANA Cloud. The latest versions are:

And what is this HANA SQL?

SQLScript is a programming language that is an extension of the SQL standard by SAP. This language is designed to move the execution of complex business logic from the application server to the SAP HANA database.

This makes this language similar to that of other database vendors, such as Oracle’s PL/SQL or IBM’s SQL PL. All of these extensions allow a sequence of SQL statements to be stored on the database server in the form of procedures or functions. Unlike single SELECT queries, procedure calls can also return multiple result sets.

SQLScript vs. SAP HANA SQL

Moving complex queries to the database server provides significant execution benefits. For example, the declarative formulation of queries in SQL allows massive optimization and parallelization of execution. In contrast, imperative, record-wise programming on the application server does not allow such optimization.

Nevertheless, imperative elements are also part of the SQLScript programming language. This allows the programming of algorithms and flow logic in procedures. A mixture of imperative and declarative logic is also possible, whereby optimization and parallelization is only possible to a limited extent.

With the AMDP Framework, SAP has created an almost seamless integration of database procedures into the ABAP language. This allows you to define, implement, and call database procedures and functions directly in ABAP source code. As an ABAP developer, you can use the advantages of SQLScript and optimized execution without direct access to the SAP HANA database.

This post is also available in: Deutsch