SQL Server temporary table

post 1:

temporary tables are similar to permanent tables, but temporary tables are stored in tempdb and 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 the database session creates a local temporary table # employees, the table can be used only 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.

Post 2:

You can create local and global temporary tables. The local temporary table is only visible in the current session; the global temporary table is visible in all sessions. The name of the local temporary table is preceded by an identifier (# Table_name ), While the name of the global temporary table has two numbers before it (##Table_name ). In the create table statement Table_name The specified name references the temporary table: Create Table # mytemptable (COLA int primary key) insert into # mytemptable values (1) If a local temporary table is created by a stored procedure or is executed by multiple users at the same time Program SQL Server must be able to differentiate the tables created by different users. Therefore, SQL Server adds a digital suffix to the table name of each local temporary table internally. Stored inTempdb Database Sysobjects The full name of a temporary table in a table is composed of the table name specified in the create table statement and the digital suffix generated by the system. The table name specified for the local temporary table to allow append suffix Table_name It cannot exceed 116 characters.
Unless the drop TABLE statement is used to explicitly remove a temporary table, the system automatically removes the temporary table when it exits its scope:

• When the stored procedure is complete, the local temporary table created in the stored procedure is automatically removed. All nested stored procedures executed by the stored procedure of the created table can reference this table. However, the process that calls the stored procedure to create this table cannot reference this table.
• All other local temporary tables are automatically removed at the end of the current session.
• The global temporary table is automatically removed when the session for this table is created and other tasks are stopped to reference it. The association between tasks and tables is only maintained during the lifecycle of a single Transact-SQL statement. In other words, when the session for creating a global temporary table ends, the table is automatically removed after the last Transact-SQL statement that references the table is completed.

the local temporary table created in the stored procedure or trigger is different from the temporary table created before the stored procedure or trigger is called. If a query references a temporary table with two temporary tables with the same name at the same time, it does not define which table to resolve the query. A nested stored procedure can also create a temporary table with the same name as the temporary table created by calling its stored procedure. All references to table names in nested stored procedures are interpreted as tables created for this nested procedure, for example:

Create procedure Test2 as create table # T (x int primary key) insert into # T values (2) select test2col = X from # t go create procedure test1 as create table # T (x int primary key) insert into # T values (1) select test1col = X from # T exec Test2 go create table # T (x int primary key) insert into # T values (99) Go exec test1 go
 

The following is the result set:

(1 row (s) affected) test1col ----------- 1 (1 row (s) affected) test2col ----------- 2
 

When creating a local or global temporary table, the create table syntax supports all the constraint definitions except the foreign key constraint. If the foreign key constraint is specified in the temporary table, the statement returns a warning message indicating that the constraint has been ignored and the table will still be created, but it does not have the foreign key constraint. Temporary tables cannot be referenced in the foreign key constraint.

Use table variables instead of temporary tables. A temporary table is useful when you need to create an index explicitly on a temporary table or use table values for multiple stored procedures or functions. Generally, table variables provide more effective query processing.