SQLScript – the SQL dialect of SAP HANA
Resources to SQLScript
Sap’s reference documentation is definitely the most comprehensive and important source in the field. A distinction is made between HANA SQL and SQLScript, see also below.
SQLScript News and Updates
The document What’s New in the SAP HANA Platform 2.0 contains the changes to each support package level of 2.0, not just SQLScript. For example, some new features of SQLScript in SPS04 that I covered in part in my blog are:
- Recursive procedure calls
- SQLScript Unit Tests – The End-User Testing Framework
- Parallel processing with map merge
- The EXISTS and IN operators in SQLScript, for example in an IF/ELSE branch
- DML statements such as INSERT, UPDATE and DELETE on table variables
- Run Dynamic SQL as ReadOnly
- Embedded functions in SQL queries
Versions of SQLScript
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:
- HANA 2.0 PLC 05 – latest version on Premise systems
- HANA Cloud QRC 2/2020
And what is 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 SQLScript 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 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 SQLScript 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.
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 directly define, implement, and call SQLScript procedures and functions in the 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.
Request more information
This post is also available in: Deutsch