SQL with AS usage

A Meaning of with AS

The with as phrase, also called the subquery section (subquery factoring), can define a SQL fragment that will be used by the entire SQL statement. You can make the SQL statement more readable or in different parts of union all as part of the data supply.

For union all, a UNION ALL statement is defined using with AS, and when the fragment is called more than 2 times, the optimizer automatically places the data obtained by the with as phrase into a temp table. The hint meterialize, however, is to force the data in the with as phrase into 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          (select Countryregioncode by person. CountryRegion where Name like ' c% ')

The query statement above uses a subquery. Although this SQL statement is not complex, it can make SQL statements very difficult to read and maintain if there are too many nested hierarchies. Therefore, you can also 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 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 it also introduces another problem, the loss of performance. Because table variables actually use temporary tables, which increases the additional I/O overhead, table variables do not work well for large data volumes and queries frequently. To do this, another solution is provided in SQL Server 2005, which is a common table expression (CTE) that uses a CTE to make the SQL statement maintainable, while the CTE is much more efficient than a 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 (     select Countryregioncode. CountryRegion where Name like ' c% ') select * from person. StateProvince where Countryregioncode in (SELECT * from CR)

Where CR is a common table expression that is similar to a table variable in use, except that SQL Server 2005 differs in how common table expressions are handled.

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 CTE will not work correctly as in the following SQL statement:

WITHCR as (    select Countryregioncode from person. CountryRegion where Name like ' c% ') select * from person. CountryRegion-  This SQL statement should be removed-the SQL statement using the CTE should be immediately followed by the associated 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:

Withcte1 as (    select * FROM table1 where name like ' abc% '), Cte2 as (    select * from table2 where ID >), cte3 as (    SELECT * from Table3 where price < () 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 a data table or view, the SQL statement immediately following the CTE is still using the CTE, and of course, the subsequent SQL statement uses the data table or view, as shown in the following SQL statement:

--  Table1 is a physically present table Withtable1 as (    select * from persons where age <) SELECT * FROM table1  --  using the Name tab Le1 Common table Expression 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.

--Use recursive common table expressions to display multiple levels of recursion with Directreports (ManagerID, EmployeeID, Employeelevel) as (SELECT ManagerID, EmployeeID, 0 as E Mployeelevel from HumanResources.Employee WHERE ManagerID are NULL UNION all SELECT E.managerid, E.employeeid,  Employeelevel + 1 from humanresources.employee e INNER joins directreports d on e.managerid = D.employeeid SELECT ManagerID, EmployeeID, employeelevel from Directreports;--Use a recursive common table expression to display two levels of recursion with Directreports (ManagerID, EmployeeID, Employeelevel) as (SELECT ManagerID, EmployeeID, 0 as Employeelevel from HumanResources.Employee WHE         RE ManagerID is a NULL UNION all SELECT E.managerid, E.employeeid, Employeelevel + 1 from HumanResources.Employee E INNER JOIN directreports D on e.managerid = D.employeeid) SELECT ManagerID, EmployeeID, employeelevel from Directreports WHERE employeelevel <= 2--Using a recursive common table expression to display a hierarchical list with Directreports (Name, Title, EmployeeID, Employeelevel, Sort) as (the SELECT CONVERT (varchar (255), C.firstname + "+ c.lastname), E.title, E.employeeid, 1, CONVERT (varchar (255), C.first     Name + ' + c.lastname) from HumanResources.Employee as E joins Person.Contact as C on e.contactid = C.contactid WHERE E.managerid is a NULL UNION all SELECT CONVERT (varchar (255), REPLICATE (' | ', Employeelevel) + c.firstname + ' + c.lastname), E.title, E.employeeid, Employeelevel + 1 , CONVERT (varchar (255), RTRIM (Sort) + ' | ' + FirstName + ' + LastName) from HumanResources.Employee as E joins Person.Contact as C on E.con Tactid = C.contactid JOIN directreports as D on e.managerid = D.employeeid) SELECT EmployeeID, Name, Title, Employee Levelfrom directreports ORDER by sort--uses Maxrecursion to cancel a statement--you can use maxrecursion to prevent unreasonable recursive CTE from entering an infinite loop.  The following example deliberately creates an infinite loop and then uses the maxrecursion hint to limit the recursion level to a level two with CTE (EmployeeID, ManagerID, Title) as (SELECT EmployeeID, ManagerID, Title from HUMANRESOURCES.EMployee WHERE ManagerID is not a NULL UNION all SELECT CTE. EmployeeID, CTE. ManagerID, CTE. Title from the CTE JOIN HumanResources.Employee as E on CTE. ManagerID = E.employeeid)--uses maxrecursion to limit the recursive levels to 2SELECT EmployeeID, ManagerID, Titlefrom CTE OPTION (maxrecursion 2)--After correcting the code error, maxrecursion is no longer required. The following example shows the corrected code with the CTE (EmployeeID, ManagerID, title) as (SELECT EmployeeID, ManagerID, title from Humanresources.empl Oyee WHERE ManagerID is isn't NULL UNION all SELECT E.employeeid, E.managerid, e.title from Humanresources.employ EE as e JOIN cte on e.managerid = CTE. EmployeeID) SELECT EmployeeID, ManagerID, Titlefrom CTE

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% ';  --You must add a semicolon witht_tree as (    select Countryregioncode from person. CountryRegion where Name is @s) select * from person. StateProvince where Countryregioncode in (SELECT * from T_tree)

SQL with AS usage