Introducing SQLScript

After completing this lesson, you will be able to:

After completing this lesson, you will be able to:

  • Explain the basic concepts of SQLScript

What is SQLScript?

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.

Declarative VS. Imperative Logic

Declarative VS. Imperative

SQL is a descriptive language that is sometimes called a declarative language.

SQLScript is written in either Procedures or Functions. These are XS Advanced source objects that are part of a complete SAP HANA application.

Declarative logic allows the developer to declare the data selection via SELECT statements, as follows:

  • The developer defines the what.

  • The engine defines the how, and executes accordingly.

This enables massive parallelization.

Imperative logic allows the developer to control the flow of the logic within SQLScript using the following:

  • Scalar variable manipulation

  • DDL/DML logic

  • WHILE loops

  • Branching logic based on some conditions, for example IF/ELSE

Logic is executed exactly as scripted (procedurally).

Code Pushdown

We need to change the way we think about application development. Previously, for example in ABAP, we did a SELECT * INTO TABLE and brought thousands of rows back to the application server, then looped over it and did some processing. The new model suggests a different approach, where we take the data-intensive logic and process that logic in the database closer to the data, and then only send back to the application layer what is necessary for presentation to the end user for further processing. This is referred to as code pushdown. The SAP Business Suite on SAP HANA does this in a couple of ways, for example, in ABAP, we can now leverage SAP HANA views by exposing them to the ABAP dictionary, via external views. We can also expose SQLScript procedures and call them directly from ABAP, using the CALL DATABASE PROCEDURE statement.

Save progress to your learning plan by logging in or creating an account