[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