Use with as efficient paging

First, with asmeaning
The with as phrase, also called the subquery section (subquery factoring), allows you to do many things, defining a SQL fragment that 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 providing data. Especially useful for union all. Because each part of union all may be the same, but if each part goes through it, the cost is too high, so you can use the with as phrase, as long as you execute it again. If the table name defined by the with as phrase is called more than two times, the optimizer automatically places the data obtained from the with as phrase into a temp table, if it is called only once. The hint materialize, however, is to force the data in the with as phrase into a global temporary table. Many queries can improve speed in this way.
Second, the use of the scene
Subqueries are too nested to make SQL statements very difficult to read and maintain, although we can put subqueries in table variables, which makes the SQL statement easier to maintain, but brings 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.
Grammar:

[]  ]<common_table_expression>::=      []as(cte_query_definition)

Example:

 with  cr as   ( select  ID from  TreeNode where  Parentid=  1  )  select  from  news where  parentid in  (select  *  from  CR) 

Precautions:
The 1.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.
2.CTE can also be followed with other CTE, but only one with, multiple CTE in the middle with a comma (,) separated.

Cases

 withcte1 as(    Select *  fromTable1whereName like 'abc%'), Cte2 as(    Select *  fromTable2whereId>  -), Cte3 as(    Select *  fromTable3wherePrice<  -)SelectA.*  fromCte1 A, Cte2 B, Cte3 cwherea.ID=b.ID anda.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 table that actually exists

 with  table1  as   ( select   *  from  persons where  Age <  30  )  Span style= "color: #0000ff;" >select  *  from  table1 --  select  Span style= "color: #808080;" >*  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, the statement before it must end with a semicolon.

Three, with AS and row_number to write an efficient paging program.
Row_number is also the SQL 2005 new feature Syntax:

Row_number () over  []<order_by_clause> )

The following is a paged SQL statement:

1 Declare @pagesize int,@pageindex int2 Set @pagesize= -3 Set @pageindex=3;4  with Temp 5  as6 (7 Select *, Row_number () Over(Order  byID) rownumber fromdbo. Table name8 )9 Select *  from Temp whereRowNumberbetween @pagesize*(@pageindex-1)+1  and @pagesize*@pageindex

Using with as efficient paging