Use with as in SQL to improve performance-use a common table expression (CTE) to simplify nested SQL statements)

1. Meaning of with
The with as phrase, also known as subquery factoring, allows you to do a lot of things and define an SQL segment, which will be used by the entire SQL statement. Sometimes, it is to make the SQL statement more readable, or it may be in different parts of Union all, as part of the provided data.
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.
Ii. Usage
First, let's look at the following nested query statement:

Select * From person. stateprovince where countryregioncode in
(Select countryregioncode from person. countryregion where name like 'C % ')

The preceding query statement uses a subquery. Although this SQL statement is not complex, if there are too many layers of nesting, it will make the SQL statement very difficult to read and maintain. Therefore, you can use table variables to solve this problem. The SQL statement is as follows:

Declare @ t table (countryregioncode nvarchar (3 ))
Insert into @ T (countryregioncode) (select countryregioncode from person. countryregion where name like 'C % ')

Select * From person. stateprovince where countryregioncode
In (select * From @ T)

Although the preceding SQL statement is more complex than the first method, the subquery is placed in the table variable @ T. This will make the SQL statement easier to maintain, but it will bring about another problem, namely the performance loss. Because the table variables actually use temporary tables, which increases Additional I/O overhead, the table variables are not suitable for large data volumes and frequent queries. Therefore, another solution is provided in SQL Server 2005, which is the common table expression (CTE). Using CTE can make the SQL statement maintainability, CTE is much more efficient than table variables.

The syntax of CTE is as follows:

[With <common_table_expression> [, N]
<Common_table_expression >::=
Expression_name [(column_name [, N])]
As
(Cte_query_definition)

Now we use CTE to solve the above problem. The SQL statement is as follows:

 

With
Cr
(
Select countryregioncode from person. countryregion where name like 'C %'
)

Select * From person. stateprovince where countryregioncode in (select * from Cr)

Cr is a common table expression, which is similar to a table variable in use, but SQL Server 2005 has different processing methods for common table expressions.

Pay attention to the following points when using CTE:
1. The CTE must be followed by the SQL statements that use the CTE (such as select, insert, and update). Otherwise, the CTE will become invalid. The following SQL statement cannot use CTE normally:

With
Cr
(
Select countryregioncode from person. countryregion where name like 'C %'
)
Select * From person. countryregion -- remove this SQL statement.
-- The SQL statement using CTE should be followed by the relevant CTE --
Select * From person. stateprovince where countryregioncode in (select * from Cr)

2. The CTE can be followed by other CTE, but only one with can be used. Multiple CTE are separated by commas (,), as shown in the following SQL statement:

 

With
Cte1
(
Select * From Table1 where name like 'abc %'
),
Cte2
(
Select * From Table2 where ID> 20
),
Cte3
(
Select * From table3 where price <100
)
Select a. * From cte1 A, cte2 B, cte3 c Where a. ID = B. ID and A. ID = C. ID

3. if the expression name of the CTE is the same as that of a data table or view, the SQL statement followed by the CTE still uses the CTE. Of course, the following SQL statement uses a data table or view, as shown in the following SQL statement:

-- Table1 is an existing table.

With
Table1
(
Select * from persons where age <30
)
Select * From Table1 -- a public table expression named Table1 is used.
Select * From Table1 -- a data table named Table1 is used.

4. The CTE can reference itself, or reference the pre-defined CTE in the same with clause. Forward references are not allowed.

5. the following clause cannot be used in cte_query_definition:

(1) Compute or compute

(2) Order by (unless top clause is specified)

(3)

(4) option clause with query prompt

(5) For XML

(6) For browse

6. If you use CTE in a statement that is part of the batch processing, the statement before it must end with a semicolon, as shown in the following SQL:

Declare @ s nvarchar (3)
Set @ s = 'C %'
; -- Add points required
With
T_tree
(
Select countryregioncode from person. countryregion where name like @ s
)
Select * From person. stateprovince where countryregioncode in (select * From t_tree)

In addition to simplifying nested SQL statements, CTE can also perform recursive calling. This part of content will be introduced in the next article.