Differences between temporary Oracle tables and temporary SQL Server tables

The process of creating temporary tables in the Oracle database and comparing the differences with the temporary tables in SQL Server are the main content of this article. Let's take a look at this part together. I hope it can help you. help.

1 Introduction

In addition to storing permanent tables, the Oracle database can also create temporary tables. These temporary tables are used to hold data for a session session, or to store data needed in a transaction. When the session exits or the user submits a commit and rollback transaction, the data in the temporary table is automatically cleared, but the structure and metadata of the temporary table are also stored in the user's data dictionary.

2. Detailed introduction

Oracle temporary tables are divided into session-level temporary tables and transaction-level temporary tables.

Session-level temporary tables refer to the fact that the data in the temporary table exists only during the life cycle of the session. When the user exits the session, Oracle automatically clears the data in the temporary table.

Transaction-level temporary tables refer to the fact that the data in the temporary table exists only during the life cycle of the transaction. When a transaction ends (commit or rollback), Oracle automatically clears the data in the temporary table.

The data in the temporary table is only valid for the current session. Each session has its own temporary data and cannot access the data in the temporary tables of other sessions. Therefore, temporary tables do not require DML locks.

When a session ends (the user exits normally and the user abnormally exits the Oracle instance crashes) or a transaction ends, Oracle executes a TRUNCATE statement on the session table to clear the temporary table data. But it does not clear the data in other session temporary tables.

You can index temporary tables and build views based on temporary tables. Similarly, indexes created on temporary tables are also temporary and only valid for the current session or transaction. Temporary tables can have triggers.

3. Create a temporary table

The definition of the temporary table is visible to all sessions, but the data in the table is only valid for the current session or transaction.

Build method:



1) ON COMMIT DELETE ROWS defines a method to create a transaction-level temporary table.
CREATE GLOBAL TEMPORARY TABLE admin_work_area
(startdate DATE,
enddate DATE,
class CHAR (20))
ON COMMIT DELETE ROWS;
EXAMPLE:
SQL> CREATE GLOBAL TEMPORARY TABLE admin_work_area
2 (startdate DATE,
3 enddate DATE,
4 class CHAR (20))
5 ON COMMIT DELETE ROWS;
SQL> create table permernate (a number);
SQL> insert into admin_work_area values (sysdate, sysdate, 'temperary table');
SQL> insert into permernate values (1);
SQL> commit;
SQL> select * from admin_work_area;
SQL> select * from permernate;
A
1 
2) ON COMMIT PRESERVE ROWS defines a method for creating session-level temporary tables.
CREATE GLOBAL TEMPORARY TABLE admin_work_area
(startdate DATE,
enddate DATE,
class CHAR (20))
ON COMMIT PRESERVE ROWS;
EXAMPLE:
Session 1:


SQL> drop table admin_work_area;
SQL> CREATE GLOBAL TEMPORARY TABLE admin_work_area
2 (startdate DATE,
3 enddate DATE,
4 class CHAR (20))
5 ON COMMIT PRESERVE ROWS;
SQL> insert into permernate values (2);
SQL> insert into admin_work_area values (sysdate, sysdate, 'session temperary');
SQL> commit;
SQL> select * from permernate;
A
----------

SQL> select * from admin_work_area;
STARTDATE ENDDATE CLASS
---------- ---------- --------------------
17-1 ?? -03 17-1 ?? -03 session temperary
Session 2:


SQL> select * from permernate;
A
----------

SQL> select * from admin_work_area;
No rows selected.
Session 2 cannot see the data of the temporary table in session 1.

4. Oracle temporary table and SQL Server temporary table similarities and differences

SQL Server can also create temporary tables. Temporary tables are similar to permanent tables, but temporary tables are stored in tempdb and are automatically deleted when they are no longer used.

