New SQLScript Features in SAP HANA 1.0 SPS9

Semantic Code Completion in SAP HANA Studio

The fact that we have such long names for tables, views and table types in HANA, has been a pain point for many for some time now.  To help alleviate this issue, we have built semantic code completion into the SQLScript editor in SAP HANA Studio.  Now when a developer needs to do a SELECT against a particular table, he can hit CTRL+SPACE and get a list of tables to choose from.   The list is compiled of relevant objects based on the context of the statement, so if you have a SELECT statement and have entered the name of the schema already, and hit CTRL+SPACE, you will only get a listing of tables from that particular schema. This also works when calling procedures, or defining parameters with global table types.

Check out the demo video here.

SQLScript Editor & Debugger in the SAP Web-Development Workbench

Prior to SPS9, there was no way to maintain procedures from the SAP Web-based Development Workbench. You were forced to use the SAP HANA Studio for this.  Now as of SPS9, we have a basic SQLScript editor for maintaining .hdbprocedure files.  The .procedure file format is not supported here.  This editor has basic keyword code hints and syntax highlighting.

Since we can now create procedures from the SAP HANA Web-Based Development Workbench, it makes sense that we should be able to debug them.  As of SPS9, we also have a SQLScript procedure debugger as well. Currently, you must set breakpoints in the runtime object in the catalog, and then CALL your procedure from the SQLConsole in order to debug it.  We have plans to make it possible to debug a design time artifact directly without having to drop to the runtime object.  Within the debugger, you can of course single step through the code, and evaluate input/output parameters as well as intermediate scalar and table variables.

See a demo video here.

Table Type Definitions for Parameters

In previous support packages, we’ve had several different ways to create and reference table types when defining input/output parameters in our procedures.   For .procedure file format, we had “local table types” which really were not local, which is why we did not support them in the new .hdbprocedure file format.  For .hdbprocedure files, we recommended to create your tables types globally via CDS(.hdbdd file).  While I will still recommend to create table types via CDS for global type scenarios,  I am pleased to announce that we now have the possibility to declare local table types inline for parameters.  In the screen shot below you will see that I have an OUT parameter called EX_PRODUCT_SALE_PRICE which has a table type definition using the keyword TABLE followed by the column list with associated simple types.  These type declarations are truly local and cannot be used across procedures.  For situations where that you know that your table type will not be reused frequently, it might make sense and be a little easier to simply define the structure inline as opposed to creating it via CDS.

Table Type Definitions for Variables

In previous support packages, intermediate table variables were simply defined by the result set of the data selection, such as a SELECT statement.  So whatever columns were in the field list would become the structure of the intermediate table variable.  The issue with this approach is that there is some performance cost associated with the type conversions at runtime.  Also, this could cause some ambiguousness in the code.  As of SPS9, we can now explicitly define the structure of an intermediate table variable from the DECLARE statement.  As shown below, I have an intermediate table variable called LT_PRODUCTS which is defined as a TABLE, followed by the column list and associated simple types.  This allows the developer to have strict typing within the procedure and avoid any unnecessary performance costs from type conversions.

Autonomous Transactions

Another new language features in SPS9, is Autonomous Transactions.  The autonomous transaction allows the developer to create an isolated block of code which runs as an independent transaction.  This feature is particular helpful when executing logging type tasks.  Committed statements inside the autonomous transaction block will be persisted regardless of a rollback of the main transaction.  The keywords COMMIT and ROLLBACK are only allowed within the autonomous transaction block and not in the main line of the procedure.  If any tables are updated within the main body of the procedure, those tables are not allowed to be accessed from within the autonomous transaction block.

See a demo video of the new language features here.  And for more information regarding the SQLScript language itself, please check out the SQLScript Reference Guide.

Use of CE Functions within Procedures & Scripted Calculation Views

Although not specific to SPS9, I’d like to close with some clarification around the use of CE Functions. Calculation Engine(CE) Functions, also known as Plan Operators, are an alternative to writing SQL.  At one time, it was recommended to always use CE Functions over SQL in both SQLScript stored procedures as well as scripted calculation views as they performed better than SQL. This is no longer the case.  The recommendation moving forward is to use SQL rather than CE Functions within SQLScript. The execution of Calculation Engine Functions currently is bound to processing within the calculation engine and does not allow a possibility to use alternative execution engines, such as L native execution. As most Calculation Engine Functions are converted internally and treated as SQL operations, the conversion requires multiple layers of optimizations. This can be avoided by direct SQL use. Depending on your system configuration and the version you use, mixing Calculation Engine Functions/Plan Operators and SQL can lead to significant performance penalties when compared to plain SQL implementation. Please note that the above described recommendation/behavior only applies to calculation engine functionality exposed by SQLScript.  Therefore only SQLScript related artifacts such as procedures, table functions and scripted calculation views are affected.

For a more general overview of all of the developer related features for SAP HANA 1.0 SPS9, please have a look at this blog post.