Temporary tables in the database. The advantages of table variables and CTE are extremely different.

1	Before writingSQLTemporary tables, table variables, and CTE are often used. These three have their own advantages in use:

1. Temporary tables: divided into local temporary tables and global temporary tables.

1.1 Local temporary tables starting with # are stored in the system database tempdb. the current link is visible. When the link is disconnected, the temporary table is automatically released. You can also manually drop table # tmptable

When different links are used to create the same temporary table at the same time, they do not affect each other. In tempdb, the system automatically attaches a specific session name to distinguish them. it is often used in SP to extract data or common data to a temporary table. Other operations (such as SELECT, UPDATE, DELETE, and DROP) can be performed in the future ).

You can create a temporary table like creating a permanent table:

 CREATE TABLE#tmpTable
(
IDINT,
NAMEVARCHAR(10),
COMPANYVARCHAR(50)
)

SELECT*FROM#tmpTableJOIN...

DROPTABLE#tmpTable

You can also use INTO to create a temporary table, such as querying all orders with employee ID = 1 and placing them in a temporary table for subsequent processing.

 SELECTE.EmployeeID,E.FirstName,E.LastName,O.OrderID,O.CustomerID,O.OrderDate
INTO#tmpTable
FROMOrders OJOINEmployees EONO.EmployeeID=E.EmployeeID
WHEREE.EmployeeID=1

 

1.2 create a global temporary table starting with #. It is stored in tempdb and visible to all sessions.

 CREATETABLE##tmpTable2
(
IDINT,
NAMEVARCHAR(20),
COMPANYVARCHAR(50)
)
SELECT*FROM##tmpTable2JOIN...

DROPTABLE##tmpTable2

2. table variables: stored in memory, faster than the execution speed of temporary tables. if the SP or function is out of the valid scope, it is automatically released without explicit write drop. table variables can only be used in DML operations. There are many restrictions.

Code

 -- Directly declare table Variables
DECLARE @ varTableTABLE
(
IDINT,
NAMEVARCHAR (20 ),
COMPANYVARCHAR (50)
)


-- Create a table type first
CREATETYPE [dbo]. [T_TEMP] ASTABLE (
IDINT,
NAMEVARCHAR (20 ),
COMPANYVARCHAR (50)
)

-- Declare table Variables
DECLARE @ varTableT_TEMP

 

3. CTE (Common Table Expressions) Generic Table expression: a result set that can be referenced by a definition statement. In their simple form, CTE can be considered as a derived table similar to the non-durable type view. you only need to define the CTE once to reference it multiple times in the query.

Code

 WITHCTE_NAME
AS
(
SELECTE.EmployeeID,E.FirstName,E.LastName,O.OrderID,O.CustomerID,O.OrderDate
FROMOrders OJOINEmployees EONO.EmployeeID=E.EmployeeID
WHEREE.EmployeeID=1
)
SELECT*FROMCTE_NAME

 

The most powerful feature of CTE is recursive queries. For more information, seeMicrosoft.

Http://space.itpub.net/16436858/viewspace-629908