[SQL] Trigger - 避免大量更新或大量刪除

朋友傳來的一個悲劇討論，大意就是進行更新時沒有下 WHERE 導致全部的資料都被更新，在 MS SQL 內可以在重點 Table 上，利用 instead of Trigger 來避免這種悲劇

利用 instead of Trigger 禁止一筆資料以上的更新或刪除，下面為 Script

[SSRS] 格式化條件 - 顯示圖片

網友問題，要分析逗號字串資料，並依資料內容來顯示所屬欄位圖形，如下圖

[SQL] 觀看執行計畫重點

執行計畫可能會很龐大，要每個路徑都去觀察是一件相當費時的事情，從效能調校角度來觀察的話，可以先開啟 SET STATISTICS IO ON 來了解 Logical Read 最高是發生在哪一個 Table，先針對執行計畫內該 Table 相關執行路徑去觀察；利用 SET SHOWPLAN_ALL ON 改成文字執行計畫，複製至記事本上，再用 Ctrl + F 去搜尋該 Table 並觀察相關路徑也是一個方法，以下列出常見的觀察重點：

1. 減少資料量：

執行計畫中的箭頭符號代表資料量，資料量越大，箭頭會越粗，滑鼠移到箭頭上方會出現明細資料，可以觀察是否沒有適合的 WHERE 條件而抓取太多資料出來處理

2. 統計資訊老舊

觀察 operator 上的［實際資料列數量］和［估計的資料列數量］是否差異太大，差異太大代表，表示統計資訊需要更新

• [SQL] 統計資訊老舊
• [SQL] 大量更新造成統計資訊老舊

3. Index 使用方式是否合理

Index 使用有兩種方式：Index Seek 或 Index Scan，理論上使用 Index Seek 效能會比 Index Scan 佳，但假如抓取大量資料，利用 Index Scan 反而會比 Index Seek 更佳

4. 書籤查詢（Bookmark lookup）、鍵查詢（Key Lookup ）

SQL 2005 SP2 開始 Bookmark lookup 更名為 Key lookup，代表 T-SQL 語法沒有滿足 covering index，可以觀察 T-SQL 是否有抓取多餘欄位資料或建立包含索引來避免

• [SQL] 具有內含資料行的索引

5. missing index

在 SSMS 內執行 T-SQL 語法，假如有 missing index 的話，會利用綠色文字來說明，在綠色文字說明上，點選滑鼠右鍵 => 遺漏索引詳細資料，就會建立 missing index 語法

• missing index 相關筆記

6. 避免平行處理

OLPT 小交易系統，跑出平行處理通常代表耗能警訊，下左圖執行計畫中的 operator 上有黃色方向左雙箭頭在上面，就代表平行處理

7. 排序

盡量避免在 SQL Server 中進行排序

• [SQL] 複合索引 - 排序
• [SQL] STRING_SPLIT 執行計畫
• [SQL] 避免 Order By

8. 避免隱含式轉換發生

Operator 中的述詞（就是指 WHERE 條件）是否有隱含式轉換，會造成 Index Scan

• 隱含轉換相關筆記

9. Warning 警訊

SELECT operator 中可見右下角的警訊圖式，滑鼠移到該 operator 上就可以觀察警訊內容

10. 成本較高的 Operator

在執行計畫中每個 operatore 下都會有一個［成本：？％］資訊，代表此 operator 佔整個執行計畫總成本的？％，可以先針對高成本 operator 進行了解

參考資料
• 論壇問題出處
• 圖形執行計畫圖示

[SSRS] 使用 Split 分割字串

被論壇問題誤導，此篇變成練習 SSRS 中利用 VB.NET Split 來分割逗號字串資料

DataSet T-SQL 語法

CREATE TABLE SplitData (Data varchar(10))
INSERT INTO SplitData VALUES
 ('1,2,3,4'),
 ('1,2,3,'),
 ('1,2,,'),
 ('1,,,'),
 ('1,,4'), -- 不滿足四位
 ('2,4')   -- 不滿足四位

[SQL] 具有內含資料行的索引

Nonclustered index 是由 Index Key、included column 和 Clustered Key 組成，利用 Nonclustered index 搜尋資料，會先利用 Index Key 來搜尋，假如利用 Index Key 就搜尋完全部資料，此情況稱為 Covering Index（覆蓋索引）；相反地，無法在 Index Key 上找到全部資料，就會進一步利用 Clustered Key 進入 Clustered Index 內去搜尋所需要資料，此行為稱為索引鍵查閱（Key Lookup），2005 SP2 之前稱為書籤查詢（Bookmark Lookup），會增加鎖定和執行時間

建立［具有內含資料行的索引］（文內簡稱包含索引）來滿足 Covering Index 進而消除 Key Lookup 來提高效能，就是此篇文章要說明的

