SQL Temp Table

A temporary table is a table whose name begins with a pound sign (#). If the temporary table is not dropped when the user disconnects, SQL Server automatically drops the staging table. Temporary tables are not stored in the current database but are stored in the system database tempdb.
There are two types of temporal tables:
The table name that the local temporary table begins with a pound sign (#). These tables are only visible on connections that create local temporary tables, and temporary tables are deleted when the link is broken (the local temporary table is exclusive to the session that created it) or the local temporary table is created by the current user and is accessible only to the current user's session .

If a local temporary table is created by a stored procedure or an application that is executed concurrently by multiple users (which can actually be considered a different link, a different session), the database engine must be able to differentiate between tables created by different users. To do this, the database engine internally appends a numeric suffix to the table name for each local temporary table. The temporary table stored in the sysobjects table in tempdb , whose full name consists of the table name specified in the CREATE table statement and the system-generated numeric suffix. To allow the append suffix, the table_name specified for the local temporary table cannot exceed 116 characters.

For local temporal tables, it is important to be aware of the fact that local temporary tables are being removed in different situations. Suppose the database establishes a local temporary table when executing a stored procedure. At this point, the local temporary table is not automatically deleted at the end of the session, but is deleted when the stored procedure is finished executing. What does that mean? In other words, a user initiates a session in order to perform a special job (such as a user's session that calls a stored procedure). This is actually the process of creating a child session. In this case, it is important to note that the local temporary table created by the child session is only valid within the child session. When this sub-session is terminated (the stored procedure is completed), the temporary table is automatically deleted. That is, for a session that calls this sub-session, the temporary table created for that child session is not valid for it, because the temporary table is automatically deleted when the child session is closed. Make an image of the metaphor. Now father is going to call his son to build a house. When the son died, the house would disappear. For this scenario, the database administrator needs to be aware. A parent session can only reference data that is passed from a temporary table by a child session. That is, there is only one means for the parent session to access the data of the temporary table created by the child session. That is, the child session first queries or operates on the data in the staging table, and then passes the structure back to the parent session. The parent session is not able to directly access the temporary tables created by the child session. This restriction is, of course, specifically for local temporary tables. For global temporary tables, this is not the limit, as it is accessible to all users.

The Global temporary table names are those that begin with two pound sign (# #). A global temporary table can be seen on all connections, or so long as this global temporary table exists, the user creates the session and is visible to all users. If these tables are not explicitly dropped before the connection to create the global temporary table is dropped, the tables are dropped as long as all other tasks stop referencing them. When the connection to create a global temporary table is broken, new tasks cannot reference them (in other words, how the old task is referenced). As soon as the current statement is executed, the association between the task and the table is dropped, so the global temporary table is typically dropped whenever the connection to create the global temporary table is broken.

Code--Failed----DECLARE @sql varchar Set @sql = ' Select ID as a,name as b into #b from shopname ' Print @sql exec (@sql) s Elect * from #b drop table #b
MSG 208, Level 16, State 0, line 5th object name ' #b ' is not valid.
---Successful-----declare @sql varchar Set @sql = ' Select ID as a,name as b to # #b from Shopname ' Print @sql exec (@sql) sel ECT * FROM # #b drop table # #b//must drop or the error will be given again

Of course, whether it is a global or local table, as long as it can be accessed by the drop table can be forced to delete the temporary table.

When you create a local or global temporary table, the CREATE TABLE syntax supports all constraint definitions except the FOREIGN KEY constraint. If the FOREIGN KEY constraint is specified in the staging table, the statement returns a warning message that the constraint has been skipped. This table is still created, but does not use the FOREIGN KEY constraint. Temporary tables cannot be referenced in the FOREIGN KEY constraint.

Take a practical example of the difference between a normal table, a local temporary table, a global temporary table, and three tables. If you now have a table that holds employee information, user. This table is a normal table, as long as its establishment is not automatically deleted, any good in the database with the use of this table (with access rights) users can access the table, unless the table is deleted by the owner or changed permissions. During User A (with access) to access the table, the database may generate a local temporary table #user as needed. Only this session can access this local temporary table at this time. When the user's session is interrupted, the local temporary table is automatically deleted. However, depending on your needs, the database may also establish a global temporary table # #user (unlike a local temporary table on a name). At this point, any user in the database can access the global temporary table (different access rights) as long as they are connected to the database. When the user who creates the temporary table session interrupts the database connection, whether the temporary table will be deleted is an unknown, depending on the actual situation (different in usability). If there are other users connected to the table at this time, then this global temporary table will not be deleted. Only when a connection is interrupted, no other user accesses the table, that is, a user (not necessarily the user who created the global temporary table) disconnects and all other sessions are no longer using the table to be deleted.

Visible whether it is a global temporary table or a local temporary table, the most important difference is that it is automatically created as needed, compared to a normal table. It is automatically deleted when it is no longer needed. This is also the charm of the temporary table, which can be in the process of data processing, the reduction of many intermediate tables.

Effects of temporal tables on logs and locks

Log files are a very important tool in the database. Both the SQL Server database and the Oracle database have the log tool. With the Redo Log tool, the database administrator can recover data at the time of the database failure and restore the data to the point of failure. However, when using temporal tables, it is important to note that temporary tables do not have log files. That is, DML operations on temporary tables do not form a log file. This feature is good and bad. The benefit is that changes to the temporary table are not saved to the log file. That is, if the database fails, the data saved in the staging table cannot be recovered. For this reason, the database administrator has to re-execute some jobs to regenerate the data in the staging table. The benefit is that DML operations for temporary tables can be very fast-blocking. In addition to the other causes of performance improvements, it is also an important reason to not generate log information when changing its content. This is a double-edged sword that does not generate log information for operations on temporary tables. Database administrators in their daily work, to maximize their advantages, reduce the impact of their negative effects.

In addition, it is necessary to pay attention to the effect of the temporary table on the lock if the processing mechanism is used. When describing the difference between a local temporary table and a global temporary table, the author describes that the local temporary table is only valid for the current session. Even if the current session creates another child session, it is only valid for the child session. When a session terminates, the temporary table is automatically deleted. For normal tables or global temporary tables, this table may be accessible to multiple sessions at the same time. What's the difference between the two? If you allow multiple sessions to access a table at the same time, the table may encounter a lock situation. That is, a user session in the table to record DML and other operations, in order to ensure data consistency, the relevant records will be locked and other measures. In the case of a local temporary table, there is no lock conflict problem even if the session changes the data in the staging table because only one session can access the data in the staging table. Therefore, it is not necessary to lock the data in the local temporary table when changing it. Therefore, the operation of the local temporary table is faster than the other tables. Therefore, when the use of temporary tables can undoubtedly improve the overall performance of the database. If you can complete some operations in a temporary table, then update the final results to the base table.

Prevent users from repeating logins by using a global temporary table of SQL

      When we develop business software, we often encounter such a problem: how to prevent users from repeatedly logging into our system? 　　 　　Especially for banks or financial departments, but also to restrict users to their work number as many times logged in. May be said in the User Information table to add a field to determine the status of the user sign in, write 1 after logging on, write 0 on exit, and log in to determine whether its flag bit is 1, if the user is not allowed to log in. 　　　　But that will inevitably bring new problems: such as the occurrence of unpredictable phenomena such as power outages, the system is not normal exit, unable to mark the location of 0, then the next time the user work number login is not logged in.

Code CREATE PROCEDURE gp_findtemptable/* Find global temporary table named with Operation employee number * If none, set the out parameter to 0 and create the table, if any, set the out parameter to 1 * at connection break 　　After the connection, the global temporary table is automatically reclaimed by SQL Server * such as a power outage, such as an unexpected, global temporary table although it still exists in tempdb, but has been inactive * with the OBJECT_ID function to judge when it is considered to be non-existent. 　　 　　*/@v_userid varchar (6),--operation employee number @i_out int out--output parameter 0: No login 1: Already logged as declare @v_sql varchar (100) If object_id (' tempdb.dbo.## ' [email protected]_userid) is a null begin set @v_sql = ' CREATE TABLE # # ' [Email prote Cted]_userid+ ' (userid varchar (6)) ' EXEC (@v_sql) Set @i_out = 0 end Else Set @i_out = 1

In this process, we see that if the global temporary table named after the user work does not exist, the process will create a sheet and set the out parameter to 0, and if it already exists, set the out parameter to 1. Thus, when we call the procedure in our application, if we get an out parameter of 1 o'clock, we can simply jump out of a message and tell the user that "I'm sorry, this work number is being used!" ”

The above also involves a object_id () function:

Syntax:

object_id (' [database_name. [Schema_name]. | Schema_name. object_name ' [, ' object_type '])

General syntax:int object_id (' objectname ');

This method returns the database object identification number.

Where the parameter objectname represents the object to be used, whose data type is nchar or char (if char, the system converts it to nchar)

Object_type: is an optional parameter whose data type is nchar or char (if char, the system converts it to nchar), indicating the schema-scoped object type (object_name is a string through which you can tell if the string is a description object, See the end of the article for a list)

PS: You cannot query non-schema-scoped objects (such as DDL triggers) using OBJECT_ID. For objects that are not found in the Sys.objects catalog view, you need to get the object's identification number by querying the appropriate catalog view. For example, to return the object identification number of a DDL trigger, use Select object_id from sys.triggers WHERE name = ' Databasetriggerlog '.

The return type is int, which indicates the number of the object in the system and returns null if no errors are found or error occurs.

Example:

A. Returning the identification number of the Production.WorkOrder table in the database AdventureWorks

Use master; GO SELECT object_id (N ' AdventureWorks.Production.WorkOrder ') as ' OBJECT ID '; GO

B. The existence of a check

The following confirms that the table has an identifier for the object, which checks to see if the specified table exists. Delete if it exists.

Use AdventureWorks; GO IF object_id (N ' dbo. Awbuildversion ', N ' U ') is not NULL DROP TABLE dbo. Awbuildversion; GO

This method is generally used to determine that the database does not have this object (procedures,views,functions, etc.).

Note: When this parameter is optional for system functions, the system takes the current database, host, server user, or database user. The built-in function must be followed by parentheses. If you specify a temporary table name, unless the current database is tempdb (nonsense), you must precede the temporary table name with the database name, for example: SELECT object_id (' tempdb. #mytemptable ')

Object_type list:

AF = Aggregation Functions (CLR)

C = CHECK Constraints

D = DEFAULT (constrained or independent)

F = FOREIGN KEY Constraints

FN = SQL scalar Functions

FS = Assembly (CLR) scalar Functions

FT = Assembly (CLR) table-valued functions

IF = SQL Inline table-valued functions

IT = Internal Tables

P = SQL Stored Procedures

PC = Assembly (CLR) Stored Procedures

PG = Plan guide

PK = PRIMARY KEY Constraints

R = rules (old-fashioned, standalone)

RF = Replication Filtering Process

S = System Base Table

SN = Synonyms

SQ = Service Queue

TA = Assembly (CLR) DML Trigger

TF = SQL table-valued functions

TR = SQL DML Trigger

U = table (user-defined type)

UQ = UNIQUE Constraints

V = View

X = Extended stored Procedures

SQL Temp Table