自己總結的一些sql server 2005 的新特征

2005  新特性
1 . with  用法

with  aa 
as ( select   top   10   *   from   table )

select   *   from  aa

2 .ntile 用法
select  field1 ,field2,ntile( 8 )  over ( order   by  field1  desc )
from   table
-- 分8組,每組編號

3 .PIVOT 用法

select  vendor_id, ' 2005 '   as   ' 2005 ' , ' 2005 '   as   ' 2006 '  purchase_order

SELECT  Vendor_ID,  [ 164 ]   AS  Emp1,  [ 198 ]   AS  Emp2,  [ 223 ]   AS  Emp3,  [ 231 ]   AS  Emp4,  [ 233 ]   AS  Emp5
FROM  
( SELECT  no,currency_id, Vendor_ID
FROM  purchase_order) p
PIVOT
(
COUNT  (vendor_id)
FOR  vendor  IN
(  [ 164 ] ,  [ 198 ] ,  [ 223 ] ,  [ 231 ] ,  [ 233 ]  )
)  AS  pvt
ORDER   BY  VendorID

4 . BEGIN  TRY
{sql statement |  SQL Block}
END  TRY
BEGIN  CATCH TRAN_ABORT
{sql statement |  SQL Block}
END  CATCH
5 .大字段的應用
varchar ( max )
nvarchar ( max )
6 .新增函數
row_number() 相當於行號
-- 分頁功能
with  a  as
( select   top   100   * ,row_number()  over ( order   by  field1)  as  a  from   table )
select   *   from  a  where  a  between   10   and   20
7 .不支持 *= , =* 的左,右聯接