#眉標=SQL Server 2008、CDC、IntelliSense
#副標=SQL Server 2008新功能概覽（6）
#大標=異動資料擷取與管理工具的新功能
#作者=文／圖	胡百敬


===<反灰>=============
CREATE DATABASE dbCDC
==<end>==============




===<反灰>=============
exec sp_cdc_enable_db
==<end>==============




===<反灰>=============
SELECT [NAME] AS 資料庫名稱,is_cdc_enabled FROM sys.databases
==<end>==============




===<反灰>=============
CREATE TABLE tbEmp(
PK INT IDENTITY(1,1) PRIMARY KEY,
empName NVARCHAR(50),
empEMail NVARCHAR(50))
==<end>==============




===<反灰>=============
exec sp_cdc_enable_table 'dbo','tbEmp',@role_name=null,@supports_net_changes=1
==<end>==============




===<反灰>=============
作業'cdc.dbCDC_capture'已成功啟動。
作業'cdc.dbCDC_cleanup'已成功啟動。
==<end>==============



===<反灰>=============
SELECT [NAME] AS 資料表名稱,is_tracked_by_cdc FROM sys.tables
==<end>==============



===<反灰>=============
INSERT tbEmp VALUES
('Adams','Adams@Company'),('Byron','Byron@Company'),('Sandy','Sandy@Company')

UPDATE tbEmp SET empName='Byron2’ WHERE PK=2
==<end>==============



===<反灰>=============
SELECT * FROM cdc.dbo_tbEmp_CT
==<end>==============



===<反灰>=============
--呈現異動資料擷取內所有的紀錄，所以從最小的交易流水序號(LSN Log Sequence Number)到最大的LSN
DECLARE @from_lsn BINARY(10),@to_lsn BINARY(10)
SET @from_lsn=sys.fn_cdc_get_min_lsn('dbo_tbEmp')
SET @to_lsn=sys.fn_cdc_get_max_lsn()
SELECT * FROM cdc.fn_cdc_get_all_changes_dbo_tbEmp(@from_lsn,@to_lsn,'all')
--net 運算要比較久
SELECT * FROM cdc.fn_cdc_get_net_changes_dbo_tbEmp(@from_lsn,@to_lsn,'all')
==<end>==============



===<反灰>=============
DECLARE @dtEnd DATETIME=GetDate()
DECLARE @toLsn BINARY(10)
--以最接近當下時間的一筆記錄當作最後的紀錄(low water mark)
--清掉之前的紀錄
SELECT @toLsn=sys.fn_cdc_map_time_to_lsn('largest less than or equal',@dtEnd)
exec sys.sp_cdc_cleanup_change_table @capture_instance='dbo_tbEmp',
@low_water_mark=@toLsn
==<end>==============



===<反灰>=============
else if (@relational_operator = N'largest less than or equal')
begin
	select @lsn = max(start_lsn)
	from [cdc].[lsn_time_mapping]
	where tran_end_time <= @tracking_time
end
==<end>==============



===<反灰>=============
EXEC sys.sp_cdc_disable_table @source_schema=N'dbo',
@source_name=N'tbEmp',@capture_instance=N'dbo_tbEmp'
==<end>==============



===<反灰>=============
EXEC sys.sp_cdc_disable_db
==<end>==============




===<反灰>=============
初始安裝(SQL Server 2008)→SQL Server安裝概觀→功能與工具概觀(SQL Server 2008)→SQL Server Management Studio簡介→SQL Server Management Studio環境→在SQL Server Management Studio中編輯指令碼和檔案→使用IntelliSense
==<end>==============