建立包含索引的簡易語法

CREATE INDEX IndexName
ON SchemaName.TableName (Col1,Col2,Col3.......) 
INCLUDE (ColA,ColB,ColC.......)

利用 AdventureWork 的 Sales.SalesOrderHeader 來說明

-- Step 1：針對 OrderDate 建立 nonclustered index
CREATE INDEX IX_OrderDate ON [Sales].[SalesOrderHeader] (OrderDate)

-- Step 2：找出 OrderDate = '20040729' 的 SalesOrderID、OrderDate 和  CustomerID 這三個欄位資料
SELECT 
    SalesOrderID , 
    OrderDate ,
    CustomerID
FROM [Sales].[SalesOrderHeader]
WHERE OrderDate = '20040729'

下圖為 Step 2 執行計畫，可以看出因為 IX_OrderDate 無法滿足 Covering Index 所以利用 Clustered Index 搭配 Nested Loop（巢狀迴圈）一筆一筆資料進入PK_SalesOrderHeader_SalesOrderID 抓取資料

把滑鼠移到［索引鍵查閱］上，會顯示下列資訊，可以觀察到是為了要找 CustomerID 資料（綠色框框），所以利用［索引鍵查閱］（紅色框框）進入PK_SalesOrderHeader_SalesOrderID （綠色框框）抓取資料

-- Step 3：刪除 IX_OrderDate 
DROP INDEX IX_OrderDate ON [Sales].[SalesOrderHeader]

-- Step 4：建立包含索引
CREATE INDEX IX_OrderDate 
ON [Sales].[SalesOrderHeader] (OrderDate)
INCLUDE (CustomerID) -- 包含 CustomerID 欄位

-- Step 5：再次執行查詢語法
SELECT 
    SalesOrderID , 
    OrderDate ,
    CustomerID
FROM [Sales].[SalesOrderHeader]
WHERE OrderDate = '20040729'

下圖為 Step 5 執行計畫，可以觀察到因為 CustomerID 已經包含在 IX_OrderDate 內，不再需要［索引鍵查閱］去搜尋資料

Key Lookup 只會利用 Nested Loop 來抓取資料，是一個極耗成本的動作，是進行 Perfomance Tuning 時的觀察重點，而除了建立包含索引來避免，撰寫 T-SQL 語法時，也要避免抓出不必要的資料，SELECT * 的寫法更是大忌喔

• 2011 SQL Hero

• 參考資料
• 叢集與非叢集索引說明
• 具有內含資料行的索引
• [SQL SERVER]書籤查閱是什麼
  

[SQL] Partition Function

Partition Function 會指定如何分割資料表或索引並將資料對應到單一 Partition，建立 Partition Funcation，要決定

1. DataType：資料型態必須和 Partition Column 的資料型態一致
2. Boundary Value：資料界限值
3. Range Left、Range Right：資料界限值是屬於 Boundary Value 的左邊還是右邊的 Partition

[SQL] Partition Table - Big Table

論壇網友問題

我都已經到3GB多了，那代表說，更應該馬上針對大的Table做分割，是吧?
即使所謂的大Table筆數還不到200萬筆?

Partition Table 是針對 Big Table 的進階技巧，而所謂的 Big Table 並不單指 Table Size、資料筆數這兩個較直覺的考量因素，還有其他因素應該考量進去，例如

1. 該 Table 是效能瓶頸 => 分割成多個 Partition 來提升效能
2. 索引維護困難 => Rebuild Index 耗費時間，用 Partition 可以針對單一 partition index 進行 rebuild
3. 定期 Insert 資料或定期 Delete 舊資料會很耗時，且影響系統正常運作 => 用 Partition 減少 Lock 層級造成的影響

請把該 Table 對於整體效能的影響也列入考量，對於效能有所提昇的前提下，建立 Partitoon 才有意義，要不然不是只增加維護困難度而已。

參考資料
• 論壇問題出處
• Partitioned Table and Index Strategies Using SQL Server 2008 - P7 Choosing a table to Partition 章節內容

[SQL] 利用執行計畫比較成本

Perfomance Turning 時，假如要比較 TSQL 語法修改後是否有差異，可以同時執行並觀察執行計畫中的［相對於批次 ? %］來了解成本差異，但［相對於批次 ? %］是根據［估計的子樹成本］來當成比較依據，並非實際執行計畫成本，這一點要特別注意

利用 AdventureWork SalesOrderHeader 來說明：
下面兩個 TSQL 語法都是要搜尋 SalesOrderID = 73460 這筆資料，但 TSQL2 強制指定 Query Optimizer 使用 IX_SalesOrderHeader_CustomerID Index 來搜尋資料

-- TSQL1
SELECT * 
FROM [Sales].[SalesOrderHeader] 
WHERE SalesOrderID = 73460

