SQL Tech Insider-8 using with as to improve performance simplifies nested SQL

--This article source: http://www.cnblogs.com/fygh/archive/2011/08/31/2160266.html

A Meaning of with AS

The with as phrase, also called the subquery section (subquery factoring), allows you to do a lot of things, define a SQL fragment that will
is 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 a number
of the data.
especially useful for union all. Because each part of union all may be the same, but if each part is executed again, the cost is too high,
So you can use the with as phrase, and just do it once. if the table name defined by the with as phrase is called more than two times, the optimizer automatically
The data obtained by the with as phrase is placed in a temp table and will not if it is called only once . The hint materialize is to force the with AS
The data in the phrase is placed in a global temporary table. Many queries can improve speed in this way .

Two How to use

Let's look at one of the following nested query statements:

SELECT * FROM person. StateProvince where Countryregioncode in          

The query statement above uses a subquery. Although this SQL statement is not complex, it can make SQL statements very difficult to read if too many layers are nested.
Read and maintain. Therefore, you can also use table variables to solve this problem.

The SQL statements are 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                      

　　

Although the above SQL statement is more complex than the first, it puts the subquery in the table variable @t, which makes the SQL statement easier to maintain, but
will bring another problem, is the loss of performance. Because table variables actually use temporary tables, which increases the additional I/O overhead, the way the table variables
is not very suitable for large data volumes and frequent queries. To do this, another solution is provided in SQL Server 2005, which is a common table expression (CTE) that uses a CTE to increase the maintainability of the SQL statement while the CTE is much more efficient than the table variable.

Here is the syntax for the CTE:

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

Now using the CTE to solve the above problem, the SQL statement is as follows:

With CR as (     

　　

Where CR is a common table expression that is similar to a table variable in use, except that SQL Server 2005 has a way of handling common table expressions
are different.

The following points should be noted when using a CTE:

1. The CTE must be followed directly with the SQL statement that uses the CTE (such as SELECT, Insert, UPDATE, and so on), otherwise the CTE will fail. The following SQL statement cannot be
Frequently used CTE:

With CR as (     select Countryregioncode. CountryRegion where Name like ' c% ') select * from person. CountryRegion-  This SQL statement should be removed-the SQL statement using the CTE should immediately follow the relevant CTE-select * from person. StateProvince where Countryregioncode in (SELECT * from CR)

2. The CTE can also be followed by other CTE, but only one with, the middle of multiple CTE separated by commas (,), as shown in the following SQL statement:

With Cte1 as (     select * FROM table1 where name like ' abc% '), Cte2 as (     select * from table2 where ID >), c Te3 as (     

3. If the expression name of the CTE is the same as a data table or view, the SQL statement immediately following the CTE is still using the CTE, of course, the following SQL statement
The data table or view is used, as shown in the following SQL statement:

With Table1 as (     select * from persons where age <) SELECT *  from table1--  uses a common table expression named Table1 select * From table1  --  using a data table named Table1

　　

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

5. The following clauses cannot be used in CTE_query_definition:

(1) COMPUTE or COMPUTE by

(2) ORDER by (unless the TOP clause is specified)

(3) into

(4) OPTION clause with query hint

(5) for XML

(6) for BROWSE

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

declare @s nvarchar (3) Set @s = ' c% ';  --Must be preceded by a semicolon with T_tree as (     

　　

SQL Tech Insider-8 using with as to improve performance simplifies nested SQL