Differentiating SQL and SQLScript

Structured Query Language (SQL) is a standardized language for communicating with a relational database. SAP HANA database is a relational database and fully supports SQL.

SQL is used to retrieve, store, or manipulate data in the SAP HANA database.

• SAP HANA is a relational database management system.
• SAP HANA supports SQL.

SQL is a declarative language, which means that you write statements that describe what you want to achieve and not precisely how the system should achieve it.

For example, when you request data from multiple tables, you do not define the sequence in which tables should be opened. You leave that decision to the database so that it can decide on the best approach. Depending on the size of tables, the location, and the dependencies on other parts of the SQL statement, the system may read the tables in any order, or perhaps even in parallel. A key goal of SQL is to execute your statements using the best performance possible. Consider that landscapes can change and so the precise execution of your SQL may change over time. What does not change is your intent.

SQL is written in a series of statements. There are many statements which can be used with SQL to interact with a database. Statements are grouped into three families:

• DDL – data definition language

  • Creation (CREATE) of database table by defining the structure and the table columns

  • Modify or ALTER the table definition in the database

  • Remove or DROP the table from the database

  • Other objects like Schemas, Indexes, Views, Sequences, and Triggers can also be managed using this subset

• DML – data manipulation language

  • SELECT data from the database including filtering, joining, and so on

  • INSERT data into the database

  • UPDATE data from the database

  • DELETE data from the database

  • Other actions like Truncate table data, Load or Unload tables into memory, and so on

• DCL – data control language

  • CREATE, ALTER, and REMOVE users, user groups, and roles in the database

  • GRANT or REVOKE data access in an object to a specific user or set of users

  • GRANT or REVOKE execute access on DDL / DML commands on the database to a specific user or set of users

  • COMMIT and ROLLBACK of the changes made in the database

  • Other actions like create or manage audit policies, define remote source, and so on

• Data Types: Binary, Boolean, Character, Datetime, LOB, Numeric, etc.
• Operators: Perform operations on the data; e.g., arithmetic or string manipulation, comparison, etc.
• Expressions: Can be evaluated to return values; e.g. CASE, Aggregates, SQL functions, etc.
• Predicates: Specified by combining expressions or logical operators; e.g. LIKE, BETWEEN, CONTAINS, etc.
• Functions: Returns an output based on the manipulation on the data; e.g., convert data type, date and time manipulation, etc.

The database can store many different data types data. Data type defines the characteristics of a data value. The data types are classified based on their characteristics, as shown in the table below.

The data types, mentioned in the below table, are supported by SAP HANA SQL.

Notice the special SAP HANA data types (TEXT, ST_GEOMETRY, …)

Operators perform arithmetic, string, and comparison operations in expressions.

Some of the examples are unary operators like NOT, + (unary positive), - (unary negative), or logical operators like NOT, OR, AND, or comparison operators like ‘=’, ‘<’, ‘<=’, and so on.

An expression is a clause that can be evaluated over a set of inputs to return values as designed.

For example, the CASE expression below will evaluate the input and return a value based on the input value.

1234567
CASE 
WHEN 'APPLE' 
THEN 'FRUITS'
WHEN 'CABBAGE'
THEN 'VEGETABLE' 
ELSE 'UNKNOWN' 
END

Just like CASE expressions, there are others like SQL built-in functions evaluating as expressions (ABS, MIN, RAND, SUBSTRING, and so on).

Predicate is specified by combining one or more expressions or logical operators, and returns one of the following logical values: TRUE, FALSE, or UNKNOWN.

For example, the test performed in the WHERE clause of the following query is a predicate:

123
SELECT * 
FROM <table_1> 
WHERE itemtype IN ('FRUIT', 'VEGETABLE');

There are many built-in functions that are provided with SAP HANA SQL standard. They can be used to convert data from one data type to another, manipulate or return information of a string, or perform mathematical operations to return a value after manipulation.

1
TO_VARCHAR, TO_DATE, LENGTH, SUBSTR_AFTER, SQRT, etc.

You can add comments to improve the readability and maintainability of your SQL statements. Comments are delimited in SQL statements as follows:

• Double hyphens "--". Everything after the double hyphen until the end of a line (not statement) is ignored by the SQL parser.

• "/*" and "*/". This style of commenting is used to wrap comments that appear on multiple lines. All text between the opening "/*" and closing "*/" is ignored by the SQL parser.

SQLScript is a set of extensions on top of standard SQL that employs the unique features of SAP HANA. It is based on the ANSI-92 SQL standard but adds extensions to exploit SAP HANA features:

• Datatype extensions
• Additional security
• Logic containers (procedures, functions)
• Implicitly- and explicitly-defined table variables
• Adds imperative logic
• Orchestration logic to control both imperative and declarative statements

By using these extensions, it allows much more pushdown of 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 it leverages fast column operations, query optimization, and parallel execution. This can be achieved to a certain extent if the applications use advanced SQL statements, but sometimes you may want to push more logic into the database than is possible by using individual SQL statements, or by making the logic more readable and maintainable.

SQLScript has been introduced to assist with this task.

Compared to plain 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. SQL only allows the definition of SQL views to structure complex queries, and SQL views have no parameters.

• SQLScript supports local variables for staging 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 and looping constructs, that are not available in SQL.

• Stored procedures can return multiple results with different tabular structures, while an SQL query returns only one result set.

• SQLScript supports input parameters so that code can be re-used in different scenarios by passing different parameter values. SQL does not support parameters and so SQL is usually limited in its use.

Pushing the processing to SAP HANA is a good thing 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.

The primitive data types, mentioned in the table below, are supported by SQLScript.

SQLScript currently allows a length of 8,388,607 characters for the NVARCHAR and the VARCHAR data types, unlike SQL where the length of that data type is limited to 5,000.

It is important to remember that SQLScript is not a full application programming language such as ABAP or C++. But SQLScript can significantly reduce the need to program data-intensive tasks in the application layer by providing imperative language in the database. 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, then return to read the next record. Standard SQL does not allow this type of processing and only provides a way to write set-based logic that works on complete data sets from an instruction. That is why standard SQL needed to be extended with SQLScript to add more programmatic control in the database layer. This is especially necessary for transaction-based applications that operate at the record level.

SQL is a descriptive, or sometimes called declarative, language.

SQLScript is written in logic containers such as procedures or functions. These are XS advanced source objects that are part of a complete SAP HANA application along with other related application components such as Java, HTML, and so on.

However, the cost of using the SQLScript imperative language elements is that you potentially break the automatic optimization of SAP HANA. This is because you introduce dependencies into your logic. For example, before you can apply a discount, you first need to read through all customer sales records line by line and check if they are eligible records, and then sum the total sales to then 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. With this, you seriously limit the parallelization potential if queries that should otherwise be able to execute independently get driven by this type of strictly sequenced logic.