SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software
Development resources, articles, tutorials, code samples, tools and downloads for SAP HANA and ABAP, HANA Database, SQLScript, SAP UI5, Screen Personas, Web Dynpro, Workflow

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

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

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)
  select row_number() over (order by OBJECT_OID) as rownum
  from Objects
  limit :maxnumber;

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

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;

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 )
  select element_number as rownum
  from SERIES_GENERATE_INTEGER(1,0,:maxnumber);

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

select * from Numbers_Table(15)

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;

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.


Install SAP Free
CRM Companies List
Web Based CRM Software

Copyright © 2004 - 2021 Eralper YILMAZ. All rights reserved.