標籤: SQL Server 2005

使用CTE進行遞迴(Recursive)查詢

從 MS SQL 2005 起, 支援了 CTE (Common Table Expression) 語法, 可以參考之前的文章: https://diary.tw/archives/339 .

今天要來介紹的是有關應用在遞迴方面的查詢, 利用這種查詢, 可以很容易地將資料展開, 例如像是組織圖, 或是像分類項含有子分類這樣的樹狀資料, 當然, 簡單一點的像是累加也是一樣的, 先來介紹 CTE 用在累加的語法上.

WITH cte (num, mysum) AS (
  SELECT 1 as num, 1 as mysum
  UNION ALL
  SELECT num + 1, mysum + num + 1 FROM cte a where num <10
)
SELECT * FROM cte

看好, 在括號內的第一個 SELECT 1, 1 的部分, 就是原始值, 而後面的 SELECT .. FROM cte 則是開始自己對自己查詢, 最後的 num < 10 是他的 boundary condition, 也就是只做到 num < 10.

結果如下:
是的, 結果就是那個 1 + 2 + 3 + … + 10 = 55 .

再來看看其他的用法, 像是分類含有子分類的資料, 先來準備一些資料:

CREATE TABLE CATEGORY (CID int primary key, NAME nvarchar(200), PARENTID int)
INSERT INTO CATEGORY values (1, N'交通工具', 0), (2, N'陸上', 1), (3, N'水上', 1)
INSERT INTO CATEGORY values (4, N'機車', 2), (5, N'汽車', 2), (6, N'輪船', 3)
INSERT INTO CATEGORY values (7, N'空中', 1), (8, N'飛機', 7), (9, N'直升機', 7)

來組合吧, 使用CTE:

WITH CTE (CID, NAME, PARENTID, LEVEL) AS (
  SELECT CID, NAME, PARENTID, 0 FROM CATEGORY WHERE PARENTID = 0
  UNION ALL
  SELECT A.CID, A.NAME, A.PARENTID, LEVEL + 1 FROM CATEGORY A INNER JOIN CTE B ON A.PARENTID = B.CID
)
SELECT * FROM CTE

結果如下:

是不是很清楚看出來各分類的位階(LEVEL)狀況? 十分方便好用的語法. 再來看看如何展開成一行資料, 也就是各自分類結構合成一筆結果, 語法如下:

WITH CTE (CID, NAME, PARENTID, LEVEL, ORICID, DATA) AS (
  SELECT CID, NAME, PARENTID, 0, CID, CONVERT(NVARCHAR(MAX), NAME) FROM CATEGORY WHERE PARENTID = 0
  UNION ALL
  SELECT A.CID, A.NAME, A.PARENTID, LEVEL + 1, A.CID, DATA + '-' + A.NAME FROM CATEGORY A INNER JOIN CTE B ON A.PARENTID = B.CID
)
SELECT * FROM CTE

結果如下:

最後面的那個欄位就是想要呈現出來的結果, 說穿了, 就是一直累加上去, 只是是字串的累加(DATA), 不是數字的累加, 另外也多了一個欄位用來表現原本自己的CID(ORICID), 這樣看起來也就更清楚完整, 利用這樣的方式來表現分類結構, 非常好用呢!

另外記得字串累加的部分, 使用 NVARCHAR(MAX) 以避免發現 CTE 和累加的欄位不一致的問題, 這是要特別注意的地方.

無法對齊欄位的錯誤訊息為:
Types don’t match between the anchor and the recursive part in column “xx” of recursive query “xx”.
可以參考這篇解答: http://stackoverflow.com/questions/1838276/cte-error-types-dont-match-between-the-anchor-and-the-recursive-part

結論, CTE的遞迴語法, 其實也就是自己再對自己查詢, 只要把握好起始條件, 還有遞迴的終止條件, 就可以很順利的產出你要的結果. 大家可以多加利用這個好用的語法, Let’s CTE!!

SQL Server的遺失索引統計

先說好, 這篇必須是 SQL Server 2005 以上的用戶才能用到的, 因為用到的資料是 DMV 的系統 view, 也就是 Dynamic Management Views.

這裡會用到的 DMVs 是用來查詢所謂遺失的索引, 白話一點, 就是應該要建立的索引, 而沒有建立的索引, 稱之為所謂的”遺失的索引”. 資料庫在查詢時, 若是發現有這樣的狀況, 會記錄下來, 在 DMVs 內的這幾個表:

sys.dm_db_missing_index_groups
sys.dm_db_missing_index_group_stats
sys.dm_db_missing_index_details

在這些表內可以利用這篇文章 (揭露隱藏的資料以最佳化應用程式效能)的一個計算方式(當然也可以再調整), 來將影響較為嚴重而又沒有加上索引的 table 找出來, 查詢如下:

SELECT  TOP 10
        [Total Cost]  = ROUND(avg_total_user_cost * avg_user_impact * (user_seeks + user_scans),0)
        , avg_user_impact
        , TableName = statement
        , [EqualityUsage] = equality_columns
        , [InequalityUsage] = inequality_columns
        , [Include Columns] = included_columns
FROM        sys.dm_db_missing_index_groups g
INNER JOIN    sys.dm_db_missing_index_group_stats s
       ON s.group_handle = g.index_group_handle