There are two types of temporary tables, local and global, which differ in name, visibility, and availability. The names of local temporary tables begin with a single number sign (#); they are visible only to the current user connection; they are deleted when the user disconnects from the Microsoft SQL Server instance. The name of the global temporary table begins with a mathematical symbol (##), is visible to any user after creation, and is deleted when all users referencing the table are disconnected from SQL Server.

The SQL statement references the temporary table using the name specified for table_name in the CREATE TABLE statement:


CREATE TABLE #MyTempTable (cola INT PRIMARY KEY)
INSERT INTO #MyTempTable VALUES (1)
If a local temporary table is created by a stored procedure or an application that is executed by multiple users at the same time, SQL Server must be able to distinguish between tables created by different users. To do this, SQL Server internally appends a numeric suffix to the table name of each local temporary table. The temporary table stored in the sysobjects table of the tempdb database, whose full name consists of the table name specified in the CREATE TABLE statement and a system-generated numeric suffix. To allow appending suffixes, the table name table_name specified for the local temporary table cannot exceed 116 characters.

Unless the temporary table is explicitly removed using the DROP TABLE statement, the temporary table is automatically removed by the system when it exits its scope:

When the stored procedure completes, local temporary tables created in the stored procedure are automatically removed. This table can be referenced by all nested stored procedures executed by the stored procedure that created the table. However, the process calling the stored procedure that created this table cannot reference this table.

All other local temporary tables are automatically dropped at the end of the current session.

Global temporary tables are automatically dropped when the session that created the table ends and other tasks stop referencing it. The association between tasks and tables is only maintained for the lifetime of a single Transact-SQL statement. In other words, when the session to create a global temporary table ends, the table will be automatically removed after the last Transact-SQL statement that references the table is completed.

For example, if you create a table named employees, anyone can use the table as long as they have security permissions to use the table in the database, unless it is deleted. If you create a local temporary table named #employees, only you can perform operations on the table and the table is dropped when you disconnect. If you create a global temporary table named ## employees, any user in the database can perform operations on the table. If the table is not used by other users after you create it, the table is deleted when you disconnect. If the table is used by other users after you create it, SQL Server deletes the table after all users disconnect.

A local temporary table created in a stored procedure or trigger is different from a temporary table of the same name that was created before the stored procedure or trigger was called. If a query references a temporary table and there are two temporary tables with the same name, it is not defined which table the query is parsed against. A nested stored procedure can also create a temporary table with the same name as the temporary table created by the stored procedure that called it. All references to the table name in a nested stored procedure are interpreted as referring to the table created by the nested procedure, for example:


CREATE PROCEDURE Test2
AS
CREATE TABLE #t (x INT PRIMARY KEY)
INSERT INTO #t VALUES (2)
SELECT Test2Col = x FROM #t
GO
CREATE PROCEDURE Test1
AS
CREATE TABLE #t (x INT PRIMARY KEY)
INSERT INTO #t VALUES (1)
SELECT Test1Col = x FROM #t
EXEC Test2
GO
CREATE TABLE #t (x INT PRIMARY KEY)
INSERT INTO #t VALUES (99)
GO
EXEC Test1
GO
Here is the result set:


(1 row (s) affected)
Test1Col
-----------
  
(1 row (s) affected)
Test2Col
-----------

When creating local or global temporary tables, the CREATE TABLE syntax supports all constraint definitions except FOREIGN KEY constraints. If a FOREIGN KEY constraint is specified in a temporary table, the statement returns a warning message stating that the constraint has been ignored and the table is still created but does not have a FOREIGN KEY constraint. You cannot reference temporary tables in a FOREIGN KEY constraint.

Consider using table variables instead of temporary tables. Temporary tables are useful when indexes need to be explicitly created on temporary tables, or when multiple stored procedures or functions require table values. In general, table variables provide more efficient query processing.

Differences from Oracle:

1. SQL Server temporary table is a kind of "memory table", the table is stored in memory. ORACLE temporary table unless the DROP TABLE is executed, the table definition will remain in the data dictionary.

2. SQL Server temporary tables do not exist similar to ORACLE temporary table transaction level functions.

3.SQL Server local temporary table (#) is similar to ORACLE's session-level temporary table, but ORACLE will not delete the table when the session exits.

4.SQL Server's global temporary table (##) refers to multiple connections sharing the same piece of memory. When no pointer refers to this memory area, SQL Server automatically releases the global temporary table.

5. Because Oracle is not a database in memory. If Oracle frequently creates and deletes temporary tables like SQL Server, it will definitely affect performance. So Oracle will retain the definition of temporary tables until the user DROP TABLE.

6. In Oracle, if multiple users need to share a table (similar to SQL Server's global temporary table ##), you can take advantage of the permanent table, and add some columns in the table that can uniquely identify the user. Use triggers and views When the user logs out, delete the data in the corresponding table according to the unique information of the logged-in user. This method brings a certain amount of load to Oracle.

The relevant knowledge about the differences between the Oracle temporary table and the SQL Server temporary table is introduced here, I hope this introduction can be useful for you!