-- TSQL2
SELECT * 
FROM [Sales].[SalesOrderHeader]
WITH(INDEX(IX_SalesOrderHeader_CustomerID)) -- 強制指定索引
WHERE SalesOrderID = 73460

從執行計畫中的紅框框就可以比較孰優孰劣

[SSRS] 子報表

利用採購單來練習子報表（內嵌報表）

• DataSet T-SQL 語法

IF OBJECT_ID('Purch') IS NOT NULL
  DROP TABLE Purch

IF OBJECT_ID('PurchDetail') IS NOT NULL
  DROP TABLE PurchDetail

IF OBJECT_ID('Employ') IS NOT NULL
  DROP TABLE Employ

IF OBJECT_ID('Supplier') IS NOT NULL
  DROP TABLE Supplier

CREATE TABLE Purch(PurNO char(11),PurDate date,PurEmpNO char(5),SPLNO char(5))
CREATE TABLE PurchDetail(PurNO char(11),MtNum char(16),Spec nvarchar(100),PurQty int,Price money,Unit nchar(4),DealDate date)
CREATE TABLE Employ (EmpNO char(5),EmpName nchar(10))
CREATE TABLE Supplier (SPLNO char(5),SPLName nchar(10),Contact nchar(10),Tel nchar(10),Fax nchar(10))

INSERT INTO Employ VALUES
  ('001',N'王小明'),
  ('002',N'李大白')

INSERT INTO Supplier VALUES
  ('001',N'雜物供應商',N'王XX','00-1234567','00-1234444'),
  ('002',N'電熱供應商',N'張OO','00-3217654','00-3217777'),
  ('003',N'噴漆供應商',N'蔡YY','00-9999999','00-9999990'),
  ('004',N'馬達供應商',N'柯ZZ','00-6666666','00-6666666')

INSERT INTO Purch VALUES
  ('20131021001','20131021','001','002'),
  ('20131021002','20131021','002','004')
 
INSERT INTO PurchDetail VALUES
  ('20131021001','557D118L050',N'電熱_230V 1180x500W',60,231,N'支','20131028'),
  ('20131021001','557D146L065',N'電熱_230V 1460×650W',60,251,N'支','20131028'),
  ('20131021001','557D194L065',N'電熱_230V 1940x650W',36,278,N'支','20131028'),
  ('20131021002','554819',N'馬達_10"_200～240V 50/60Hz_雙轉向_UL認証',4000,310,N'顆','20140101'),
  ('20131021002','553201B',N'馬達_12"_4P_220V_雙轉向_無保護器',600,340,N'顆','20131128'),
  ('20131021002','552215B',N'馬達  12" 75/46W 4P_200-240V_左右轉_散熱',600,463,N'顆','20131128'),
  ('20131021002','5546257',N'馬達_1/4HP 3ψ  380~420V50/60Hz_三片式外殼',300,828,N'顆','20131101')

-- 子報表 DataSet（dsPurch）T-SQL 語法
SELECT 
  P.* , 
  E.EmpName , 
  S.SPLName , 
  S.Contact , 
  S.Tel , 
  S.Fax
FROM Purch AS P
  JOIN Employ AS E ON P.PurEmpNO = E.EmpNO
  JOIN Supplier AS S ON P.SPLNO = S.SPLNO

-- 子報表明細 DataSet（dsPurchDetail）T-SQL 語法
SELECT * FROM PurchDetail WHERE PurNO = @PurNO

[SQL] View 和 WHERE 條件

論壇上問題，網友疑問

對於「檢視表」，我一直有種「用它是否有錯」的感覺，因為它的優點雖然是「方便我們在寫 SQL 查詢式時，不需要寫一大串 JOIN 的資料表，只要將它們組合成一個檢視表即可」!但問題是，將檢視表用在程式中的SQL查詢式時，例如:

SELECT xxx,ooo FROM View1 WHERE v_date BETWEEN '2013/6/1' AND '2013/6/2'

看起來，雖然有經過WHERE條件的篩選，但是，它是不是「其實是先經過了一個"完全沒有篩選"的"子查詢"」，然後取回了「全部的資料」再給「WHERE」來篩出範圍內的資料呢?

如果原理真的是這樣，那檢視表不是應該少用為妙嗎???......因為一用它就會產生效能問題?

[Security] Skype 病毒 - invoice_xxxxxx.pdf.exe

• 20131105 

中午吃完飯，正想要休息時，收到同事 Skype 訊息，打開一看臉都綠了，一看就是中毒後自動傳送檔案，而且災情已經擴散出去啦，Orz

幸好 Skype 官網已經有網友提供的官方回應，可以解決此問題，步驟如下：

親愛的PChome & Skype使用者，您好：
謝謝您的來信，我們已初步瞭解您的使用狀況，以下為Skype原廠所提供的相關資訊，提供您參考，希望對您有所幫助：