INNER JOIN    sys.dm_db_missing_index_details d
       ON d.index_handle = g.index_handle
ORDER BY [Total Cost] DESC;

這樣表列出來的資料簡單說明一下:
第一個Total Cost為總成本, 該作者是使用了一個計算的方式, 並以此為排序條件, 找出總成本最高的資料, 第二個 avg_user_impact 為對使用者的影響, 後面三個最重要了, [EqualityUsage]是指等於的條件, 例如 newsid=235 這種條件, 而 [InequalityUsage] 就是指不等的使用, 例如: newsid < 235, 而 [Include Columns]是指查詢時的涵蓋欄位, 也就是指 select newsid, newstitle, newsdesc …. from 前面的欄位.

再來談談有關索引欄位的建立, 上述的後三個欄位就都是要件, 基本上, Equality, Inequality 是指 where 使用的比較欄位, 而 Include Columns 是查詢出來的覆蓋欄位, 至於要如何下這個索引, 成本最粗的下法是將 Equality + Include Columns 加入或 Inequality + Include Columns 加入, 這樣就會有查詢較佳的效能, 但會不會是最好的, 也還是得看異動的頻繁度來考量, 而該索引有沒有價值, 也是必須要評估的, 這裡介紹的方法是將沒有建上的索引資料整理出來列表給管理員來參考用的, 可以節省許多追蹤上的時間. 希望對於效能管理上對各位能有所幫助!!

參考資料:
http://msdn.microsoft.com/zh-tw/magazine/cc135978.aspx

利用CTE將分頁資料取出-Stored Procedure篇

之前在 用CTE來取出指定筆數 – SQL2005 這篇介紹了如何利用 CTE 來取出指定的筆數, 這篇文章將進一步實作一支 stored procedure 做為方便分頁的方式.

主要是為了方便取出指定的筆數來實作的, 所以傳入的參數很單純, 就是原本的查詢指令, 將查詢及排序條件分開, 並指定開始的索引值和結束的索引值即可, 建立的程式碼如下:

create proc sp_getRecordByCTE 
@sqlcmd nvarchar(4000),
@ordercmd nvarchar(1000),
@startindex int,
@endindex int
as
declare @mycmd nvarchar(4000)
select @mycmd = '
with myctequery as (
select row_number() over ( ' + @ordercmd + ' ) 
as CTESN, '+replace(@sqlcmd,'select ',' ')+' )
select * from myctequery 
where CTESN between ' + convert(nvarchar,@startindex) + ' and '+convert(nvarchar,@endindex) 
exec sp_executesql @mycmd

其中比較特別的是為了方便輸入起見, 只要將原來查詢指令的非排序部分和排序部分分開即可, 簡單範列如下:

原來的查詢是:

select a.orderid, a.orderdate, b.custname, b.custaddr from orders a inner join cust b on a.ordercustid = b.custid order by a.orderdate desc

利用上面的 stored procedure 做指定分頁的筆數若為 51~100 (每頁50筆的第二頁), 先將指令區分為紅色查詢及藍色排序, 並且如下輸入給 sp_getRecordByCTE:

exec sp_getRecordByCTE
'select a.orderid, a.orderdate, b.custname, b.custaddr from orders a inner join cust b on a.ordercustid = b.custid',
'order by a.orderdate desc',
51,100

便能將指定的分頁第二頁(51~100)這些資料取出, 這樣和使用 ADO 分頁比較, 不僅可以降低 network IO 外, 更能在複雜查詢時提升效能, 減少不必要的資源浪費, 對於網頁分頁使用時有很大的幫助. 不過目前尚有一個問題無法解決, 就是總筆數這個部分, 因為利用 ADO 查詢時, 可以取得總筆數這個資料, 利用這個 stored procedure sp_getRecordByCTE 將無法取得這個資訊, 看是否有沒有什麼好方法來做這個資料的取得及回傳, 若各位有什麼好建議也歡迎給我建議!

希望這個 stored procedure 對各位能有所幫助.

繼續閱讀:

方便好用的Database Mail – SQL2005

有在用 SQL 2000的使用者還記得 SQL Mail吧. SQL Mail是利用MAPI的方式進行mail的傳遞, 在實務上往往會使用outlook配合exchange server來進行環境的建立, 也造成在使用SQL Mail前, 要準備好這些環境. 但在一般的應用上, 要使用SQL Mail, 又要有這些環境, 不見得很容易實現. 另外再加上SQL Mail在cluster環境時, 也要注意兩台的MAPI profile的設定, 才不會發生發不出mail的問題.

是的, 你的不方便, MS都知道, 到 SQL 2005後, 你會發現多了一個 Database Mail 的功能, 本篇文章就是來介紹這個方便又好用的 SQL Server發信方式. Database Mail 是基於 SMTP的方式來寄送信件, 所以很容易設定, 不太需要像 MAPI 需要 outlook 介面及 exchange server 配合寄送, 當然還是要建立 profile, 好讓 SQL Server 明白你要發信的帳號. 不過因為是走 SMTP, 所以設定也就容易多了. 而且 Database Mail 是能完全支援 cluster 的(因為是用 SQL Server 的機制, 而非外部的資源).

閱讀全文 “方便好用的Database Mail – SQL2005”