Create SQL Numbers Table for SAP HANA Database

ABAP programmers developing SQL solutions on SAP HANA database may require numbers table or a numbers sequence to use in SQLScript Select statements, CDS views or in AMDP codes. In this HANA SQL tutorial, I want to show how SQLScript programmers can use series_generate_integer HANA database Series function or SYS.Objects system view to create a numbers table for SAP HANA database developments.

HANA database programmers can use series data SQLScript functions like series_generate_date in order to dates table. In this SQLScript tutorial, we will use series_generate_integer function as an alternative way to create numbers table

HANA Database Numbers Table using Sys.Objects System View

SYS.OBJECTS system view provides information about all available database objects like object name, its schema, type, etc. Sys.Objects system view has a public synonym called Objects which enables developers to query using Objects synonym instead of using its sys.objects name form

Since there are numerous objects defined in a database, this system view can be used as a source for our custom numbers table. Let's check this sys.objects system view for table column names and data content in this system view


select top 3 * from SYS.OBJECTS

Code

SAP HANA database sys.objects system view

As the following query on public synonym of Sys.Objects system view shows, there are quite a lot objects in this database.


select count(*) from OBJECTS

Code

number of rows on sys.objects SAP HANA system view

Let's now using SQL Row_Number() function and limiting the result set with LIMIT clause or using TOP clause, SAP HANA developers can create following user-defined function which returns numbers table for the given range as output.


CREATE FUNCTION Numbers_Table (maxnumber int)

 RETURNS table (rownum int)

AS

BEGIN

 RETURN

  select row_number() over (order by OBJECT_OID) as rownum

  from Objects

  limit :maxnumber;

END;

Code

Here is how we can consume or call this user-defined SQL function in our database programming codes


select * from Numbers_Table(12) as nt

Code

SAP HANA numbers table SQL function

Numbers Table using SAP HANA Series Functions

What about creating a better performing numbers table for HANA database developments?

Another option and the preferrable one is using the Series Data function SERIES_GENERATE_INTEGER to create a number sequence with given interval and between given number range.

Since we want to build a numbers table for successive integers starting from 1 to let's say 100;
SQL function Series_Generate_Integer call will be as follows:
the interval will be 1, this is the first argument for the function,
the period start will be the previous value of the first integer. So we will pass 0 instead of 1 as the second parameter.
And the last parameter will be the maximum value or the end value for the integer numbers sequence, so 100


select Element_Number from SERIES_GENERATE_INTEGER(1,0,100) as Numbers;

Code

Output of our virtual numbers table will be as follows

create SQL numbers table with Series function on SAP HANA database

Of course, SAP HANA developers can embed this SELECT statement to build a reusable function as follows:


CREATE FUNCTION Numbers_Table ( maxnumber int )

 RETURNS TABLE ( rownum int )

AS

BEGIN

 RETURN

  select element_number as rownum

  from SERIES_GENERATE_INTEGER(1,0,:maxnumber);

END;

Code

Here is how HANA database developer can use it in custom SQL codes


select * from Numbers_Table(15)

Code

And the result is as follows

SAP HANA database user defined SQL function for numbers table

Performance Comparison of Numbers Tables for SAP HANA

As a developer, if you have checked the Sys.Objects system view, you will realize there are many tables joined to build the final Objects view data which is a costly process.
I wondered what will be the results if I compare both methods to generate numbers table on HANA database by performance.

I tested performance of both methods using following way.
I opened an empty SQL Console and paste below SQL code


select Element_Number from SERIES_GENERATE_INTEGER(1,0,100) as Numbers;

select top 100 ROW_NUMBER() over (order by OBJECT_OID) m from sys.OBJECTS;

Code

Then right click on the console and from context menu, choose options: Visualize Plan > Execute or use short-cut keys Ctrl+Shift+X
When the VisPlan outputs are prepared and displayed on the screen, you can simply compare Compilation Time and Execution Time figures for performance comparison.

SQL performance for numbers table with SAP HANA Series function
SAP HANA Series function performance in Plan Visualizer

HANA Plan Visualizer output for numbers table function performance
SAP HANA numbers table function performance where sys.objects view is used

If you want to learn how to create a table function on a HANA database using SAP HANA Studio, please ream my HANA database tutorial Create Table Function in SQL using SAP HANA Studio as a step by step guide.