Brief discussion on SQL Server temporary table

Temporary tables are divided into local and global, with # Start and # #开头
#开头的临时表只能在本回话中使用, the temporary table created when the reply is finished is deleted
# #开头的临时表可以在所有回话中使用, will be deleted when SQL Server is restarted

Whether it's the beginning or the # #开头的临时表都存在tempdb数据库中

We can test with the following statement

Create Table nvarchar (ten))

Open tempdb we can see the table we just created when we close the reply, we refresh the tempdb table, we can still see # #aa, when we restart the SQL Server service we see that the table does not exist, and tempdb is back to the original state.

And then we'll get rid of a # try

    Create Table nvarchar (ten))

We open two answers and enter the above code execution, and we also open the tempdb table after execution,

We can see the #aa table we just created, and SQL Server adds a different suffix after the #aa according to our different answers, now we close the reply, refresh the tempdb table again, and we can see that two temporary tables have been deleted.

Creation of temporary tables

Temporary tables can be created using the CREATE table when they are actually used, such as the above code; We can also create temporary tables with the SELECT INTO statement, for example

Select  into  from table1

We know that the global temp table can be used in all of the reply, so it's best to determine if the temporary table exists before creating it.

Deletion of temporary tables

We can delete the temporary table by

Drop Table # #aa Drop Table #aa

The way to remove

Temporary table use

Temporary tables in the actual development process when we need to store some temporary calculations in the process, and there is no actual table available to you when available, temporary table for developers to provide a great convenience;

The use of temporary tables can reduce the complexity of the statement, when a lot of tables are associated, we can reduce the size of the data stored in the temporary table, in the temporary table to be associated with other tables, this can improve performance, but it is important to note that when the amount of data is large, it is not a wise choice to store in the temporary table, We have to use the temporary table reasonably according to the actual situation.

Brief discussion on SQL Server temporary table