Query regarding local temporary tables in HANA

[former_member255525]

Hello Experts,

I have a query regarding the local temporary tables. When you create a local temporary table in procedure it is created under what schema?

Eg: CREATE LOCAL TEMPORARY TABLE #approved_batches(TASKID INTEGER, PINFOID NVARCHAR(100));

under which schema the above table is created?, is it the schema where the procedure is running on or somewhere else?

What happens if I explicit mention the schema name before the temporary table name like this?

CREATE LOCAL TEMPORARY TABLE "SAP_FSN".#approved_batches(TASKID INTEGER, PINFOID NVARCHAR(100));

Will it cause any issue or is it a bad practice?

Kindly answer my questions

Regards,
Sid

[lbreddemann]

From a database catalog point of view, the temporary tables are handled the same as regular tables.

You can create them in any schema your user has privileges to.

I am not aware that there is any general catalog of 'bad practices' around this, but usually, schemas should be handled like namespaces.

So, if that temporary table belongs there, then put it into the schema. Seeing that you want to work with a local temporary table, I'd guess that it has more relevance to the current user/session than to the schema itself. Therefore I advice to keep this with the sessions namespace/schema. The other observation here is that the need for temporary tables can be a 'code smell' that some line-by-line processing is done in your code. So, the recommendation here is to be very clear about the overall effect of the temporary table (think of multi-user large scale usage).