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

Source: Internet
Author: User
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

 

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.