Question Re: Difference between CREATE LOCAL TEMPORARY COLUMN TABLE and CREATE LOCAL TEMPORARY TABLE in Technology Q&A

Difference between CREATE LOCAL TEMPORARY COLUMN TABLE and CREATE LOCAL TEMPORARY TABLE

kgaurav2k14 — Mon, 28 Sep 2020 10:54:03 GMT

Hi Friends,

I came across CREATE LOCAL TEMPORARY TABLE and found that we can do delete and update operations on it whereas we cannot do any modification to the table created by CREATE LOCAL TEMPORARY COLUMN TABLE.

which one is better for usage in the SQL stored procedure and why? What if I always use CREATE LOCAL TEMPORARY TABLE? Is there any disadvantage?

Thanks,

Gaurav

Re: Difference between CREATE LOCAL TEMPORARY COLUMN TABLE and CREATE LOCAL TEMPORARY TABLE

Sz_An — Tue, 29 Sep 2020 08:43:07 GMT

Hi Gaurav,

Local temporary tables created using the CREATE LOCAL TEMPORARY TABLE statement remain until they are either explicitly dropped, or until the connection closes. Local temporary tables created in IF statements using CREATE LOCAL TEMPORARY TABLE also persist after the IF statement completes.
Tables created using CREATE LOCAL TEMPORARY TABLE do not appear in the SYSTABLE view of the system catalog.

I would suggest checking KBA 2800007 - FAQ: SAP HANA Temporary Tables for further details on creating the different types of temporary tables on SAP HANA. It also states typical errors and bugs regarding temporary tables.
For "CREATE LOCAL TEMPORARY TABLE", a known bug is identified and it is described by SAP Note 2568196 - CREATE LOCAL TEMPORARY TABLE Statement Gets Rolled Back by ROLLBACK.

Best Regards,
Anna Szalay

Re: Difference between CREATE LOCAL TEMPORARY COLUMN TABLE and CREATE LOCAL TEMPORARY TABLE

Sz_An — Tue, 29 Sep 2020 08:44:23 GMT

Hi Gaurav,

Thank you for visiting SAP Community to get answers to your questions.

If you're hoping to connect with readers, please consider adding a picture to your profile as it encourages readers to respond to you. Here's how to do it: https://www.youtube.com/watch?v=F5JdUbyjfMA&list=PLpQebylHrdh5s3gwy-h6RtymfDpoz3vDS.

Since you asked a question, don't forget to mark the answer that helped you most and is correct.

Best Regards,
Anna Szalay

Re: Difference between CREATE LOCAL TEMPORARY COLUMN TABLE and CREATE LOCAL TEMPORARY TABLE

kgaurav2k14 — Tue, 29 Sep 2020 09:30:13 GMT

Hi Anna,

Thanks for your reply. I think you misunderstood my question, may be I didn't put it the right way. Let me rephrase it. I want to know the difference between the temporary tables created by

1. CREATE LOCAL TEMPORARY COLUMN TABLE

AND

2. CREATE LOCAL TEMPORARY TABLE

if you note that 2nd table is not a column table. However, we can do update and delete operation on this 2nd table which we cannot do it in temporary column table. I want to know the usage level information on which one to use when and why SAP has given update and delete operation capability to table created by CREATE LOCAL TEMPORARY TABLE and not created by CREATE LOCAL TEMPORARY COLUMN TABLE statement.

Thanks,

Gaurav

HDB 2.0 SP3

Re: Difference between CREATE LOCAL TEMPORARY COLUMN TABLE and CREATE LOCAL TEMPORARY TABLE

Vitaliy-R — Wed, 30 Sep 2020 15:17:50 GMT

Hi Gaurav. Seems there is an issue with the 2.0 SPS3 documentation because it says that the COLUMN table should be created by default, but I checked and indeed it is ROW table created in case of LOCAL TEMPORARY...

CREATE LOCAL TEMPORARY COLUMN TABLE "#TEST_TABLE1" (ID INTEGER, NAME VARCHAR(10)); 
CREATE LOCAL TEMPORARY  TABLE "#TEST_TABLE2" (ID INTEGER, NAME VARCHAR(10)); 
CREATE LOCAL TEMPORARY ROW TABLE "#TEST_TABLE3" (ID INTEGER, NAME VARCHAR(10)); 


SELECT TABLE_NAME, "IS_COLUMN_TABLE" FROM M_TEMPORARY_TABLES
WHERE TABLE_NAME LIKE '%TEST%'
ORDER BY 1;

returns

TABLE_NAME  |IS_COLUMN_TABLE|
------------|---------------|
#TEST_TABLE1|TRUE           |
#TEST_TABLE2|FALSE          |
#TEST_TABLE3|FALSE          |

And per https://help.sap.com/viewer/4fe29514fd584807ac9f2a04f6754767/2.0.03/en-US/20d58a5f75191014b2fe92141b7df228.html?q=%22LOCAL%20TEMPORARY%20ROW%20TABLE%22 indeed row temporary table allows you to do Delete/Update/Upsert, that is not possible on column temporary table.

The description was fixed in the documentation for SP 04: "If neither ROW nor COLUMN is specified, the default ROW for temporary tables (local and global), and COLUMN for non temporary tables." https://help.sap.com/viewer/4fe29514fd584807ac9f2a04f6754767/2.0.04/en-US/20d58a5f75191014b2fe92141b7df228.html