Usage of the With as keyword in SQL Server

Especially useful for union all. Because each part of union all may be the same, but if each part is executed once, the cost is too high, so you can use the with as phrase, you only need to execute it once. If the table name defined by the with as phrase is called more than twice, the Optimizer automatically puts the data obtained by the with as phrase into a TEMP table. If it is called only once, no. The prompt materialize is to forcibly put the data in the with as phrase into a global temporary table. This method improves the speed of many queries.

Syntax:

[WITH <common_table_expression> [, n]
<Common_table_expression >::=
Expression_name [(column_name [, n])]
AS
(CTE_query_definition)

 

Usage

The code is as follows:	Copy code
With temp ( Select * From tb_order where Auditing = 2 and AuditingTime> = '2017-04-01 'and Flag = 2 ) Select * from temp where EbayAccountID = 21

The temp analyzer can intelligently prompt temporary tables, but not.

Temporary table

The code is as follows:	Copy code
Select * into # temp From tb_order where Auditing = 2 and AuditingTime> = '2017-04-01 'and Flag = 2 Select * From # temp where EbayAccountID = 21 Declare @ flag int = 2; With temp ( Select * From tb_order where Auditing = 2 and AuditingTime> = '2017-04-01 'and Flag = @ flag ) Select * from temp where EbayAccountID = 21

Declare @ flag int = 2; here, the semicolon is indispensable; otherwise, an error is reported.
There is a syntax error near the keyword '. If the statement is a common table expression, xmlnamespaces clause, or a change trace context clause, the previous statement must end with a semicolon.