The SAP HANA SQLscript
In the following section, we will learn about the SAP HANA SQLscript and see the additional capabilities it brings along with it.
Why SQLscript?
SQLscript is a collection of extensions in Structured Query Language (SQL). The main motivation for SQLscript is to push data intensive application logic into the database, which was not being done in the classical approach where the application logic is mostly executed in an application layer.
We have the following extensions for SQLscript:
|
Extension |
Usage |
|---|---|
|
Datatype extension (create/drop type) |
This allows definition of table type without corresponding tables |
|
Procedural extension (create procedure) |
This is an imperative construct to push data intensive logic into the database |
|
Functional extension (create function) |
This creates side-effect free scalar or table functions, which can be used to express and encapsulate complex data flows |
How different is an SQLscript in SAP HANA from classical SQL queries?
Let's do a comparative study between an SQLscript in SAP HANA and classical SQL queries to find out what the point of differences are, as shown in the following table:
|
SQLscript in SAP HANA |
Classical SQL |
|---|---|
|
Multiple result sets can be returned |
Query returns only single result set |
|
More database intensive, codes are executed at DB layer, gives better performance |
Limited executions at DB layer resulting in multiple access to and from database, relatively slow performance |
|
Control logics such as if/else and business logics like currency conversion can be easily expressed |
SQL queries do not have such features |
|
Gives more flexibility to developer to use imperative and declarative logics together |
No such flexibility with SQL queries |
|
Supports local variables for intermediate result sets with implicit types |
Globally visible views need to be defined even for intermediate result sets or steps |
|
Parameterization of views is possible |
Parameterization of views is not possible |
The following figure shows you a graphical comparison of the classical approach and the SAP HANA approach:
