SQL Server temporary table

Post 1: 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.

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 a number character (# table_name), and the name of the global temporary table is preceded by two numbers (# table_name ).

The SQL statement uses the name specified for table_name In the CREATE TABLE statement to reference 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 an application executed by multiple users at the same time, 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 inTempdbDatabaseSysobjectsThe 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. To allow append suffixes, the table name table_name specified for the local temporary table 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.

Consider using 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.

 

Post 3

SQL Server temporary table utilities

1. SQL Server temporary tables Old Blue Notes: Introduction: Temporary data tables are often stored. The client can use the clientdataset memory table of Delphi. However, clientdataset is similar to a table and does not support the SQL language. You can also use temporary tables. Sometimes we do not need to worry about the life cycle of a temporary table, but use the actual table for temporary use. Tell us what tempxxx is like. They are temporary tables. There is no problem with creating and releasing. So what is the period of a temporary table? Is there any special usage? 1. The difference between a global temporary table and a temporary table: The field of view is different. A global temporary table is of course a table with # headers. A common temporary table is # headers. Their cycle should be born with the birth of a connection, that is, the connection, and die with the disconnection of the connection. The difference is that the field of view is different. Global table, all authorized connections can be seen. However, only the connection created for the common temporary table (local temporary table) can be seen. In particular, each connection of isql.exe in SQL Server is a connection. For example, if an application app.exe connects to SQL Server data only through adoconnection, the corresponding table created by the adoqueryworkflow in this connection can be used by another app.exeits appx.exe. Others or others cannot be seen. Trap: if an application app.exe has a stored procedure connected to the same adoconnection as tadoquery, remember to use the temporary table created by the Stored Procedure (not global) and cannot be accessed by tadoquery under the connection. The reason is that the stored procedure itself is executed on the server and should be the connection of the server. 2. Store and create a global or temporary table. We all know that temporary tables or Global tables are stored in SQL Server's tempdb database. We use them when accessing tables #, however, in the process, user tables in tempdb are stored in the format of # XXX _________ processing identifier XXX (whether the user table is a process ID is unknown or not ). You can see them through the tempdb of isql.exe. 3. Headache initialization table Initialize the table, why is it a headache? After all, use drop or something. Or it is difficult to judge. In fact, it is also very simple. In addition, creating temporary tables is boring SQL statements. How can this problem be solved. Method 1: Use the stored procedure. You can directly write SQL statements or exec (@ v_ SQL ). Method 2: Save the temporary table to memo in the data, read the table, and execute it directly. Method 1: Common exists functions can be used. For example: if exists (select * From tempdb .. sysobjects where id = object_id (@ stmpwarea) and type = "U ") Method 2: If object_id ('tempdb. DBO. # '+ @ v_userid) is null Method 1: (thanks to Aman, confused) A statistical temporary table that uses tadoproc to execute the stored procedure, as follows: Create procedure initcreatecoawardtable Begin If not (object_id ('tempdb. DBO. # tmp_detail ') is null) Drop table tempdb. DBO. # tmp_detail Create Table tempdb. DBO. # tmp_detail ( [ID] [int] identity (1, 1) not null, [Document category] [varchar] (20) Collate chinese_prc_ci_as null, [Document No.] [varchar] (20) Collate chinese_prc_ci_as null, (.. All the complexity here can be ..... Omitted ...) Select * From tempdb. DBO. # tmp_detail End Go Run the stored procedure using tadoproc on the client, With sproc do Begin Try Close; Procedurename: = spname; Parameters. Refresh; Prepared; Execproc; Open; // This statement cannot be omitted. If the database name is ignored, the object in tempdb will be referenced. Except Close; Exit; End; Result: = true; End; In this way, your tadoproc can use the append and insert statements at will. It is better that you do not need to clear the statements, and the method is flexible. It is a bit more effective in processing concurrency. Of course, some people are still using the actual table to replace it, and you have to recycle it when using sessions to solve the problem. Very troublesome. And the flexibility is much lower.   The following is an example of an application reprinted: Use the global temporary table of SQL Server to prevent repeated logins When developing business software, we often encounter the following problem: how to prevent users from repeatedly logging on to our system? Especially for banks or financial departments, it is necessary to restrict users to log on multiple times as their employee IDs. Some people may say that they should add a field in the user information table to determine the logon status of the user's employee ID, write 1 after logon, write 0 when exiting, and determine whether the flag is 1 when logon, if yes, the user's employee ID is not allowed to log on. However, this will inevitably lead to new problems: in the case of unpredictable phenomena such as power outages, the system exits abnormally and cannot set the mark position to 0, so next time you log on with this user's employee ID, you cannot log on. What should I do? Maybe we can change our thinking: What can be automatically recycled by the system after the connection is disconnected? By the way, SQL Server temporary tables have this feature! However, in this case, we cannot use a local temporary table. Because a local temporary table is an independent object for each connection, we can only use a global temporary table for our purpose. Now, the situation is clear. We can write a simple stored procedure like the following:   Create procedure gp_findtemptable -- 2001/10/26 21:36 zhuzhichao in Nanjing /* Search for a global temporary table named after the operator's employee ID * If not, set the out parameter to 0 and create the table. If yes, set the out parameter to 1. * After the connection is disconnected, the global temporary table is automatically reclaimed by SQL Server. * In the event of an accident such as power failure, although the global temporary table still exists in tempdb, it is no longer active. * When the object_id function is used to determine whether the object does not exist, it is considered as nonexistent .*/ @ V_userid varchar (6), -- operator ID @ I _out int out -- output parameter 0: No Logon; 1: logged on As Declare @ v_ SQL varchar (100) If object_id ('tempdb. DBO. # '+ @ v_userid) is null Begin Set @ v_ SQL = 'create table # '+ @ v_userid +' (userid varchar (6 ))' Exec (@ v_ SQL) Set @ I _out = 0 End Else Set @ I _out = 1 In this process, we can see that if the global temporary table named after the user's employee number does not exist, a new table will be created and the out parameter is set to 0, if it already exists, set the out parameter to 1. In this way, when we call this process in our application, if the out parameter obtained is 1, we can jump out of a message and tell the user to say "sorry, this employee ID is in use!" Sample judgment method: Select @ stmpwarea = "tempdb .. [# marwarea" + @ computername + "]" If exists (select * From tempdb .. sysobjects where id = object_id (@ stmpwarea) and type = "U ") Begin Set @ stmpwarea = "[# marwarea" + @ computername + "]" Exec ("Drop table" + @ stmpwarea) End Else Set @ stmpwarea = "[# marwarea" + @ computername + "]"