[SQL] 資料欄位的加解密

練習利用憑證（Certicate）加密金鑰（Key），再利用金鑰（Key）加密欄位資料（Data）來保護重要資料

建立測試基本資料

USE AdventureWorks2012 
GO

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

CREATE TABLE PWData
(
  MemID INT PRIMARY KEY CLUSTERED,
  MemName nvarchar(10),
  PW varchar(100) NOT NULL,
  EncryptPW varbinary(100) -- 資料型態也是重點之一
)
GO

INSERT INTO PWData (MemID,MemName,PW) VALUES
  (1,N'張三','123'),
  (2,N'李四','456'),
  (3,N'王五','789')
GO

[RV] ReportViewer 遠端模式

ReportViewer 遠端模式（ReportViewer Remote Mode）是檢視部署到 SQL Server 2008 開始之後的 Report Service 上報表（rdl）的建議使用方法。

在 VS 2012 上拖曳 ReportViewer 和 ScriptManager 控件

[SQL] 強制指定索引

Top 10 Turning 時，發現同事這段語法非常耗能，調整過索引和了解商業邏輯後去改 T-SQL 語法，最後還是很醜陋的語法，醜陋歸醜陋，但效能有提升就好 ~~

縮減後的 Turning 語法（只列出文章要記錄部分）， 重點在 POItem Table 身上

SELECT 
    DA.custno, 
    sum(DA.price * DA.LEFT_QTY) AS ALLMONEY
FROM
    (
        SELECT 
            bA.custno,
            bA.po,
            iA.model_no,
            iA.opt_no,
            iA.PRICE,
            iA.left_qty,
            iA.asm_memo,
            iA.FINISH_DAY
        FROM pobasic as bA
            JOIN poitem as iA ON bA.odno=iA.odno
            JOIN pmfinishdate as MA ON MA.order_no=IA.ordeR_no AND MA.pid='A1' AND MA.doneqty>0
        WHERE bA.custno <>'09084'
            AND IA.left_qty > 0
    ) AS DA
WHERE DA.finish_day < CAST(CONVERT(char(10),GETDATE(),112) AS datetime)
GROUP BY DA.custno

[SSRS] SSMS Express 連接 Report Service

嘗試要利用 SSMS 連進 Report Service 時，發現竟然沒有辦法使用該功能

查了半天才想起，這是 SQL Server 2012 Express Advance，也許根本沒有支援該功能，果然在 MSDN 上找到說明

SQL Server Express 包括 SQL Server Management Studio Express。SQL Server Management Studio Express 不能用來管理報表伺服器。請改用報表管理員和 Reporting Services 組態工具。您可以使用報表管理員和 Reporting Services 組態工具來管理您在安裝 SQL Server Express with Advanced Services 中的 Reporting Services 時所建立的報表伺服器。雖然安裝套件包含 SQL Server Management Studio Express，不過您無法使用 SQL Server Management Studio Express 來管理報表伺服器。

浪費自己時間，Orz ~~

參考資料
• SQL Server Express with Advanced Services 中的 Reporting Services
• SQL Server Express 中 Reporting Services 所支援的功能

[SQL] 避免重覆的 WHERE 條件 2

一早打開信箱就看見自動成長的通知 mail，打開一看更是傻眼，TempDB 的自動成長 2 分鐘內被觸發 65 次，空間大約增長了 1 G，Orz ~~

利用 TOP 10 語法找疑犯，發現這段 ad-hoc T-SQL 語法，跑出超耗能的結果，且執行時間在 TempDB 自動觸發前後

[VFP] 資料記錄超出範圍

收到 Client 端的錯誤訊息：資料記錄超出範圍（Record is Out of Range）

Debug 時發現這個 Form 只要一遇上 thisform.refresh 就會出現 資料記錄超出範圍（Record is Out of Range）的錯誤訊息，抽絲剝繭後才發現到，我對兩個控件設定了相同的 ControlSource，詳見下圖

cbo 控件不應該存在此 ControlSource 設定，把該設定拿掉就不會有問題啦，結案。
心中最大的疑問是，這個 bug 應該存在很久了，為什麼今天才蹦出來，Orz ~~

[SSRS] 新聞稿樣式報表

新聞稿樣式報表，又稱為多欄位（Multi-Columns）報表，可以讓資料由左而右、由上而下往右邊欄位來顯示

設定重點在於

1. 紙張大小：頁寬、頁高
2. 左右邊界
3. 可用頁面區域：此範例中即為欄位
4. 欄位間距
5. 資料範圍：建議利用 Tablix 或 List 來顯示資料，在此採用 Tablix 來顯示資料

下圖為 MSDN 上截取，上述重點圖示

[SQL] 避免重覆的 WHERE 條件

進行 TOP 10 Turning 時的改善實例，下面兩個 T-SQL 語法片段是從原語法中截取出來

TSQL1：觀察到延伸資料表［W］內已經有下 WHERE 條件［18、19］，但延伸資料表［W］又下了一次相同效果的 WHERE 語法［32、33］

-- TSQL1
SELECT 
    w.order_no , 
    w.pid, 
    W.DDATE, 
    sum(w.qty) AS qty, 
    s.modelcode, 
    s.odno, 
    s.opt_no
FROM
    (
        SELECT 
            ORDER_NO, PID, 
            QTY, 
            (right(replicate('0', 3) + cast(year(date)-1911 AS VARCHAR),3)+RIGHT(REPLICATE('0',2)+CAST(MONTH(DATE) AS VARCHAR),2)) AS DDATE,
            WK_ID
        FROM pmwkok
        WHERE (date BETWEEN '20121101' AND '20131130' ) 
            AND WK_ID= 'D1021'
    ) as w
    join pmsetorder as s on w.order_no = s.order_no
    JOIN
        (
            SELECT 
                K.order_no, 
                min(K.PID) AS PID
            FROM PMWKOK AS K
            WHERE (K.date BETWEEN '20121101'  AND '20131130' )
                AND K.WK_ID = 'D1021'
            GROUP BY K.ORDER_NO
        ) AS V ON V.ORDER_NO = W.ORDER_NO AND V.PID=W.PID
WHERE w.Ddate BETWEEN '10111' AND '10211'
    AND w.wk_id= 'D1021'
GROUP BY W.DDATE,W.order_no,W.pid,s.modelcode,s.odno,s.opt_no

[SQL] JOIN ON 條件的重要性

進行 TOP 10 語法時發現高成本的語法，此"物料語法"是配合生管需求，針對物料去找出最近一次採購記錄、最近一次派工記錄、最近完工日期等資訊，總共 JOIN 18 個 Table，而這次的瓶頸在於要找出，"該產品物料兩年內最新的完工資料（T17 語法）"上

下圖是縮減版的"物料語法"，"T17 語法" 為這次的瓶頸

SELECT 
    ........................
FROM AS 物料
    LEFT JOIN
        (
            SELECT 
                SY.派工單號 ,
                SY.物料識別碼 ,
                SY.表定完成日 ,
                SY.派工數量
            FROM 派工單 AS SY
                JOIN
                    (
                        SELECT 
                            MAX(派工單號) AS 派工單號
                        FROM 派工單
                        WHERE 優先權 = 'P' 
                            AND 選擇性 = '' 
                            AND 是否結案 = 'Y' 
                            AND 派工日期 >= CAST(CONVERT(char(10),DATEADD(YY,-2,getdate()),120) AS DATETIME)
                        GROUP BY 物料識別碼
                    ) AS AX ON AX.派工單號 = SY.派工單號
        ) AS T17 ON M.物料識別碼 = T17.物料識別碼

從 T17 語法執行計畫路徑可以觀察到 [派工單 Table] 抓出很誇張的資料量，該 Table 全部的資料量也才 130,000 筆左右，但卻撈出 1,815,541 筆資料，整個大出 14 倍左右，Orz ~~

T17 語法的疑點：

1. 針對 [物料識別碼] 進行 GROUP BY 後，[物料識別碼] 欄位沒有被列為 JOIN ON 條件之一
2. [派工單號] 彙總後欄位是唯一的 JOIN ON 條件

下列為修改後的 T17 語法，把 [物料識別碼] 也加入 JOIN ON 條件

SELECT 
    SY.派工單號 ,
    SY.物料識別碼 ,
    SY.表定完成日 ,
    SY.派工數量
FROM 派工單 AS SY
    JOIN
        (
            SELECT 
               物料識別碼 ,
               MAX(派工單號) AS 派工單號
            FROM 派工單
            WHERE 優先權 ='P' 
                AND 選擇性 = ''
                AND 是否結案 ='Y' 
                AND 派工日期 >= CAST(CONVERT(char(10),DATEADD(YY,-2,getdate()),120) AS DATETIME)
            GROUP BY 物料識別碼
      ) AS AX ON AX.派工單號 = SY.派工單號
                   AND AX.物料識別碼 = SY.物料識別碼 -- 加上該條件

重新執行該物料語法，下圖為 T17 語法執行計畫路徑

下圖為 T17 語法文字執行計畫，可以明顯看出資料量減少後執行計畫變化

下表為 Logical Read 和 CPU 時間比較表

	Logical Read	整體 CPU Time（ms）
原語法	392,610	6,257
修改後	7,175	888

JOIN ON 條件跟 WHERE 條件一樣重要