Differences between Oracle temporary tables and SQL Server temporary tables:

Differences between Oracle temporary tables and SQL Server temporary tables:

The process of creating a temporary table in the Oracle database and the difference between the temporary table and the SQL Server are described in this article, next, let's take a look at this part of content, hoping to help you.

1. Introduction

In addition to permanent tables, the Oracle database can also create temporary tables. These temporary tables are used to save the data of a SESSION or the data required in a transaction. When the session exits or the user commits a commit or 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 tableIt can be divided into session-level temporary tables and transaction-level temporary tables.

A session-level temporary table means that the data in the temporary table only exists in the session lifecycle. When the user exits the session, Oracle automatically clears the data in the temporary table.

Transaction-level temporary table means that the data in the temporary table only exists in the transaction lifecycle. 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 table of other sessions. Therefore, DML locks are not required for temporary tables.

When a session ends (the user Exits normally and the user does not exit normally and the ORACLE instance crashes) or a transaction ends, Oracle executes the TRUNCATE Statement on the table in the session to clear the temporary table data. however, data in other temporary session tables is not cleared.

You can index a temporary table and create a view on the temporary table. Similarly, the index on the temporary table is temporary and only valid for the current session or transaction. The temporary table can have a trigger.

3. Create a temporary table

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

Creation method:

1) on commit delete rows defines how 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 how to create a session-level temporary table. 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 ---------- 1 2 SQL> select * from admin_work_area; STARTDATE ENDDATE CLASS ---------- ---------- -------------------- 17-1?? -03 17-1?? -03 session temperary 

Session 2:

SQL> select * from permernate; A ---------- 1 2 SQL> select * from admin_work_area; no row is selected.

Session 2 cannot see the data in the temporary table in session 1.

4. Similarities and Differences between Oracle temporary tables and SQL Server temporary tables

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

There are two types of temporary tables: local and global, both of which have different names, visibility, and availability. The name of the local temporary table starts with a single numeric sign (#). They are only visible to the current user connection and are deleted when the user is disconnected from the Microsoft SQL Server instance. The name of the global temporary table starts with a mathematical sign (#). After being created, it is visible to all users. All users who reference this table are deleted when they are disconnected from SQL Server.

The SQL statement uses the name specified for table_name In the CREATE TABLE statement to reference the temporary TABLE:

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 executed by multiple users at the same time, SQL Server must be able to differentiate the tables created by different users. Therefore, SQL Server adds a digital suffix to the table name of each local temporary table internally. The full name of a temporary TABLE stored in the sysobjects table of the tempdb database is composed of the TABLE name specified in the create table statement and the digital suffix generated by the system. To allow append suffixes, the table name table_name specified for the local temporary table cannot exceed 116 characters.

Unless the drop table statement is used to explicitly remove a temporary TABLE, the system automatically removes the temporary TABLE when it exits its scope:

When the stored procedure is complete, the local temporary table created in the stored procedure is automatically removed. All nested stored procedures executed by the stored procedure of the created table can reference this table. However, the process that calls the stored procedure to create this table cannot reference this table.

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

The global temporary table is automatically removed when the session for this table is created and other tasks are stopped to reference it. The association between tasks and tables is only maintained during the lifecycle of a single Transact-SQL statement. In other words, when the session for creating a global temporary table ends, the table is automatically removed after the last Transact-SQL statement that references the table is completed.

For example, if you create a table named employees, anyone who has the security permission to use the table in the database can use the table unless it has been deleted. If you create a local temporary table named # employees, you can only perform operations on the table and delete the table when the connection is disconnected. 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, delete it when you disconnect it. If the table is used by other users after creation, SQL Server deletes the table after all users are disconnected.

The local temporary table created in the stored procedure or trigger is different from the temporary table created before the stored procedure or trigger is called. If a query references a temporary table with two temporary tables with the same name at the same time, it does not define which table to resolve the query. A nested stored procedure can also create a temporary table with the same name as the temporary table created by calling its stored procedure. All references to table names in nested stored procedures are interpreted as tables created for this 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 

The following is the result set:

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

When creating a local or global temporary TABLE, the create table syntax supports all the constraint definitions except the foreign key constraint. If the foreign key constraint is specified in the temporary table, the statement returns a warning message indicating that the constraint has been ignored and the table will still be created, but it does not have the foreign key constraint. Temporary tables cannot be referenced in the foreign key constraint.

Consider using Table variables instead of temporary tables. A temporary table is useful when you need to create an index explicitly on a temporary table or use table values for multiple stored procedures or functions. Generally, table variables provide more effective query processing.

Differences from Oracle:

1. the SQL Server temporary TABLE is a "memory TABLE", which is stored in the memory. ORACLE temporary TABLE. Unless DROP TABLE is executed, the TABLE definition will be kept in the data dictionary.

2. SQL Server temporary tables do not support transaction-level functions similar to ORACLE temporary tables.

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

4. The global temporary table (#) of SQL Server refers to multiple connections sharing the same piece of memory. When no pointer references the memory area, SQL Server Automatically releases the global temporary table.

5. because Oracle is not a database in memory. therefore, if Oracle frequently creates and deletes temporary tables like SQL Server, the performance will be affected. therefore, Oracle retains the definition of the temporary TABLE until the user drops the TABLE.

6. in Oracle, if multiple users need to share a table (similar to SQL Server's global temporary table ##). permanent tables can be used, and columns that can uniquely identify users can be added to the table. trigger and view. when the user exits, the data in the corresponding table is deleted based on the unique information of the login user. this method brings a certain amount of load to Oracle.

This article introduces the differences between temporary Oracle tables and temporary SQL Server tables. We hope this article will help you gain some benefits!