SQL中使用WITH AS提高性能,使用公用表表達式（CTE）簡化嵌套SQL

一．with as的含義

    with as短語，也叫做子查詢部分（subquery

factoring），可以讓你做很多事情，定義一個sql片斷，該sql片斷會被整個sql語句所用到。有的時候，是為了讓sql語句的可讀性更高些，也有可能是在union

all的不同部分，作為提供資料的部分。

特别對于union all比較有用。因為union

all的每個部分可能相同，但是如果每個部分都去執行一遍的話，則成本太高，是以可以使用with as短語，則隻要執行一遍即可。如果with

as短語所定義的表名被調用兩次以上，則優化器會自動将with

as短語所擷取的資料放入一個temp表裡，如果隻是被調用一次，則不會。而提示materialize則是強制将with

as短語裡的資料放入一個全局臨時表裡。很多查詢通過這種方法都可以提高速度。

二．使用方法

先看下面一個嵌套的查詢語句：

select * from person.stateprovince where countryregioncode in

         (select countryregioncode

from person.countryregion where name like ‘c%‘)

上面的查詢語句使用了一個子查詢。雖然這條sql語句并不複雜，但如果嵌套的層次過多，會使sql語句非常難以閱讀和維護。是以，也可以使用表變量的方式來解決這個問題，sql語句如下：

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)

雖然上面的sql語句要比第一種方式更複雜，但卻将子查詢放在了表變量@t中，這樣做将使sql語句更容易維護，但又會帶來另一個問題，就是性能的損失。由于表變量實際上使用了臨時表，進而增加了額外的i/o開銷，是以，表變量的方式并不太适合資料量大且頻繁查詢的情況。為此，在sql

server

2005中提供了另外一種解決方案，這就是公用表表達式（cte），使用cte，可以使sql語句的可維護性，同時，cte要比表變量的效率高得多。

    下面是cte的文法：

[ with <common_table_expression> [ ,n ]

]

<common_table_expression>::=

expression_name [ ( column_name [ ,n ] ) ]

as

        ( cte_query_definition )

    現在使用cte來解決上面的問題，sql語句如下：

with

cr as

(

    select countryregioncode from

person.countryregion where name like ‘c%‘

)

select * from person.stateprovince where countryregioncode in (select * from

cr)

    其中cr是一個公用表表達式，該表達式在使用上與表變量類似，隻是sql server

2005在處理公用表表達式的方式上有所不同。

    在使用cte時應注意如下幾點：

1.

cte後面必須直接跟使用cte的sql語句（如select、insert、update等），否則，cte将失效。如下面的sql語句将無法正常使用cte：

select * from

person.countryregion  -- 應将這條sql語句去掉

-- 使用cte的sql語句應緊跟在相關的cte後面

--

select * from person.stateprovince where countryregioncode in (select *

from cr)

2. cte後面也可以跟其他的cte，但隻能使用一個with，多個cte中間用逗号（,）分隔，如下面的sql語句所示：

cte1 as

    select * from table1 where name

like ‘abc%‘

),

cte2 as

    select * from table2

where id > 20

cte3 as

    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.

如果cte的表達式名稱與某個資料表或視圖重名，則緊跟在該cte後面的sql語句使用的仍然是cte，當然，後面的sql語句使用的就是資料表或視圖了，如下面的sql語句所示：

--  table1是一個實際存在的表

table1 as

    select * from persons where age

< 30

select * from table1  --  使用了名為table1的公共表表達式

select

* from table1  --  使用了名為table1的資料表

4. cte 可以引用自身，也可以引用在同一 with 子句中預先定義的 cte。不允許前向引用。

5. 不能在 cte_query_definition 中使用以下子句：

（1）compute 或 compute by

（2）order by（除非指定了 top 子句）

（3）into

（4）帶有查詢提示的 option 子句

（5）for xml

（6）for browse

6. 如果将 cte 用在屬于批處理的一部分的語句中，那麼在它之前的語句必須以分号結尾，如下面的sql所示：

declare @s nvarchar(3)

set @s = ‘c%‘

;  -- 必須加分号

t_tree

    select countryregioncode from person.countryregion

where name like @s

select * from person.stateprovince where

countryregioncode in (select * from t_tree)