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

Temporary tables, table variables, and CTE are often used when writing SQL statements. 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
(
ID INT,
NAME VARCHAR(10),
COMPANY VARCHAR(50)
)

SELECT * FROM #tmpTable JOIN ...

DROP TABLE #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.

 SELECT E.EmployeeID,E.FirstName,E.LastName,O.OrderID,O.CustomerID,O.OrderDate 
INTO #tmpTable
FROM Orders O JOIN Employees E ON O.EmployeeID=E.EmployeeID
WHERE E.EmployeeID=1

 

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

 CREATE TABLE ##tmpTable2
(
ID INT,
NAME VARCHAR(20),
COMPANY VARCHAR(50)
)
SELECT * FROM ##tmpTable2 JOIN ...

DROP TABLE ##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 @ vartable table
(
Id int,
Name varchar (20 ),
Company varchar (50)
)


-- Create a table type first
Create type [DBO]. [t_temp] As table (
Id int,
Name varchar (20 ),
Company varchar (50)
)

-- Declare table Variables
Declare @ vartable t_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

 WITH CTE_NAME
AS
(
SELECT E.EmployeeID,E.FirstName,E.LastName,O.OrderID,O.CustomerID,O.OrderDate 
FROM Orders O JOIN Employees E ON O.EmployeeID=E.EmployeeID
WHERE E.EmployeeID=1
)
SELECT * FROM CTE_NAME

 

The most powerful feature of CTE is recursive queries. For details, refer to Microsoft's article.