SQL Server temporary table usage

SQL Server temporary table usage
-- Delete temporary table # TMP
Create Table # TMP -- create a temporary table # TMP
(
Id int identity () not null, -- creates a column ID and Adds 1 to each new record.
Wokno varchar (50 ),
Primary Key (ID) -- Define ID as a temporary table # primary key of TMP
);
Select * from # TMP -- query the data in the temporary table
Truncate table # TMP -- clear all data and constraints of the temporary table

Example:

Declare @ wokno varchar (500) -- used to record employee numbers
Declare @ STR nvarchar (4000) -- used to store query statements
Declare @ count int -- obtain the total number of records
Declare @ I int
Set @ I = 0
Select @ COUNT = count (distinct (wokno) from # TMP
While @ I <@ count
Begin
Set @ STR = 'select top 1 @ wokno = wokno from # TMP where id not in (select top '+ STR (@ I) + 'id from # TMP )'
Exec sp_executesql @ STR, n'@ wokno varchar (500) output', @ wokno output
Select @ wokno, @ I -- display the employee ID in one row
Set @ I = @ I + 1
End
Temporary table
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 is executed by multiple users at the same timeProgramSQL Server must be able to differentiate tables created by different users. Therefore, SQL Server internally records the table names of each local temporary table.
Add a numeric suffix. The full name of a temporary table stored in the sysobjects table of the tempdb database is composed of the table name specified in the create table statement and the digital suffix generated by the system. To allow appending 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 you create a global temporary table session
When the last statement that references the table is completed, the table is automatically removed.
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
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, this statement returns a warning message indicating that
The bundle 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.
 
========================================================== ========================================================== ========================================================== ====================================

What is the difference between a table and a table variable?
When can I use tables and when can I use table variables?
 
--------------------------------
A table is an object that stores data in a database file.

Table variables are special variables that are only valid for the current session.
 
-----------------------------------------
For Table variables, books online is described as follows:

A special data type used to store result sets for subsequent processing. This data type is mainly used to temporarily store a group of rows. These rows are returned as the result set of the Table value function.

Try to use table variables instead of temporary tables. Table variables have the following advantages:

· The behavior of table variables is similar to that of local variables and has a clearly defined scope. This scope is the function, stored procedure, or batch processing that declares the variable.
In its scope, table variables can be used as regular tables. This variable can be applied to tables or table expressions in select, insert, update, and delete statements. However, table cannot be used in the following statements:
Insert into table_variable exec stored procedure.
Select select_list into table_variable statement.
When the function, stored procedure, or batch processing of the table variable is defined, the table variable is automatically cleared.

· The check constraints, default values, and calculation columns in the table type declaration cannot call user-defined functions.

· Using Table variables in a stored procedure reduces the amount of recompilation in a stored procedure compared to using a temporary table.

· Transactions involving table variables only exist during table variable update. This reduces the need for table variables to lock and record resources.

· Assignment between table variables is not supported.
Declare @ T1 table (T1 INT)
Declare @ T2 table (t2 INT)
Set @ T1 = @ T2 -- Error

· In addition, table variables have limited scope and are not part of a persistent database, so they are not affected by transaction rollback.