#眉標=SQL Server 2008、plan freezing
#副標=SQL Server 2008新功能概覽（5）
#大標=實作資料庫的計畫指南與變更追蹤
#作者=文／胡百敬
#引言=


===<反灰>=============
USE Tempdb
GO
CREATE TABLE t(PK INT IDENTITY(1,1) Primary Key,
C2 INT,
C3 DATETIME DEFAULT(GetDate()))
GO
----製造假資料
INSERT t(C2) VALUES(1)
GO
DECLARE @i INT=0
WHILE @i<6
BEGIN
	INSERT t(C2) SELECT 2 FROM t
	INSERT t(C2) SELECT 3 FROM t
	SET @i+=1
END
GO
---建立索引，查詢資料時，讓 SQL Server 有不同的執行方式
CREATE INDEX idx ON t(C2)
==<end>==============



===<反灰>=============
--先清空快取的執行計畫
DBCC FREEPROCCACHE
GO
SELECT * FROM t WHERE C2=2
==<end>==============




===<反灰>=============
SELECT * FROM sys.dm_exec_query_stats AS qs
	CROSS APPLY
		sys.dm_exec_sql_text(qs.sql_handle) AS qt
	CROSS APPLY
		sys.dm_exec_text_query_plan(qs.plan_handle, 
		qs.statement_start_offset,
		qs.statement_end_offset) AS qp
WHERE
	qt.text LIKE N'SELECT * FROM t%';
==<end>==============




===<反灰>=============
DECLARE @plan_handle varbinary(64);
DECLARE @offset int;
SELECT @plan_handle = qs.plan_handle,
　　@offset = qs.statement_start_offset
FROM
	sys.dm_exec_query_stats AS qs
	CROSS APPLY
		sys.dm_exec_sql_text(qs.sql_handle) AS qt
	CROSS APPLY
		sys.dm_exec_text_query_plan(qs.plan_handle,
		qs.statement_start_offset,
		qs.statement_end_offset) AS qp
WHERE
	qt.text LIKE N'SELECT * FROM t%';
EXECUTE sys.sp_create_plan_guide_from_handle 
    @name =  N'pgTest',
    @plan_handle = @plan_handle,
    @statement_start_offset = @offset;
==<end>==============



===<反灰>=============
SELECT * FROM t WHERE C2=2
==<end>==============



===<反灰>=============
DELETE t WHERE C2=2
==<end>==============



===<反灰>=============
IF  EXISTS (SELECT * FROM sys.plan_guides WHERE name = N'pgTest')
	EXEC sp_control_plan_guide @operation = N'DROP', @name = N'[pgTest]'
==<end>==============



===<反灰>=============
CREATE DATABASE dbChangeTrack
GO
USE dbChangeTrack 

CREATE TABLE tbTrack(PK INT IDENTITY(1,1) PRIMARY KEY,
c2 NVARCHAR(50),
c3 NVARCHAR(50)
)
==<end>==============




===<反灰>=============
ALTER DATABASE dbChangeTrack
SET CHANGE_TRACKING = ON
(CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON) 
==<end>==============



===<反灰>=============
ALTER TABLE tbTrack
ENABLE CHANGE_TRACKING
WITH (TRACK_COLUMNS_UPDATED = ON)
==<end>==============




===<反灰>=============
DECLARE @LastSyncVer BIGINT=CHANGE_TRACKING_CURRENT_VERSION() 
--變更資料
INSERT tbTrack VALUES('A','A')
INSERT tbTrack VALUES('B','B')
INSERT tbTrack VALUES('C','C') 
--查閱自最後同步的版本以來之變更
SELECT *
FROM
    CHANGETABLE(CHANGES tbTrack, @LastSyncVer) AS CT 
/*做一些同步的動作，並記載最後同步的版本*/
SET @LastSyncVer =CHANGE_TRACKING_CURRENT_VERSION() 
--在同一批的變更追蹤之間，已做了多次變更，只有最後的結果
UPDATE tbTrack SET c2='AA' WHERE PK=1
DELETE tbTrack WHERE PK=1 
UPDATE tbTrack SET c2='BB' WHERE PK=2 
--透過 ChangeTable 系統函數查詢哪幾筆記錄、哪個欄位、做了什麼樣的更新
SELECT
    CT.PK, CT.SYS_CHANGE_OPERATION,
    CT.SYS_CHANGE_COLUMNS, CT.SYS_CHANGE_CONTEXT
