Analyze SQL server temporary table and SQL server

Analyze SQL server temporary table and SQL server

Create a temporary table. # indicates a local temporary table. # indicates a global temporary table. What are the meanings of local temporary tables and global temporary tables?

For example, it is clearer. First, let's look at the local temporary table, [Create a query], and enter the following text in it:

After running, run select * from # tempTable in this file to query the following data:

Open another new query and name it "create query 2". In "create query 2", run the following command: select * from # tempTable. The prompt is as follows:

Modify # tempTable in the SQL statement to # tempTable. Execute the command again according to the preceding method. You will find that no error is prompted in new query 2 and the same results as those in new query 1 can be found. Why? => Local temporary tables are only visible in the current session; global temporary tables are visible in all sessions.

Note: Do not forget to drop the global temporary table. However, if you do not use a local temporary table, the system automatically recycles the created local temporary table at the end of the current session.

For specific usage of temporary tables, see http://www.bkjia.com/article/23952.htm

Where are the temporary tables in SQL Server stored?

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

Temporary tables can be local or global. They differ in terms of name, visibility, and availability. The name of the local temporary table starts with a single digit (#). They are only visible to the current user connection and are deleted when the user is disconnected from the SQL Server instance. The name of the global temporary table starts with two numeric symbols (#). After being created, the table is visible to all users. When all users that reference the table are disconnected from SQL Server, the table is deleted.

For example, if you have created an employees table, you can use the table unless it has been deleted. If a local temporary table # employees is created for a database session, only the table can be used by the session. After the session is disconnected, the table is deleted. If the # employees global temporary table is created, any user in the database can use the table. If the table is not used by other users after you create it, delete it when you disconnect it. If another user is using the table after you create the table, SQL Server will delete the table when you disconnect it and all other sessions no longer use it.

How does sqlserver create a temporary table?

Use a # number before the table name. The temporary table is local and uses two # numbers. The temporary table is global. After the connection is disconnected, the SQL statement automatically deletes the temporary table.
Create table #
(
Id int,
Name varchar (50)
)
Insert into # a (id, name) values (1, '20140901 ')
Select * from #
Drop table #
In addition to the # number before the name, the temporary table has the same operations as the normal table.
Tb_Student is a created table. We can copy the content in the tb_Student table to the tb_lizi table through the temporary table temp, which can be implemented using the following code:
Use OLAP
SELECT * INTO # temp FROM tb_Student
SELECT * INTO tb_lizi FROM # temp
After the execution, disconnect the SQL connection and reconnect (you can also exit the sq and then restart the SQL). It is found that the content in the tb_Student table in the tb_lizi table is completely consistent, and the replication is realized, at the same time, we didn't use code to delete the temp table, but there was no temp table in the apsaradb for MySQL database. This is because the SQL automatically deletes the temp table when the connection is disconnected.