[SQL SERVER][Denali] TSQL 新特性(上)
這篇我們來看看 Denali 在TSQL方面所帶來的新特性。
1.Offset and Fetch First/Next(分頁操作改善)
SQL2005/2008 我想大家都會透過row_number function 來達到分頁效果，
而在 Denali 你可以有更好的選擇，使用 Offset and Fetch First/Next 你將感受到分頁操作就是這麼簡單且更有效率。
假設今天我希望每頁顯示15筆資料，在 Denali 實現分頁真的再簡單不過了，同時我也會簡單測試兩者效能差異。
SQL2011 分頁操作
set statistics io on;
set statistics time on;
declare @currentpage int,@pagecount int
set @currentpage=0 --現在頁數(0=第一頁)
set @pagecount=15--每頁顯示筆數
select * from Production.TransactionHistory t1
order by t1.TransactionID
Offset @currentpage*@pagecount rows --第一筆 rownum
fetch next @pagecount rows only --從第一筆 rownum 往下取得總筆數
statistics io/time(經過時間=65 ms)
執行計畫(總成本=0.0039108)
SQL2005/2008 分頁操作
set statistics io on;
set statistics time on;
declare @currentpage int,@pagecount int
set @currentpage=0 --現在頁數(0=第一頁)
set @pagecount=15--每頁顯示筆數
;with mycte as
(
select row_number() over (order by TransactionID) 'rownum',*
from Production.TransactionHistory
)
select * from mycte
where rownum >(@currentpage*@pagecount) and rownum<=(@currentpage*@pagecount)+@pagecount
statistics io/time(經過時間=70 ms)
執行計畫(總成本=0.0040088)
和SQL2011 執行計畫比較可說複雜多了。
一起比較兩者執行計畫成本(SQL2011執行計畫較優於SQL2005/2008)
效能比較結果表
(注意:With Result Sets不支援 Insert …. Exec 陳述句)
以前我們透過 SP(store procedure)想要取得查詢結果集，通常會將結果塞入 TempTable 或 TVP..等，
然後再查詢取得相關結果集，但 Denali 不需要你這麼麻煩(不需要使用 TempTable或TVP..等)，
我們可以直接利用 With Result Sets 就能達到目的，且也能減少整體時間，同時更能在執行過程中更改欄位名稱或資料型別。
建立SP
create proc dbo.mysp(@tid int)
as
select TransactionID,ProductID,TransactionType,ModifiedDate
from Production.TransactionHistory
where TransactionID=@tid
執行SP
exec dbo.mysp 100004
with result sets
(
(
TransactionID int,
ProductID int,
TransactionType varchar(10),--更改資料型別
MyModifiedDate datetime --更改欄位名稱
)
)
3.Rows and Range:允許更改分區中的開始位置和結束位置
注意:
1.並非所有function 都支援 rows and range
2.使用 rows and range必須指定 order by
3.有指定 order by但未使用rows and range，則預設為 range between undounded preceding and current rows。
假設今天我們需要計算累加值。
原始資料
SQL2011作法
declare @starttime time(7),@endtime time(7),@elaspedtime numeric(20,7);
set @starttime=GETDATE()
select c1,c2, sum(c2) over (order by c1
range unbounded preceding) as '累加量'
from dbo.mysales
set @endtime=GETDATE();
set @elaspedtime = convert(integer, datediff(ms, @starttime, @endtime));
select @elaspedtime as '花費時間(ms)'
經過時間=50 ms
執行計畫(總成本=0.0147154)
SQL2005/2008
做法可以參考[SQL SERVER][TSQL]如何計算累加值
經過時間=63 ms
執行計畫(總成本=0.0132996+0.0132842+0.0032831=0.0298669)
一起比較兩者執行計畫成本(SQL2011執行計畫較優於SQL2005/2008)
--2011
select c1,c2, sum(c2) over (order by c1
range unbounded preceding) as '累加量'
from dbo.mysales
--2005/2008
--結果表
declare @result table
(
c1 varchar(10),
c2 int,
total int default(0)
)
insert into @result select *,0 from dbo.mysales
declare @total int
set @total=0
update @result
--設定變數與資料行相同的值
set @total = total = @total + c2
--結果
select t1.c1,t1.c2,t1.total as '累加量'
from @result t1
效能比較結果表
設定開始位置和結束位置五種方式
select c1,c2,sum(c2) over (order by c1
range unbounded preceding) as '累加量1',
sum(c2) over (order by c1
range between unbounded preceding AND unbounded following) as '累加量2',
sum(c2) over (order by c1
rows between current row and 1 following ) as '累加量3',
sum(c2) over (order by c1
rows 1 preceding ) as '累加量4',
sum(c2) over (order by c1
rows between 1 preceding and 3 following ) as '累加量5'
from dbo.mysales
當然Rows and Range 應用不只這些，其餘應用大家有興趣可自行測試看看。