1. When writing SQL statements, temporary tables, table variables, and CTE are often used. These three have their own advantages: 1. temporary tables: these tables are 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 droptable # tmptable is in use.
1. When writing SQL statements, temporary tables, table variables, and CTE are often used. These three have their own advantages: 1. temporary tables: these tables are 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 is in use.
1 |
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 #tmpTableFROM Orders O JOIN Employees E ON O.EmployeeID=E.EmployeeIDWHERE 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.
-- Directly DECLARE the TABLE variable DECLARE @ varTable TABLE (id int, name varchar (20), Company varchar (50) -- First CREATE the table type create type [dbo]. [T_TEMP] as table (id int, name varchar (20), Company varchar (50) -- DECLARE the table variable 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.
WITH CTE_NAMEAS(SELECT E.EmployeeID,E.FirstName,E.LastName,O.OrderID,O.CustomerID,O.OrderDate FROM Orders O JOIN Employees E ON O.EmployeeID=E.EmployeeIDWHERE E.EmployeeID=1)SELECT * FROM CTE_NAME
The most powerful feature of CTE is recursive queries. For details, refer to Microsoft's article.