Similarities and differences between temporary tables in Oracle and SQL Server databases

Common applications:

Temporary tables play an important role in database design.

For example, we can use the temporary table function to restrict multiple logins of the same user name to the same system. For example, there is a financial management system, and enterprises want to log on only once for the same user at the same time. This is mainly used to restrict each employee to log on with his/her username and password. The purpose of this restriction is to find the owner of each document in the financial management system.

Original solution:

In the previous database design, some people did not use temporary tables for such restrictions, but used real database tables to register relevant information. For example, there is a column in the user information table specifically used to record the current login status of the user. After a user logs on to the system, the user's login Status field is changed to Y. After the user exits the system, the content of this field is changed to N. This solution seems feasible, but it has a very large vulnerability in practical application. If a user logs on to the system and the terminal crashes due to various reasons, such as viruses or power outages, the user does not log on to the system, the user information table in the financial management system shows that the login status of the user is still Y. If a user attempts to log on to the Financial Management System, the user is rejected by the system. The system considers that the user has logged on to the system and cannot log on to the system again.

Therefore, a management vulnerability exists when the entity table is used to record user login information.

Use a temporary table to restrict user re-login:

Later, database designers thought, could they record the user login information in a temporary table? When a user ends a session, the contents of the temporary table are cleared after the user ends a session normally or unexpectedly.

If combined with this requirement, the Database Designer hopes to implement the following functions.

After a user logs on to the system to start a session, the database system creates a temporary table with at least one content, that is, the user account (or the corresponding ID of the account ). When other users log on to the system, the system first queries from this temporary table to check whether the same user records exist. If yes, the user will be rejected and the user will be warned that the same user has logged on. When the user exits the system normally or ends the current session due to other reasons, the database system will clear the contents of this table. In this way, you can log on to the system even if you log on to the system again.

It can be seen that temporary tables of Databases play an important role in the actual application of enterprises.

Both the ORACLE database and the SQL SERVER database implement the temporary table function. However, there are differences between the two methods. Different implementations give them different features. This is one aspect that we have to pay attention to in database selection.

Implementation of temporary tables for two databases:

The difference between SQL SERVER temporary tables and ORACLE temporary tables can be summarized in one sentence. SQL SERVER temporary tables are created when they are needed. ORACLE Database temporary tables are created during database initialization and operated upon a specific session or transaction, after a session ends or a transaction ends, the database content is cleared.

1. Similarities and Differences during creation.
Temporary tables in the SQL SERVER database are created as needed. Specifically, you can use the SELECT statement and CREAT statement to create a temporary table. For example, you can use SELECT * INTO # USER_TEMP from user; with this statement, you can create a temporary table at the required time. You can also use the CREATE statement to CREATE a temporary table as needed.

While the ORACLE database requires a temporary table during database system initialization. That is, when a user installs the financial management software system and initializes the database system, the system creates a temporary table. Instead of being created when a temporary table is needed. Therefore, there is only one method for creating temporary tables in the ORACLE database. During database initialization, CREATE a temporary table in the database. Therefore, there is another saying about temporary tables in ORACLE databases. We generally say that the temporary table of the ORACLE database is permanent, but the content of the temporary table is temporary. You only need to call the temporary table directly when you need it, instead of creating it temporarily. Unlike the SQL SERVER database, this temporary table is created only when it is used. When the session ends, not only the data in the table is cleared, but also the table is deleted.

Author's comment:

I prefer the implementation of temporary tables in ORACLE databases. Why? As we all know, database definition languages, such as CREATE, occupy system resources. If the front-end programs frequently use the create and other database definition languages to CREATE temporary tables during the database SQL SERVER database system design process, it will have a great negative impact on the running efficiency of the SQL server database system. In addition, each operation will have a similar adverse effect, this is because a temporary table is created every time you run it. In the ORCLE database, the CREATE statement is used during system initialization. Therefore, the performance may be affected only during system initialization. In the future database operation, it will not be difficult for this to always run the CREATE statement. Therefore, I personally prefer to use the temporary table Processing Solution of the ORACLE system.