http://community.skype.com/t5/Windows-desktop-client/Invoice-xxxxxx-pdf-virus/m-p/2059261#M198187

操作方式請參考：
請先完全關閉Skype，掃毒並確認病毒全數清除完畢後，再重新依照以下步驟執行Skype。

1. 完全關閉Skype
a) 系統列Skype圖示按右鍵，選擇「結束」
b) ctrl-alt-del 啟動「工作管理員」＞「處理程序」＞「Skype.exe」＞「結束處理程序」
2. 到「開始」＞「搜尋」＞「執行」，輸入 %appdata%，按「確定」送出
3. 找到「Skype」資料夾，將資料夾重新命名，可改名為「Skype_old」
4. 若您使用以下Skype版本：6.5 / 6.6 / 6.7 / 6.9 / 6.10，請到「開始」＞「搜尋」＞「執行」＞ 輸入 %temp%\skype，按「確定」送出
5. 移除 DbTemp 這個資料夾
6. 重啟 Skype

若需要重新建立對話記錄，請參考以下步驟：
1. 結束Skype應用程式
2. 回到先前重新命名的 Skype_old 資料夾
3. 點入有您的帳號名稱的該資料夾
4. 找到 main.db （請確認您可以看到副檔名），並將該檔案完全複製到新的Skype / 您的Skype帳號資料夾下
5. 重新啟動Skype

再次提醒您：請勿點擊不明連結，下載或安裝任何可疑的檔案，它將很有可能為病毒或木馬程式或隱藏性質的收集者，安裝於您的電腦中並進而竊取您的資料。

利用上述方法讓 Skype 恢復正常，只有一台電腦（OS 為 Win7）用此方法後，Chrone 竟然打不開，也無法重新安裝，最後是利用 Windows 還原，還原至前一天的 1900，來解決問題，Orz ~~

• 20131108

PTT 網友發現 Win7 上的 Chrone 相容性層級被改為 XP SP2 改回來 Chrone 就正常啦

• 20131113

確定 Skype 都恢復正常，結案 ~~

參考資料
• Skype 官網說明
• PTT 討論 1 、2、3
• 力通資訊、江民防毒

[SSRS] 複選參數

練習 SSRS 複選參數設定

• DataSet T-SQL 語法

IF OBJECT_ID('Checked') IS NOT NULL
 DROP TABLE Checked
GO

CREATE TABLE Checked (DepID char(3) , DepName char(10) , EmpName char(10))
INSERT INTO Checked VALUES
    ('001','MIS','MIS01') ,
    ('001','MIS','MIS02') ,
    ('001','MIS','MIS03') ,
    ('002','HR','HR01') ,
    ('003','Sales','Sales01') ,
    ('003','Sales','Sales02') ,
    ('003','Sales','Sales03') ,
    ('003','Sales','Sales04') ,
    ('004','Design','Design01') ,
    ('004','Design','Design02') ,
    ('','','UNKOWN') -- 故意建立

在 SSRS 內要設計複選參數功能，有一先決條件是該參數在 T-SQL 語法中的 WHERE 必須使用 IN

WHERE Column IN (@Parameters)

[SQL] 大量匯入與 Trigger 觸發

有三種大量匯入資料方式，分別為

1. BCP
2. BULK INSERT
3. INSERT ...SELECT * FROM OPENROWSET(BULK...)

這三種大量匯入進行時對於 Trigger 的影響有所差異，整理在下面表格

大量匯入	預設行為	限定詞	限定詞類別
BCP	停用	-h " FIRE_TRIGGERS "	提示
BULK INSERT	停用	FIRE_TRIGGERS	引數
INSERT ...SELECT * FROM OPENROWSET(BULK...)	觸發	WITH(IGNORE_TRIGGERS)	資料表提示

[SQL] 刪除重覆資料

刪除 Table 中重覆的資料並保留一筆資料。

• Sample Data

DECLARE @Temp TABLE(Data char(1))
INSERT INTO @Temp VALUES
    ('A') ,
    ('B') ,
    ('C') ,
    ('A') , -- 重覆
    ('B') , -- 重覆
    ('D') ,
    ('E') ,
    ('F') ,
    ('G') ,
    ('F')   -- 重覆

• 方法一：利用暫存資料表

SELECT DISTINCT Data INTO TempData
FROM @Temp
 
DELETE FROM @Temp -- 實體 Table 可以用 TRUNCATE
 
INSERT INTO @Temp
SELECT *
FROM TempData

DROP TABLE TempData

• 方法二：跑迴圈來刪除

WHILE 1 = 1
BEGIN
  DELETE TOP (1) 
  FROM @Temp
  WHERE Data IN
      (
          SELECT Data
          FROM @Temp
          GROUP BY Data
          HAVING COUNT(*) > 1
      )
 
  IF @@ROWCOUNT = 0
      BREAK ;
END

[SSRS] 清單和矩形差異

一開始使用清單和矩形時，有點搞不清楚，此篇為研究後的筆記

• 清單（List）

清單資料區域會隨報表資料集中的每一個群組或資料列重複。 清單可以用於建立自由形式的報表或表單 (如發票)，也可以與其他資料區域一起使用。 您可以定義包含任何數目之報表項目的清單。 清單可以巢狀放在另一份清單內，以提供多個資料群組。

• 矩形（Rectangle）

使用矩形做為其他控件的容器。當移動矩形時，包含在矩形中的項目會跟著它移動。矩形內部的項目會以其 Parent 屬性顯示矩形的名稱。

• 文字說明兩者差異

清單會逐筆顯示所連接資料集和群組後的詳細資料，而矩形只是一個形狀、容器，用來群組控件用。

• 設計界面差異

一開始打開書本範例，直覺是長的都一模一樣是要怎麼分辨哪個是清單，那個是矩形

想說屬性視窗總會顯示是那個控件，沒想到清單和矩形在屬性視窗內都是 Rectangle 矩形

後來發現，點擊控件就可以看出兩者差異，清單會出現資料列和資料行控點，矩形則是會出現被點選的外框線和十字移動符號。

參考資料
• 清單
• 矩形和線條、加入矩形或容器
• [SQL Server 2008R2][SSRS] 巢狀清單

[SQL] 工作天判斷

利用 CTE 產生完整日期資料表，搭配一個例外 Table 來輔助判斷，例外 Table 的設立主因是無法預測人事行政局到底是如何規劃假期、防災假等原因。

• 利用 201302 來說明，23 號上班日是補 15 號彈休。

[VFP] 更換 VFP IDE 介面字型

字型大小對於閱讀 code 有很大的幫助，記錄一下如何更改 VFP IDE 字型和字型大小

• Task Panel Manager => Tools => Options

• IDE Tag 內進行設定

1. Type：可以選擇針對哪個 IDE 介面來進行設定修改
2. Font：選擇字型和字型大小
3. Override individual settings：勾選此設定，才可以覆蓋各 Form 現有的設定
4. Apply button：套用上述設定
5. Set As Default button：把上述設定改為預設值，之後再新增 Form、Program 等時，就會套用此設定。

參考資料
• 如何設定vfp的作業環境

Office 2013 Picture Manager

品管小姐來詢問為甚麼 Office 2013 內沒有 Picture Manager，是不是沒有安裝上去，依自身安裝習慣，只要是 Office 軟體，一定是全部安裝，後來發現 Picture Manager 從 Office 2013 開始被拿掉了，Orz ~~

• 官方說明

[SSRS] 隔行換色

閱讀 MSDN SSRS RowNumber() 函數 時發現文章內的範例，動手實作並記錄

語法：RowNumber(scope)
參數：指定評估資料列數的範圍，可能為資料集、資料區域、群組名稱或 Nothing

• DataSet T-SQL 語法

SELECT FirstName , LastName , EmailAddress , Phone 
FROM [Person].[Contact]

• 設定介面

先利用精靈建立 Table 表格，並在前方建立一個由 RowNumber() 函數建立的序號欄位，方便驗證設定效果

• 設定 BackgroundColor 運算式

Tablix 資料區域詳細資料列 => 屬性 => BackgroundColor => 運算式

設定運算式

= IIF(RowNumber(Nothing) MOD 2 = 0 , "LightGrey" , "White")

• 預覽結果

• 2013 DBA 天團

• 參考資料
• RowNumber 函數
• 如何在 SSRS 中設定資料表控制項偶數列顏色與奇數列不同 - 有存在影像控制項，則無法順利變色
• 透過設定報表變數改變單雙列的背景顏色
  

[SQL] OLE DB 提供者 Microsoft.ACE.OLEDB.12.0 尚未註冊

論壇問題

使用 OPENROWSET() 來抓取 Excel 資料時，出現"OLE DB 提供者 Microsoft.ACE.OLEDB.12.0 尚未註冊" 的錯誤訊息，該如何註冊？

開啟伺服器選項 Ad Hoc Distributed Queries

使用 OPENROWSET 時，必須先開啟伺服器選項 Ad Hoc Distributed Queries 才可以使用，沒有開啟的情況下，會出現下面的錯誤訊息

SQL Server 已封鎖元件 'Ad Hoc Distributed Queries' 的 STATEMENT 'OpenRowset/OpenDatasource' 之存取，因為此元件已經由此伺服器的安全性組態關閉。系統管理員可以使用 sp_configure 來啟用 'Ad Hoc Distributed Queries' 的使用。如需有關啟用 'Ad Hoc Distributed Queries' 的詳細資訊，請在《SQL Server 線上叢書》中搜尋 'Ad Hoc Distributed Queries'。

有兩種開啟方式

• SSMS 內開啟：Instance => 右鍵 Facset => 介面區組態 => AdHocRemoteQueriesEnable 設為 True

• 利用 T-SQL 語法開啟

sp_configure 'show advanced options' , 1
reconfigure
GO 
sp_configure 'Ad Hoc Distributed Queries' , 1 
reconfigure
GO

[SSRS] 柏拉圖（Pareto Chart）

柏拉圖就是 80/20 法則的圖表應用，在品管上常用來顯示不良率，為舊品管七大手法之一。

• DataSet T-SQL 語法

USE [AdventureWorks2012]
GO 

IF OBJECT_ID('QC') IS NOT NULL
    DROP TABLE QC

CREATE TABLE QC (Question nchar(20) , Qty int , Line numeric(3,2))
INSERT INTO QC VALUES
    (N'馬達運轉異常' , 38 , 0.8) ,
    (N'馬達異因' , 22 , 0.8) ,
    (N'運輸撞傷' , 6 , 0.8) ,
    (N'鰭片間距不良' , 0 , 0.8) ,
    (N'銅管凹陷' , 2 , 0.8) ,
    (N'組裝不良' , 17 , 0.8) ,
    (N'洩漏' , 3 , 0.8) 

[SQL] 利用 sqlcmd 建立 Login

在測試環境練習時，一時手殘竟然把唯一的 Login 給刪除掉，再加上 sa 根本就沒有開啟，導致完全無法進入和操作 SQL Server 的窘境。

模擬錯誤發生

• 刪除唯一的 Login Win2008R2\Administrator

• 利用 SSMS 嘗試登錄 SQL Server

• 無法登錄

[SSRS] 彙總函數 RunningValue

在 T-SQL 中要做到累計加總（Running Total）的功能，可以透過 Window 函數來達到，而在 SSRS 中，則是可以利用彙總函數 RunningValue。

語法：RunningValue(expression, function, scope)
參數：

1. expression：要進行彙總的目標，通常都是欄位
2. function：要使用的彙總函數，EX：SUM()、AVG()、COUNT()、COUNTDISTINCT()
3. scope：彙總範圍，可能為資料集、資料區域或群組名稱

• DataSet T-SQL 語法

USE [AdventureWorks]
GO

IF OBJECT_ID('SalesCase') IS NOT NULL
  DROP TABLE SalesCase

CREATE TABLE SalesCase (SalesName nvarchar(10) , Product nvarchar(100) , Price money)
INSERT INTO SalesCase VALUES
  (N'張三' , N'SQL Server 2012' , 350) ,
  (N'張三' , N'Report Service 2012' , 400) ,
  (N'張三' , N'Big Data' , 500) ,
  (N'李四' , N'ASP.NET' , 333) ,
  (N'李四' , N'HTML 5' , 444) ,
  (N'李四' , N'CSS & HTML' , 200) ,
  (N'李四' , N'Visual Studio 2012' , 345) ,
  (N'王五' , N'Powershell' , 513)

• 設計界面

先利用精靈建立 Table 表格，設定 SalesCase 群組並在最後新增 RunningValue 欄位

• 文字方塊運算式

RunningValue 文字方塊的運算式，輸入下列運算式

=RunningValue(Fields!Price.Value,SUM,"SalesName")

• 預覽結果

• 延伸閱讀
• [Challenge] 累計加總（Runing Total）
• 參考資料
• RunningValue 函數
• [SQL Server 2008R2][SSRS] 彙總函數 RunningValue - 對於 Scope 有較深入的說明

[SQL] 避免隱含式轉換

在論壇上看見這樣的 WHERE 篩選條件

AND CAST(dbo.SalaryMonth.SalaryYear - 1911 AS nvarchar(3)) + RIGHT(CAST(dbo.SalaryMonth.Month + 100 AS nvarchar(3)), 2) = 10209

其問題在於這篇 [SQL] WHERE 中的資料篩選 所說不要對日期欄位進行轉換，還有另一個問題是隱含式轉換，所謂隱含式轉換是指兩個不同資料形態進行比較，資料型態彼此相容，因此 Query Optimizer 會自動對資料型態進行轉換，轉換成相同資料型態後才會進行比較，這也會產生效能問題，要盡量避免。

資料型態優先順序（MSDN 內容）

當一個運算子結合兩個不同資料類型的運算式時，資料類型優先順序的規則，會指定將低優先順序的資料類型，轉換為高優先順序的資料類型。 如果轉換不是支援的隱含轉換，就會傳回錯誤。 如果這兩個運算元運算式的資料類型相同，則作業結果就含有該資料類型。

SQL Server 會使用下列優先順序：

