The SAP HANA SQL and System Views Reference was updated to reflect the new licensing options available for SAP HANA. All together it documents 137 SQL statements, 155 SQL functions and 308 system views.

To make it easier to locate the information relating to options the structure of the guide was changed to include separate sections for SQL Reference for Options and System Views Reference for Options.

/wp-content/uploads/2015/03/sql_ref_options_675440.png

Some of the highlights include:

Partitioning

New type of multi-level partitioning called range-range, this allows you to use a year as the partition specification and create a number of partitions for each year, for example all records from 1- 20,000 and records greater than 20,000. Additional data types for range partitioning include BIGINT and DECIMAL.

Table re-distribution

Table re-distribution now allows you to assign tables or partitions to a particular node in a distributed SAP HANA system.

Regular Expressions

SAP HANA SPS 09 supports regular expression operators in SQL statements. The search pattern grammar is based on Perl Compatible Regular Expression (PCRE).

Table Sampling

The TABLESAMPLE operator allows queries to be executed over ad-hoc random samples of tables.

Samples are computed uniformly over data items that are qualified by a columnar base table.

For example, to compute an approximate average of the salary field for managers

over 1% of the employee (emp) table you could run the following query:

SELECT count(*), avg(salary) FROM emp TABLESAMPLE SYSTEM (1) WHERE type = ‘manager’

Note that sampling is currently limited to column base tables and repeatability is not supported.

Number Functions
Number functions take numeric values, or strings with numeric characters, as inputs and return numeric values.

BITCOUNT
Counts the number of set bits in the given integer or VARBINARY value

BITXOR
Performs an XOR operation on the bits of the given non-negative integer or VARBINARY values

BITOR
Performs an OR operation on the bits of the given non-negative integer or VARBINARY values

BITNOT
Performs a bitwise NOT operation on the bits of the given integer value

More information on all of these features can be found in the SAP HANA SQL and System Views Reference on the SAP Help Portal.

Additional Resources

The HANA Academy also has a number of videos on these new SQL features and many more topics. Be sure to check them out:

SAP HANA Academy – SQL Functions: String_Agg

SAP HANA Academy – SQL Functions: TABLESAMPLE

SQL Functions: PERCENTILE_CONT and PERCENTILE_DISC

SAP HANA Academy – SQL Functions: BITCOUNT Bitwise Operation

SAP HANA Academy – SQL Functions: BITOR Bitwise Operation

SAP HANA Academy – SQL Functions: BITXOR Bitwise Operation

Additional SQL guides include:

SAP HANA Search Developer Guide

SAP HANA Spatial Reference

Backup and Recovery commands in the SAP HANA Administration Guide

To report this post you need to login first.

Be the first to leave a comment

You must be Logged on to comment or reply to a post.

Leave a Reply