[SQL Denali] T-SQL 增強介紹

2011-11-25

4567
0

在這版中提供了一些新的T-SQL語法及物件，詳細資料可參考以下Link，這裡針對其中幾個比較
感興趣的做介紹
http://technet.microsoft.com/en-us/library/cc645577(SQL.110).aspx

(1)分頁：在SQL2008，我們可能會利用row_number()來進行分頁查詢的設計，藉由row_number()的
自動編號，來計算本頁次要取出的資料，其編號是介在幾號~幾號之間，例如：我想要每10筆為一頁，
所以如果我現在要查詢第2頁，那麼資料就應該是編號為11~20的這10筆資料，因此我只要回傳這10筆
即可，以下我採用Denali的範例資料庫實測

declare @cnt int,@currpage int
set @cnt=10 --每10筆為單位分成一頁
set @currpage=1 --目前所需要的頁數 (PS:由0起算，0表示第一頁)

select * 
from (
	select row_number() over (order by productalternatekey) as 'rowno',*
	from DimProduct 
) workdaily
where rowno >(@currpage*@cnt) and rowno<=((@currpage+1)*@cnt)

現在新的語法，可以更直覺的

declare @cnt int,@currpage int
set @cnt=10 --每10筆為單位分成一頁
set @currpage=1 --目前所需要的頁數 (PS:由0起算，0表示第一頁)

select * 
from DimProduct
order by DimProduct.productalternatekey
Offset @currpage*@cnt rows --計算第一筆是誰
fetch next @cnt rows only --從第一筆往下取10筆

不過二者語法所花費的成本似乎差異不大，在效能上沒什麼差別，只是語法更直覺

 http://msdn.microsoft.com/en-us/library/ms188385(v=SQL.110).aspx

(2)iif：這個字眼，如果有寫過VBA或Excel的人應該不陌生，我直接以一個原本是由case when的語法
改成iif，做個Demo

select case when Gender='M' then N'男生' else N'女生' end gender
from DimEmployee

select iif(Gender='M',N'男生',N'女生')
from DimEmployee

(3)EOMonth：取月份最後一天，以往要取月份最後一天，我們大慨會用以下這樣的語法

select DATEADD(day,-1,(convert(datetime,(convert(char(7),DATEADD(month,1,getdate()),111)+'/1'))))

現在可以直接更簡單來取得

select EOMonth(cast(getdate() as date))

(4)Concat：字串相加，若參數為 NULL 則會被視為空字串

(5)集合運算效能提升：詳細資料可參考
http://msdn.microsoft.com/zh-tw/library/ms189461(v=SQL.110).aspx
同樣的以Denali範例資料庫進行Demo，這裡我直接舉個例子來說

需求：Sales.SalesOrderHeader是銷貨單檔，報表要看2006年各月單月銷貨金額及各月累進合計資料

在SQL 2008以前，SQL可能會這樣下

SELECT A.mon,A.TotalDue,SUM(B.TotalDue) AS TotalDue2
FROM (
	select month(OrderDate) as mon,sum(TotalDue) as TotalDue
	from Sales.SalesOrderHeader
	where year(OrderDate)=2006
	group by month(OrderDate)
) AS A
  JOIN (
	select month(OrderDate) as mon,sum(TotalDue) as TotalDue
	from Sales.SalesOrderHeader
	where year(OrderDate)=2006
	group by month(OrderDate)
) AS B
    ON B.mon <= A.mon
GROUP BY A.mon,A.TotalDue
order by  A.mon

在Denali，則可以這樣下

select A.mon,A.TotalDue
,sum(A.TotalDue) over (order by A.mon range unbounded preceding)as TotalDue2
from (
	select month(OrderDate) as mon,sum(TotalDue) as TotalDue
	from Sales.SalesOrderHeader
	where year(OrderDate)=2006
	group by month(OrderDate)
) A

來看一下執行計劃，首先可以看到同樣的資料集合，在運算過程中，資料行數一個是73，一個是24

再看花費成本，一個是1.2358，另一個則是只有0.6177，提升不少

其它的部份有時間試玩後，再跟大家分享囉！

若本文對您有所幫助，歡迎轉貼，但請在加註【轉貼】及來源出處，並在附上本篇的超連結，感恩您的配合囉。

By No.18

回首頁

No.18

學習無止盡　不進則退　世界不會因你而停下腳步

[SQL Denali] T-SQL 增強介紹

系列文章

標籤雲