FROM
    CHANGETABLE(CHANGES tbTrack, @LastSyncVer) AS CT 
==<end>==============




===<反灰>=============
--建立資料表，記錄曾經發生過的同步的版本
CREATE TABLE tbSyncRec
(PK SMALLINT IDENTITY(-32768,1) PRIMARY KEY,
LastSyncVersion BIGINT,
SyncTime DATETIME DEFAULT(GetDate())
)
DECLARE @LastSyncVer BIGINT=CHANGE_TRACKING_CURRENT_VERSION() 
INSERT tbSyncRec(LastSyncVersion) VALUES(@LastSyncVer)
GO
--模擬一個需要同步的目標資料表
SELECT * INTO tbSyncTarg FROM tbTrack
GO
--對來源資料表的內容稍作修改
INSERT tbTrack VALUES('D','D') 
UPDATE tbTrack SET c2='DDD' WHERE PK=4
UPDATE tbTrack SET c2='BBB' WHERE PK=2 
UPDATE tbTrack SET c3='CCC' WHERE PK=3 
==<end>==============



===<反灰>=============
--查詢一下同步前的資料
SELECT * FROM tbTrack
SELECT * FROM tbSyncTarg
==<end>==============




===<反灰>=============
DECLARE @LastSyncVer BIGINT
SELECT @LastSyncVer=LastSyncVersion FROM tbSyncRec
WHERE SyncTime=(SELECT MAX(SyncTime) FROM tbSyncRec)
DECLARE @C2Id int = COLUMNPROPERTY(
    OBJECT_ID('dbo.tbTrack'),'C2', 'ColumnId')
DECLARE @C3Id int = COLUMNPROPERTY(
    OBJECT_ID('dbo.tbTrack'),'C3', 'ColumnId')
 -- 顯示變更後的資料內容
SELECT CT.PK,
    --確認個別欄位是否有變更
    CASE WHEN CHANGE_TRACKING_IS_COLUMN_IN_MASK(
              @C2Id, CT.SYS_CHANGE_COLUMNS) = 1
         THEN C2 ELSE N'沒變' END AS C2Col,
    CASE WHEN CHANGE_TRACKING_IS_COLUMN_IN_MASK(
              @C3Id, CT.SYS_CHANGE_COLUMNS) = 1
         THEN C3 ELSE N'沒變' END AS C3Col,
     CT.SYS_CHANGE_OPERATION, CT.SYS_CHANGE_COLUMNS
FROM tbTrack AS t
INNER JOIN
     CHANGETABLE(CHANGES tbTrack, @LastSyncVer) AS CT
ON t.PK = CT.PK
WHERE CT.SYS_CHANGE_OPERATION = 'U'  --第四筆記錄被類歸為Insert所以不會出現
--只同步Update 過的資料到目的資料表
UPDATE tbSyncTarg SET C2=CASE WHEN o.C2Col IS NULL
				THEN C2 ELSE o.C2Col END,
				C3=CASE WHEN o.C3Col IS NULL
				THEN C3 ELSE o.C3Col END
FROM tbSyncTarg t JOIN
(SELECT CT.PK,
    CASE WHEN CHANGE_TRACKING_IS_COLUMN_IN_MASK(
              @C2Id, CT.SYS_CHANGE_COLUMNS) = 1
         THEN C2 ELSE NULL END AS C2Col,
    CASE WHEN CHANGE_TRACKING_IS_COLUMN_IN_MASK(
              @C3Id, CT.SYS_CHANGE_COLUMNS) = 1
         THEN C3 ELSE NULL END AS C3Col
FROM tbTrack AS t INNER JOIN
     CHANGETABLE(CHANGES tbTrack, @LastSyncVer) AS CT
ON t.PK = CT.PK
WHERE CT.SYS_CHANGE_OPERATION = 'U') o
ON t.PK=o.PK
GO
--檢驗同步後的結果
SELECT * FROM tbSyncTarg
--紀錄最後更新的版本
DECLARE @LastSyncVer BIGINT=CHANGE_TRACKING_CURRENT_VERSION() 
INSERT tbSyncRec(LastSyncVersion) VALUES(@LastSyncVer)
==<end>==============