Here we will see one quite useful in-built Hana function $rowid$ and it usages.

$rowid$ –

Returns the internal row ID value for each row of the table. Whenever any new row is inserted in a Hana table new unique numeric ID is internally assigned to that row. This id is not available in the table in the form of any column, so we need to use $rowid$ function to see the id generated for each row in that table.

Below is the example –

Create one simple table using below code and insert few records in that table.

CREATE COLUMN TABLE HXE.ROWID_TEMP (NAME NVARCHAR (50), ID INT);

INSERT INTO HXE.ROWID_TEMP VALUES ('A', 1);
INSERT INTO HXE.ROWID_TEMP VALUES ('B', 2);
INSERT INTO HXE.ROWID_TEMP VALUES ('C', 3);

SELECT *, ("$rowid$") AS "ROWID" FROM HXE.ROWID_TEMP

Use Select query to see result along with rowid column as additional column.Here we can see Unique numeric Id assigned to each row automatically.

Now we will observe what is the impact on this rowid when we do Update/Delete & Truncate.

Update Scenario – Update any row in the table.

UPDATE HXE.ROWID_TEMP SET NAME = 'Z' WHERE NAME = 'A';

After update row with ID 1 is now assigned newly generated unique id.

Delete Scenario – Delete all rows from the table and insert data again using insert statement provided below.

DELETE FROM HXE.ROWID_TEMP;

INSERT INTO HXE.ROWID_TEMP VALUES ('A', 1);
INSERT INTO HXE.ROWID_TEMP VALUES ('B', 2);
INSERT INTO HXE.ROWID_TEMP VALUES ('C', 3);

SELECT *, ("$rowid$") AS "ROWID" FROM HXE.ROWID_TEMP

After deleting all the records from table, rowid starting after the last generated rowid for that table before delete. Maximum Id generated in our sample table was 4 before deletion.

Truncate Scenario – Truncate table and insert data again using insert statement provided below.

TRUNCATE TABLE HXE.ROWID_TEMP;

INSERT INTO HXE.ROWID_TEMP VALUES ('A', 1);
INSERT INTO HXE.ROWID_TEMP VALUES ('B', 2);
INSERT INTO HXE.ROWID_TEMP VALUES ('C', 3);

SELECT *, ("$rowid$") AS "ROWID" FROM HXE.ROWID_TEMP

Truncate statement will reset the rowid in the table to starting values i.e. 1.

This function is quite useful and can be used in some of the common scenarios like below.

1. Delete any duplicate record where we do not have any key column available and we do not want to use any row number or rank function.

2. Can be implemented for delta record processing by capturing last generate/loaded rowid for the table.

Hope you find this function/article helpful.

Happy Learning!!