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.
Some of the highlights include:
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 now allows you to assign tables or partitions to a particular node in a distributed SAP HANA system.
SAP HANA SPS 09 supports regular expression operators in SQL statements. The search pattern grammar is based on Perl Compatible Regular Expression (PCRE).
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 take numeric values, or strings with numeric characters, as inputs and return numeric values.
Counts the number of set bits in the given integer or VARBINARY value
Performs an XOR operation on the bits of the given non-negative integer or VARBINARY values
Performs an OR operation on the bits of the given non-negative integer or VARBINARY values
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.
The HANA Academy also has a number of videos on these new SQL features and many more topics. Be sure to check them out:
Additional SQL guides include: