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

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

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

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

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

DataSet T-SQL

CREATE TABLE FormatImage (Data varchar(10))
INSERT INTO FormatImage VALUES
 ('S,O,F,C'),
 ('S,O'),
 ('O,F,C'),
 ('S,O,C'),
 ('O,C'),
 ('C')

報表設計

先利用精靈拉出一個簡單的 Table 並新增 SOFC 四欄位

針對 S 欄位插影像控件

影像控件 => 滑鼠右鍵 => 影像屬性 => 一般，選擇事先建立號的［勾圖形］，並內嵌在報表中就好

把圖片內嵌在報表中之後，就可以在［報表資料］=> 影像 中，看見剛剛的［勾圖形］

影像控件 => 滑鼠右鍵 => 影像屬性 => 可見性，點選［fx］button 來設定顯示條件

利用 indexof 來判斷 Data 中有是否有 "S" 字樣，有的話則顯示 S 欄位的勾圖形

= IIF((Fields!Data.Value).indexof("S") > -1 , false , true)

依序完成 OFC 欄位條件設定

第二次插入影像控件，就不需要再進行匯入，直接選取［勾圖形］即可

預覽設定結果

看見預覽結果，根據 VFP 製作報表經驗，只要把背景設定為 Transparent 就可以解決此問題，沒想到 SSRS 影像控件中，根本就沒有 Transparent 設定，>.<

利用矩形包覆影像控件來達到 Transparent 效果

重新設定 SOFC 欄位內的控件，先插入矩形，並在矩形中再插入影像控件，影像控件設定跟之前是完全一樣的，下圖為完成 SOFC 欄位設定後的版面

預覽設定結果

參考資料
• 論壇問題出處
• Transparent Images in Reporting Services
• String.IndexOf 方法
• 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