Menu
Can someone please tell me: how long will data be there in a Global Temporary table?
521
Temporary tables can have a Time Travel retention period of 1 day; however, a temporary table is purged once the session (in which the table was created) ends so the actual retention period is for 24 hours or the remainder of the session, whichever is shorter.
Local temporary tables are deleted after the user disconnects from the instance of SQL Server. Global temporary tables are visible to any user and any connection after they are created, and are deleted when all users that are referencing the table disconnect from the instance of SQL Server.
Global Temporary Tables (GTTs) are the Oracle tables, having data type as private; such that data inserted by a session can be accessed by that session only. The session-specific rows in a GTT can be preserved for the entire session, as AI report tables are created using ON COMMIT PRESERVE ROWS clause.
There are two varieties of temp tables. Local temp tables are only accessible from their creation context, such as the connection. Global temp tables are accessible from other connection contexts. Both local and global temp tables reside in the tempdb database.
They can be SESSION based (data survives a commit but not a disconnect/reconnect). They can also be TRANSACTION based (data disappears after a commit).
This creates a transaction based temp table:
create global temporary table temp_table_transaction on commit delete rows ...
This creates a session based temp table:
create global temporary table temp_table_transaction on commit preserve rows ...
When you create a temporary table you have two options for data persistence:
ON COMMIT DELETE ROWS (default) and ON COMMIT PRESERVE ROWSIf you don't specify a persistence clause, or specify ON COMMIT DELETE ROWS, the data in the table will be transaction-specific (it will be deleted upon commit or rollback).
If you specify ON COMMIT PRESERVE ROWS, the data will stay until the end of your session.
37
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
