SQL Server: Using temporary tables in T-SQL requires attention

When writing triggers or functions using T-SQL, a temporary table is often needed. This article introduces you to the considerations of using T-SQL temporary tables.

One, the temporary table two forms of expression.

In a SQL Server database, there are two main forms of temporary tables, namely, global temporary tables and local temporary tables. The two tables are very different, mainly in names, visibility, and usability. Specifically, the name of the local temporary table begins with the # symbol, while the global temporary table begins with the # #两个 # symbol. For visibility, a local temporary table is created by the current user and is accessible only to the current user's session. For global tables, as long as the temporary table exists, the user creates the session and is visible to all users. The timing of the two deletions is also different. This temporary table is deleted if the local temporary table is interrupted by the current user. A global temporary table is deleted only if the user who is drinking the table is disconnected from the database. Since 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.

The author now takes a practical example to talk about the differences between the three tables of the ordinary table, the local temporary table, and the global temporary table. If you have a table user that holds employee information now. This table is a normal table, as long as it is established will not be automatically deleted, any users in the database with this table (with access) can access the table, unless the table was deleted by the owner or changed permissions. During User A (with access) access to this table, the database may generate a local temporary table #user as needed. Only this session can access this local temp table at this time. When the user's session is interrupted, the local temporary table is also automatically deleted. However, depending on your needs, the database may also establish a global temporary table # #user (different from the local temp table on the name). At this point, any user in the database can access the global temporary table (different on the access rights) as long as they are connected to the database. When the user who created the temporary table session interrupts the database connection, it is an unknown whether the temporary table will be deleted, depending on the actual situation at that time (different in availability). If there are other users connected to the table at this time, then the global temporary table will not be deleted. Only when the connection is disconnected, no other user is removed when the table is accessed, that is, a user (not necessarily the user who created the global temporary table) is disconnected and all other sessions are no longer using the table.

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

II. benefits of using temporary tables.

The benefits of using temporary tables in the T-SQL language are obvious. The author below to give some common benefits.

If the use of temporary tables to organize data, than the normal table will be more concise and compact. This is largely a feature that can be implemented in a temporary table. If preprocessing calculations can be performed. If you find that the index in the base is not appropriate, you can also re-create the index in the database temp table to optimize the original index. Especially when you need to access a table or view more than once, using a temporary table to organize the data is a good way to improve efficiency. Even if only a simple query, the efficiency of the promotion is also very obvious. For this reason, the most obvious benefit of using temporary tables seems to improve database performance, especially query performance.

The use of temporary tables can also reduce the generation of intermediate tables. In some cases, the help of some intermediate tables would have to be done. Now the database administrator can make the database automatically generate the intermediate tables when needed, and delete them automatically when they are used up. In this case, the establishment and deletion of the intermediate table does not require the management of the database administrator. Therefore, using temporary tables can reduce the garbage table in the database system, but also can reduce the user's workload. For this reason, the temporary table is a very used tool in the SQL Server database. As a database administrator, in peacetime work, reasonable use of this temporary table, to play its maximum utility. Although what kind of temporary table to use for specific tasks, there are a lot of confusing places. But the database administrator can not be unworthy, but should actively try.

See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/SQLServer/

Third, special attention should be paid to the local temporary table.

In peacetime application and management, the local temporary table is more likely to be used than the global temporary table. and a local temporary table is accessible to all users, because only the user's own session can be accessed. For this reason, local temporary tables are also much higher in security than global temporary tables. For this reason, the author thinks that the database administrator should master the application skills of the local temporary table. Then compare the information about the global temporary table, which may be a shortcut to learning the SQL Server database temp table.

For local temporary tables, it is important to be aware of the fact that the local temporary table's deletion is applied under different circumstances. Suppose the database establishes a local temporary table when it executes a stored procedure. The local temporary table is not deleted automatically at the end of the session, but is deleted after the stored procedure has finished executing. What does that mean? That is, a user initiates a session in order to perform a special job (such as a stored procedure called by the user's session). This is actually the process of creating a session and then a child session. In this case, it should be noted that the local temporary table created by the child session is valid only within the child session. When the child session terminates (the stored procedure completes), the temporary table is automatically deleted. That is, for a session that calls this child session, the temporary table for which the child session was created is not valid for it, because the temporary table has been automatically deleted when the child session is closed. Make an image of the metaphor. Now the father is going to ask his son to build a house. When his son died, the house would also disappear. For this situation, the database administrator needs to be aware. A parent session can only reference data that is passed from a temporary table by a child session. That is, there is only one way for a parent session to access data for a temporary table created by a child session. This means that the child session queries or operates on the data in the temporary table, and then passes the structure back to the parent session. A parent session is a temporary table that is created without direct access to a child session. Of course, this restriction is specifically for local temporary tables. For a global temporary table, it is accessible to all users, so there is no limit to this.

The effect of temporary table on log and lock.

Log files are a very important tool in the database. This is the tool for logging, whether it is a SQL Server database or an Oracle database. With the Redo Log tool, the database administrator can use this to recover data in the event of a database failure, and restore the data to the point at which it failed. However, when using temporary tables, it is important to note that temporary tables do not have log files. That is, DML operations on temporary tables do not form a log file. This feature is both good and bad. The benefit is that changes to temporary tables are not saved to the log file. That is, if the database fails, the data saved in the temporary table cannot be recovered. This database administrator has to rerun some jobs to regenerate the data in the temporary table. The advantage is that the DML operation for a temporary table will be a very fast block. There is also an important reason for not generating log information when changing its content, except for other reasons that cause its performance to rise. The operation of this temporary table does not generate log information, which is a double-edged sword. In the daily work, the database administrator should exert its advantages and reduce the influence of its negative effect.

In addition, the use of temporary table this processing mechanism, but also need to pay attention to its impact on the lock. When describing the difference between a local temporary table and a global temporary table, the author describes that a local temporary table is only valid for the current session. Even if the current session creates another session, it is only valid for the child session. When a session terminates, the temporary table is automatically deleted. For a normal table or a global temporary table, this table may be accessible to multiple sessions at the same time. What's the difference between the two? If you allow multiple sessions to access a table at the same time, the table may experience a lock. In order to ensure the consistency of the data, a user's session will lock the related records and so on, when the records of the table are DML. In the case of a local temporary table, because only one session can access data in a temporary table, there is no problem with locking conflicts even if the session changes data in the temporary table. Therefore, it does not have to lock the data in the local temporary table when it is changed. As a result, the local temporary table is faster to operate than the other tables. Therefore, when the use of temporary tables can undoubtedly improve the overall performance of the database. If you can complete some operations in a temporary table, then update the final results into the base table.