1. 使用者自訂資料類型 (最高)
2. sql_varian t
3. xml
4. datetimeoffset
5. datetime2
6. datetime
7. smalldatetime
8. date
9. time
10. float
11. real
12. decimal
13. money
14. smallmoney
15. bigint
16. int
17. smallint
18. tinyint
19. bit
20. ntext
21. text
22. image
23. timestamp
24. uniqueidentifier
25. nvarchar (包括 nvarchar(max) )
26. nchar
27. varchar (包括 varchar(max) )
28. char
29. varbinary (包括 varbinary(max) )
30. binary (最低)

[SSRS] 階梯狀報表

傳統資料表報表會將父群組放在報表的相鄰資料行中，而階梯狀報表會在相同的資料欄中，顯示父群組底下縮排的詳細資料列或子群組。

以下實作是為了顯示各分公司各部門的事病假報表

• DataSet T-SQL 語法

USE [AdventureWorks2012]
GO

IF OBJECT_ID('Leave') IS NOT NULL
 DROP TABLE Leave

CREATE TABLE Leave (Branch nchar(4) , DepName nchar(20) , EmpName nchar(20) , personal numeric(4,1) , Sick numeric(4,1))
INSERT INTO Leave (Branch,DepName,EmpName,Personal,Sick) VALUES
  (N'台北',N'MIS',N'MIS-1',0.0,0.0)  ,
  (N'台北',N'MIS',N'MIS-2',8.0,8.0)  ,
  (N'台北',N'HR',N'HR-1',8.0,0.0)    ,
  (N'台中',N'Sales',N'Sales-1',0.0,4.0)   ,
  (N'台中',N'Sales',N'Slaes-2',8.0,8.0)  ,
  (N'台中',N'Marketing',N'Marking-1',8.0,4.0)  ,
  (N'高雄',N'Accounting',N'Accounting-1',0.0,0.0)  ,
  (N'高雄',N'Accounting',N'Accounting-2',0.0,0.0)  ,
  (N'高雄',N'Accounting',N'Accounting-3',0.0,0.0)  ,
  (N'高雄',N'Design',N'Design-1',0.0,0.0)  ,
  (N'高雄',N'Design',N'Design-2',0.0,0.0) 

[SQL] WHERE 中的資料篩選

效能議題中，有個原則是不要對欄位進行任何轉換，而最常見的轉換莫過於利用 YEAR()、DATEADD() 、CONVERT() 等函數，對日期欄位進行轉換。

• 利用 AdventureWorks2012 的 SalesOrderHeader Table 說明

-- 針對 OrderDate 建立 Index
CREATE INDEX [IX_SalesOrderHeader_OrderDate] ON dbo.SalesOrderHeader (OrderDate)

-- 跑這 4 個 T-SQL 語法來觀察執行計畫中 Index 使用情況
SELECT OrderDate
FROM [Sales].[SalesOrderHeader]
WHERE SUBSTRING(CONVERT(varchar(10),OrderDate,112),1,4) = '2007'

SELECT OrderDate
FROM [Sales].[SalesOrderHeader]
WHERE CONVERT(varchar(10),OrderDate,112) LIKE '2007%'

SELECT OrderDate
FROM [Sales].[SalesOrderHeader]
WHERE DATEPART(yyyy,OrderDate) = 2007

SELECT OrderDate
FROM [Sales].[SalesOrderHeader]
WHERE OrderDate BETWEEN '20070101' AND '20071231'

• T-SQL 執行結果和其執行計畫

從上圖中就可以觀察到只要對 OrderDate 日期欄位進行任何轉換動作，都會導致 Query Optimizer 使用 Index Scan，而非 Index Seek 來抓取資料，造成效能低落，T-SQL 語法中假如常常需要進行日期轉換，或許可以考慮除了日期欄位本身外，還可以建立年、月、日資料欄位，來增加資料蒐尋的便利性。

• 2011 SQL Hero 考題

延伸閱讀
• [SQL] 日期欄位資料型態的選擇

參考資料
• 論壇相關問題討論 1、2、3

[SSRS] 分頁頁碼

閱讀 MSDN 文章 Reporting Services 中的分頁 時有點霧煞煞，Google 些資料來了解並實作練習此主題

DataSet T-SQL 語法

利用 AdventureWorks2012，找出人員所屬部門

SELECT 
    D.DepartmentID AS DepID , 
    D.Name AS DepName , 
    P.LastName , 
    P.FirstName  , 
    A.EmailAddress
FROM Person.Person AS P
    JOIN Person.EmailAddress AS A ON P.BusinessEntityID = A.BusinessEntityID
    JOIN HumanResources.EmployeeDepartmentHistory AS H ON P.BusinessEntityID = H.BusinessEntityID
    JOIN HumanResources.Department AS D ON H.DepartmentID = D.DepartmentID
