SQLScript Extends Standard SQL
SQLScript is a set of extensions added on top of standard SQL. It is used to exploit the specific features of SAP HANA.
By using these extensions, SQLScript allows much more pushdown of the data–intensive processing to the SAP HANA database, which otherwise would have to be done at the application level.
Applications benefit most from the potential of SAP HANA when they perform as many data-intensive computations in the database as possible. This avoids loading large amounts of data into an application server separate from SAP HANA, and leverages fast column operations, query optimization, and parallel execution. This can be achieved to a certain extent if the applications use advanced SQL statements. However, sometimes you may want to push more logic into the database than is possible when using individual SQL statements, or make the logic more readable and maintainable. Therefore, SQLScript has been introduced to assist with this task.
SQLScript Definition and Goal
SQLScript is defined as follows:
The language to write stored procedures and user-defined functions in SAP HANA
An extension of ANSI SQL
The main goal of SQLScript is to allow the execution of data intensive calculations within SAP HANA. This is helpful for the following reasons:
It eliminates data transfer between database and application tiers.
It executes calculations in the database layer to benefit from fast column operations, query optimization, and parallel execution.
SQLScript Advantages
Compared to standard SQL, SQLScript provides the following advantages:
Using SQLScript, complex logic can be broken down into smaller chunks of code. This encourages a modular programming style which means better code reuse. Standard SQL only allows the definition of SQL views to structure complex queries, and SQL views have no parameters.
SQLScript supports local variables for intermediate results with implicitly-defined types. With standard SQL, it would be required to define globally visible views even for intermediate steps.
SQLScript has flow control logic such as if-then-else clauses that are not available in standard SQL.
Stored procedures can return multiple results, while a standard SQL query returns only one result set.
Pushing the processing to SAP HANA is beneficial because there are lots of opportunities for SAP HANA to optimize the execution with in–memory, parallel processing.
Standard SQL does not provide sufficient syntax to push many calculations to the database and as a result, the application layer has to take on this duty. This means huge amounts of data must be copied between the database server and the application server.
It is important to remember that SQLScript is not a full application programming language such as ABAP or C++. However, SQLScript can reduce the need to program data intensive tasks in the application layer by providing imperative language. Imperative language allows the developer to add very precise control-flow logic, for example, to the read tables, one record at a time and process each record before returning to read the next record. Standard SQL does not allow this, and only provides a way to write set-based logic that returns complete data sets from an instruction. That is why standard SQL needed to be extended to add more programmatic control in the database layer.
The cost of using the SQLScript imperative language elements is that you potentially break the automatic optimization by SAP HANA. This is because you introduce dependencies in your logic. For example, before you can apply a discount, you first need to read through all customer sales records line by line, check if they are eligible records, and sum the total sales to look up a discount table based on spend amount. With SQLScript you are able to take over the control of the logic flow to make sure each step happens in the right order. However, the parallelization potential is limited if queries, that should otherwise be able to execute independently, get caught up in this sequenced logic.
