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:
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
Backup and Recovery commands in the SAP HANA Administration Guide
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
38 | |
19 | |
13 | |
13 | |
11 | |
10 | |
10 | |
10 | |
8 | |
8 |