|
|
Posted on 5/28/2020 10:52:28 AM
|
|
|
|
Temporary tables are very important in the Sqlserver database, and the following is a detailed introduction to the characteristics and use of temporary tables in SQL database, for reference only.
Temporary tables are similar to persistent tables, but temporary tables are stored in tempdb and are automatically deleted when they are no longer in use. There are two types of temporary tables: local and global. They are distinguished by name, visibility, and availability.
The temporary table has the following characteristics:
- Local temporary tables are tables that users add the "#" prefix when creating tables, which are independent according to database connections. Only the database connection that created the local temporary table has access to the table, and other connections cannot access the table.
- In different database connections, although the local temporary tables created have the same "name", these tables do not have any relationship with each other. In SQLSERVER, a special naming mechanism ensures the independence of local temporary tables in database connections.
- True temporary tables take advantage of database temporary tablespace, which is automatically maintained by the database system, thus saving table space. And because the temporary tablespace generally uses virtual memory, the number of I/Os on the hard disk is greatly reduced, so the system efficiency is also improved.
- The temporary table is automatically emptied after the transaction or session is over, so you don't have to remember to delete the data after it runs out.
Local temporary tables
The name of the local temporary table is prefixed with a single numeric symbol (#); They are only visible to the current user connection (i.e. the connection that creates the local temporary table); It is deleted when a user disconnects from a SQL Server instance.
For example, we create a local temporary table in a database connection with the following statement: #Temp
Database Connection 1:
Then start the database connection 2 at the same time to perform the query #temp
Database Connection 2:
Let's see what is the result of database connection 2?
Database Connection 2:
The results show that database connection 2 cannot find table #Temp. This means that the temporary table #Temp is only visible to the database connection 1 that created it, but not to the database connection 2.
Global temporary table
The name of a global temporary table is prefixed with two numeric symbols (##) and is visible to any database connections when created, and is removed when all database connections referencing the table are disconnected from SQL Server.
For example, we create a global temporary table ##Temp in a database connection with the following statement, and then insert three rows of data
Database Connection 1:
Then we query ##Temp的数据 in database connection 2
Database Connection 2:
The database connection 2 result is as follows
Database Connection 2:
As you can see, database connection 2 can successfully access the global temp table ##Temp created by database connection 1, but if we close data connection 1 now and then execute the ##Temp查询语句会发生什么呢 of database connection 2? The results are as follows:
Close database connection 1, and then database connection 2 executes again:
We found that after closing database connection 1, database connection 2 cannot find the global temporary table ##Temp了. This is because after the database connection 1 is closed, there is no statement in the database connection 2 that is using the temporary table ##Temp, so the sqlserver thinks that there is no database connection referencing the global temporary table ##Temp了 at this time, so it will ##Temp释放掉了.
Next, we try to close the global temp table ##Temp持有事务中的排他锁 (X-lock) in database connection 2 and then close database connection 1.
Database Connection 1:
Database Connection 2:
Close database connection 1, and then database connection 2 executes:
The result shows that although we close database connection 1, because database connection 2 has been holding the global temporary table ##Temp的排他锁 (X lock) in the transaction, the temporary table # #Temp并没有随着数据库连接1的关闭而被释放掉, as long as the transaction started in database connection 2 is not rolled back or committed, then database connection 2 will always hold the temporary table ##Temp的排他锁. At this time, Sqlserver will think that there is still a database connection referencing the global temp table ##Temp, so ##Temp不会被释放掉. |
Previous:TypeScript property accessor (set,get)Next:SQL Server queries the previous days of the current system time
|
