Technology Blogs by SAP
Learn how to extend and personalize SAP applications. Follow the SAP technology blog for insights into SAP BTP, ABAP, SAP Analytics Cloud, SAP HANA, and more.
cancel
Showing results for 
Search instead for 
Did you mean: 
Paul_Higgins
Advisor
Advisor

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


SAP HANA Spatial Reference


Backup and Recovery commands in the SAP HANA Administration Guide