WHERE H.EndDate IS NULL

建立父群組

資料列 => 右鍵滑鼠 => 加入群組 => 父群組

群組依據為 DepID 並勾選 ［加入群組頁首］

DepID 欄位 =>滑鼠右鍵 => 刪除資料行，因為沒有要使用該群組欄位，故意把它刪除

微調版面

把 DepID 和 DepName 資料，放在群組頁首

PageBreak 相關設定

資料列群組欄位 => 內點選 DepID 群組 => 屬性 => Group => PageBreak 相關設定 => 設定如下

1. BreakLocation => BETWEEN
2. Disable => False
3. ResetPageNumber => True
4. PageName => Fields!DepName.Value

各屬性說明

1. BreakLocation：會針對啟用分頁的報表元素，提供分頁的位置：開頭、結尾，或開頭和結尾。 若是群組，BreakLocation 可以位於群組之間
2. Disabled：會指出是否將分頁套用至報表元素。 如果這個屬性評估為 True，則會忽略分頁。 如果使用這個屬性，可以根據報表執行時的運算式，以動態方式停用分頁
3. ResetPageNumber：會指出分頁時，是否應該將頁碼重設為 1。 如果這個屬性評估為 True，則會重設頁碼。
4. PageName：針對分頁所造成的新頁面，提供新的頁面名稱
   

頁首設定

頁碼只能在頁首或頁尾設定

在頁首新增文字方塊

文字方塊的運算式內輸入

= "全部分頁號碼：" & Globals!OverallPageNumber & "／" & Globals!OverallTotalPages & "（OverPageNumber／OverallTotalPages）"

同樣步驟再建立一個文字方塊並在運算式內輸入

= "群組分頁號碼：" & Globals!PageName & " - " & Globals.PageNumber & "／" & Globals.TotalPages & "（PageName - PageNumber／TotalPages）"

調整版面並預覽結果

從下圖可以看出全部資料共分 19 頁，此畫面為第 5 頁， purchasing 群組，總共是 1 頁，此為群組第 1 頁。

從下圖可以看出全部資料共分 19 頁，此畫面為第 7 頁， Production 群組，總共是 4 頁，此為群組第 1 頁。

• 參考資料
• Reporting Services 中的分頁
• Report Design: Reset Page Number on Group
• Reset Page Number On Group

[SSRS] 群組分頁

利用 SSRS 的 RowNumber() 內鍵函數來建立資料群組，再根據此群組來分頁，也可以說是每 X 筆資料就分頁的效果。

DataSet T-SQL 語法

使用 AdventureWorks2012 來捉取資料，並利用 ROW_NUMBER() 把資料順序號碼 show 出來，方便報表產生時，可以拿來比較

SELECT         
    ROW_NUMBER() OVER (ORDER BY E.BirthDate) AS ROWNO ,
    P.LastName, 
    P.FirstName, 
    E.JobTitle, 
    E.BirthDate, 
    A.EmailAddress
FROM Person.Person AS P 
  INNER JOIN HumanResources.Employee AS E ON P.BusinessEntityID = E.BusinessEntityID 
  INNER JOIN Person.EmailAddress AS A ON P.BusinessEntityID = A.BusinessEntityID

建立群組

• 新增一個資料列父群組

• 父群組分類條件

此條件文章內一律稱為 RowNumber() 條件

= INT((ROWNUMBER(NOTHING) - 1) / 10)

• 設定群組屬性

［資料列群組］內，可以看見新增的父群組（Group1），點選右邊向下箭頭，再點選［群組屬性］

群組屬性 => 一般 => 修改群組名稱為 GroupPageBreak，方便自己辨識；群組對象則是新增父群組時，所輸入的 RowNumber() 條件，不需要再修改。

群組屬性 => 分頁符號 => 勾選［在群組的每個執行個體之間］

群組屬性 => 排序，RowNumber() 條件在新增時，也會是排序依據， SSRS 無法在排序時，使用 RowNumber() 函數，因此排序條件要設成其他條件或就不要有排序，要不然預覽時就會出現錯誤

• 預覽報表

從 GroupNO 和 RowNO 兩欄位來觀看設定是否異常，GroupNO 是從 0 開始

移除 GroupNO 欄位

GroupNO 欄位是為了方便檢視設定 RowNumber() 條件才存在，實際設計報表不需要出現

點選 GroupNO 欄位 => 滑鼠右鍵 => 刪除資料行

選擇［只刪除資料行］

預覽報表

• 延伸閱讀
• [SQL] 次序函數應用-資料分組
• 參考資料
• SSRS - Page Break
• RowNumber 函數
• 加入分頁符號
• 運算式範例 - 分頁符號
• [RDLC] ReportViewer 設計系列：設定群組之每頁小計