Notes for using temporary tables in a T-SQL

1. Two forms of temporary tables.

In SQL Server databases, temporary tables are classified into global temporary tables and local temporary tables. These two types of tables are very different, mainly in terms of name, visibility, and availability. Specifically, the name of the local temporary table starts with the # symbol, while the global temporary table starts with the # symbol. In terms of visibility, a local temporary table is created by the current user and can be accessed only by the Session of the current user. For a global table, as long as the temporary table exists, the session is visible to all users after the user creates the session. The two are also different at the time of deletion. For example, the local temporary table will be deleted after the session is interrupted by the current user. The global temporary table is deleted only when the user who drinks the table is disconnected from the database. Because there are so many differences between the two temporary tables, the database administrator needs to determine the appropriate temporary table type based on the actual application.

Now I will take a practical example to talk about the differences between common tables, local temporary tables, and global temporary tables. For example, there is a user table that stores employee information. This table is a common table and will not be automatically deleted as long as it is created. any user who uses this table (with access permission) in the database can access this table, unless the table is deleted by the owner or the permission is changed. When user A (with access permissions) accesses this table, the database may generate A local temporary table # user as needed. Only this session can access this local temporary table. When the user's session is interrupted, the local temporary table will be automatically deleted. However, as needed, the database may also create a global temporary table # user (the name is different from the local temporary table ). In this case, any user in the database can access the global temporary table as long as they connect to the database (different access permissions ). When the user who creates a temporary table session breaks the database connection, it is unknown whether the temporary table will be deleted. This depends on the actual situation at the time (in terms of availability ). If other users connect to this table, the global temporary table will not be deleted. When the connection is interrupted, no other user accesses this table, that is, a user (not necessarily the user who creates this global temporary table) the table is deleted only when the table is disconnected and no other sessions use the table.

As you can see, whether it is a global temporary table or a local temporary table, the most important difference is that it will be automatically created as needed. It is automatically deleted when it is no longer needed. This is also the charm of temporary tables, which can reduce many intermediate tables in the process of data processing.

Ii. Benefits of using temporary tables.

The benefit of using temporary tables in T-SQL languages is obvious. I will introduce some common advantages below.

For example, using a temporary table to organize data is more concise and compact than a common table. This mainly enables many features in temporary tables. For example, preprocessing can be performed. If you find that the index in the basic tag is not suitable, you can also create an index in the temporary database table to optimize the original index. Especially when you need to access a table or view multiple times, using a temporary table to organize data is a good way to improve efficiency. Even if it is just a simple query, the efficiency improvement is obvious. To this end, the most obvious advantage of using temporary tables seems to improve the database performance, especially the query performance.

In addition, the use of temporary tables can also reduce the generation of intermediate tables. When performing some operations, some intermediate tables are often needed for completion. Now, the database administrator can enable the database to automatically generate intermediate tables and delete them when necessary. In this case, the establishment and deletion of intermediate tables do not need to be managed by the database administrator. Therefore, using temporary tables can reduce junk tables in the database system and reduce the user's workload. Therefore, I believe that temporary tables are a very useful tool in SQL Server databases. As a database administrator, you must properly use this temporary table in your daily work to maximize its effectiveness. Although there are many confusing types of temporary tables for specific tasks. However, the database administrator should not be able to give up and try it out.

3. Pay special attention to local temporary tables.

During normal application and management, there are more applications for local temporary tables than global temporary tables. In addition, the local temporary table can be accessed only by the user's own session, while the global temporary table can be accessed by all users. Therefore, local temporary tables are much higher than global temporary tables in terms of security. Therefore, the author believes that the database administrator should master the application skills of local temporary tables. Then compare the information of the global temporary table, which may be a shortcut to learning the temporary table of the SQLServer database.

For a local temporary table, you must note that the local temporary table can be deleted in different situations. Assume that the database creates a local temporary table when executing a stored procedure. In this case, the local temporary table is not automatically deleted when the session ends, but will be deleted after the stored procedure is executed. What does this mean? That is to say, a user initiates a session to execute a special job (for example, the user calls a stored procedure ). In this case, it is the process of creating a sub-session. In this case, note that the local temporary table created by the sub-session is only valid within the sub-session. When this sub-session is terminated (the stored procedure is executed), the temporary table is automatically deleted. That is, for sessions that call this sub-session, the temporary table created for this sub-session is invalid because the temporary table has been automatically deleted when the sub-session is closed. Make an image metaphor. That is to say, the father is now asking his son to build a house. The House will also disappear when the son dies. In this case, the database administrator must pay attention to it. A parent session can only reference data transmitted by a child Session from a temporary table. That is to say, the parent session has only one method to access the data of the temporary table created by the Child session. That is, let the sub-session query or operate on the data in the temporary table, and then return the structure to the parent session. A parent session cannot directly access the temporary table created by a sub-session. Of course, this restriction applies to local temporary tables. For global temporary tables, all users can access them.

Iv. Impact of temporary tables on logs and locks.

Log files are an important tool in the database. Both the SQL Server database and the Oracle database have the log tool. For example, with the redo log tool, the database administrator can recover data to the faulty point when the database fails. However, when using a temporary table, note that the temporary table does not have log files. That is, DML and other operations on temporary tables do not form log files. This feature has both advantages and disadvantages. The advantage is that changes to temporary tables are not saved to the log file. That is to say, if the database fails, the data stored in the temporary table cannot be recovered. Therefore, the database administrator has to re-execute some jobs to regenerate the data in the temporary table. The advantage is that DML operations on temporary tables are extremely fast. In addition to other reasons, it is also an important reason that no log information is generated when the content is changed. Therefore, operations on temporary tables do not generate log information, which is a double-edged sword. In daily work, database administrators should give full play to their advantages to reduce their negative effects.

In addition, if the temporary table processing mechanism is used, you need to pay attention to its impact on the lock. When introducing the differences between the local temporary table and the global temporary table, I have introduced that the local temporary table is only valid for the current session. Even if the current session creates another sub-session, it is only valid for the sub-session. When a session ends, the temporary table is automatically deleted. For a common table or a global temporary table, multiple sessions may access the table at the same time. What is the difference between the two? If multiple sessions are allowed to access a table at the same time, the table may encounter a lock. That is to say, when a user session performs DML operations on records in a table, it locks related records to ensure data consistency. When a local temporary table is used, since only one session can access the data in the temporary table, even if the session changes the data in the temporary table, there will be no lock conflicts. Therefore, you do not need to lock the data in the local temporary table. Therefore, the operation speed of the local temporary table is faster than that of other tables. Therefore, when to use temporary tables can undoubtedly improve the overall performance of the database. For example, you can complete some operations in the temporary table and then update the final result to the basic table.

1. SQL Server database management common SQL and T-SQL statements (1)
2. Interview SQL Server developers with T-SQL operations (1)
3. SQL Server 2008 enhancements to T-SQL language (1)
4. T-SQL in SQL Server 2005
5. T-SQL