sys.sp_cdc_enable_table （Transact-SQL）

  • 發行項

適用於：SQL Server

啟用目前資料庫中指定源數據表的異動數據擷取。 啟用異動數據擷取的數據表時，會將套用至數據表的每個數據操作語言 （DML） 作業記錄寫入事務歷史記錄中。 異動數據擷取程式會從記錄檔擷取這項資訊，並將其寫入至使用一組函式存取的數據表。

變更數據擷取不適用於每個 SQL Server 版本。 如需 SQL Server 版本支援的功能清單，請參閱 SQL Server 2022 的版本和支援功能。

Transact-SQL 語法慣例

語法

sys.sp_cdc_enable_table
    [ @source_schema = ] 'source_schema'
      , [ @source_name = ] 'source_name'
    [ , [ @capture_instance = ] 'capture_instance' ]
    [ , [ @supports_net_changes = ] supports_net_changes ]
      , [ @role_name = ] 'role_name'
    [ , [ @index_name = ] 'index_name' ]
    [ , [ @captured_column_list = ] N'captured_column_list' ]
    [ , [ @filegroup_name = ] 'filegroup_name' ]
    [ , [ @allow_partition_switch = ] 'allow_partition_switch' ]
[ ; ]

引數

[ @source_schema = ] 'source_schema'

源數據表所屬之架構的名稱。 @source_schema是 sysname，沒有預設值，而且不能是 NULL。

[ @source_name = ] 'source_name'

要在其中啟用異動數據擷取之源數據表的名稱。 @source_name是 sysname，沒有預設值，而且不能是 NULL。

source_name必須存在於目前的資料庫中。 無法針對異動數據擷取啟用架構中的 cdc 數據表。

[ @role_name = ] 'role_name'

用來閘道存取變更數據的資料庫角色名稱。 @role_name為 sysname，必須指定。 如果明確設定為 NULL，則不會使用任何設定角色來限制變更數據的存取權。

如果角色目前存在，則會使用它。 如果角色不存在，就會嘗試建立具有指定名稱的資料庫角色。 在嘗試建立角色之前，角色名稱會先修剪字元串右邊的空格符。 如果呼叫端未獲授權在資料庫內建立角色，預存程式作業就會失敗。

[ @capture_instance = ] 'capture_instance'

用來命名實例特定異動數據擷取物件之擷取實例的名稱。 @capture_instance為 sysname 且不能為 NULL。

如果沒有指定，就會採用 <schemaname>_<sourcename>格式，從來源結構描述名稱加上來源資料表名稱衍生此名稱。 @capture_instance不能超過 100 個字元，而且在資料庫中必須是唯一的。 不論指定還是衍生， @capture_instance 會修剪字元串右邊的任何空格符。

源數據表最多可以有兩個擷取實例。 如需詳細資訊， 請參閱 sys.sp_cdc_help_change_data_capture （Transact-SQL） 。

[ @supports_net_changes = ] supports_net_changes

指出是否要為此擷取實例啟用查詢凈變更的支援。 如果數據表具有主鍵，或數據表具有唯一索引，則@supports_net_changes是具有預設值1的位，而該索引是使用 @index_name 參數來識別。 否則，參數預設為 0。

  • 如果 0為，則只會產生查詢所有變更的支援函式。
  • 如果 1為 ，也會產生查詢凈變更所需的函式。

如果 @supports_net_changes 設定為 1， 則必須指定@index_name ，否則源數據表必須具有定義的主鍵。

當@supports_net_changes設定為 1時，會在變更數據表上建立額外的非叢集索引，並建立 net changes 查詢函式。 由於需要維護此索引，因此啟用凈變更可能會對 CDC 效能產生負面影響。

[ @index_name = ] 'index_name'

用來唯一識別源數據表中數據列的唯一索引名稱。 @index_name是 sysname，而且可以是 NULL。 如果指定， @index_name 必須是源數據表的有效唯一索引。 如果 指定了@index_name ，識別的索引數據行會優先於任何已定義的主鍵數據行作為數據表的唯一數據列標識符。

[ @captured_column_list = ] N'captured_column_list'

識別要包含在變更數據表中的源數據表數據行。 @captured_column_list為 nvarchar（max），而且可以是 NULL。 如果為 NULL，則所有數據行都會包含在變更數據表中。

數據行名稱必須是源數據表中的有效數據行。 必須在主鍵索引中定義數據行，或包含在@index_name所參考之索引中的數據行。

@captured_column_list是以逗號分隔的數據行名稱清單。 清單內的個別資料行名稱可以使用雙引號 （""） 或方括弧 （） 選擇性地加上引號。[] 如果數據行名稱包含內嵌的逗號，則必須加上數據行名稱的引號。

@captured_column_list不能包含下列保留的資料列名稱：__$start_lsn、、__$end_lsn__$seqval、 __$operation和 __$update_mask。

[ @filegroup_name = ] 'filegroup_name'

要用於針對擷取實例建立之變更數據表的檔案群組。 @filegroup_name為 sysname 且可以是 NULL。 如果指定， 則必須為目前資料庫定義@filegroup_name 。 如果為 NULL，則會使用預設檔案群組。

建議您為異動數據擷取異動數據表建立個別的檔案群組。

[ @allow_partition_switch = ] 'allow_partition_switch'

指出 ALTER TABLE 的 SWITCH PARTITION 命令是否可以針對啟用異動數據擷取的數據表執行。 @allow_partition_switch為 bit，預設值為 1。

對於非分割數據表，參數設定一律為 1，而且會忽略實際設定。 如果針對非分割數據表明確將 參數設定為 0 ，則會發出警告 22857，表示已忽略交換器設定。 如果分割數據表的參數明確設定為 0 ，則會發出警告 22356，表示不允許源數據表上的分割區切換作業。 最後，如果參數設定明確設定為 或允許預設1為 1 ，且已啟用的數據表已分割，則會發出警告 22855，表示不會封鎖數據分割交換器。 如果發生任何數據分割切換，異動數據擷取不會追蹤參數所產生的變更。 當取用變更數據時，這會導致數據不一致。

SWITCH PARTITION 是元數據作業，但會導致數據變更。 與這項作業相關聯的數據變更不會在異動數據擷取變更數據表中擷取。 請考慮具有三個數據分割的數據表，並且對此數據表進行變更。 擷取程式會追蹤針對資料表執行的使用者插入、更新和刪除作業。 不過，如果分割區切換至另一個數據表（例如，若要執行大量刪除），則此作業中移動的數據列不會擷取為變更數據表中已刪除的數據列。 同樣地，如果已預先填入數據列的新分割區已加入數據表中，這些數據列就不會反映在變更數據表中。 當應用程式取用變更並套用至目的地時，這可能會導致數據不一致。

如果您在 SQL Server 上啟用資料分割切換，則近期可能需要分割和合併作業。 在複寫或已啟用 CDC 的數據表上執行分割或合併作業之前，請確定有問題的分割區沒有任何暫止的複寫命令。 此外也必須確定未在分割及合併作業期間，對分割區執行任何 DML 作業。 如果有記錄讀取器或 CDC 擷取作業尚未處理的交易，或者當分割或合併作業執行時，DML 作業是在複寫或 CDC 啟用數據表的分割區上執行（涉及相同的分割區），則可能會導致處理錯誤（錯誤 608 - 找不到數據分割標識符的目錄專案），以及記錄讀取器代理程式或 CDC 擷取作業。 若要更正此錯誤，可能需要將訂用帳戶重新初始化或停用該資料表或資料庫上的 CDC。

傳回碼值

0 （成功）或 1 （失敗）。

結果集

無。

備註

您必須先啟用資料庫，才能啟用數據表以進行異動數據擷取。 若要判斷資料庫是否已啟用異動數據擷取，請查詢 is_cdc_enabled sys.databases 目錄檢視中的數據行。 若要啟用資料庫，請使用 sys.sp_cdc_enable_db 預存程式。

針對數據表啟用異動數據擷取時，會產生變更數據表和一或兩個查詢函式。 變更數據表可作為擷取程式從事務歷史記錄擷取之源數據表變更的存放庫。 查詢函式可用來從變更數據表擷取數據。 這些函式的名稱會以下列方式衍生自 @capture_instance 參數：

  • 所有變更函式： cdc.fn_cdc_get_all_changes_<capture_instance>
  • Net changes 函式： cdc.fn_cdc_get_net_changes_<capture_instance>

sys.sp_cdc_enable_table 如果源數據表是要針對異動數據擷取啟用之資料庫中的第一個數據表，而且資料庫沒有交易式發行集，也會建立資料庫的擷取和清除作業。 它會將 is_tracked_by_cdc sys.tables 目錄檢視中的資料行設定為 1。

SQL Server Agent 不需要在資料表啟用 CDC 時執行。 不過，除非執行 SQL Server Agent，否則擷取進程不會處理事務歷史記錄檔，也不會將專案寫入變更數據表。

權限

需要 db_owner 固定資料庫角色中的成員資格。

範例

A. 只指定必要的參數來啟用異動數據擷取

下列範例會啟用數據表的 HumanResources.Employee 異動數據擷取。 只指定必要的參數。

USE AdventureWorks2022;
GO

EXECUTE sys.sp_cdc_enable_table
    @source_schema = N'HumanResources',
    @source_name = N'Employee',
    @role_name = N'cdc_Admin';
GO

B. 藉由指定其他選擇性參數來啟用異動數據擷取

下列範例會啟用數據表的 HumanResources.Department 異動數據擷取。 除了@allow_partition_switch之外，所有參數都會指定。

USE AdventureWorks2022;
GO

EXEC sys.sp_cdc_enable_table
    @source_schema = N'HumanResources',
    @source_name = N'Department',
    @role_name = N'cdc_admin',
    @capture_instance = N'HR_Department',
    @supports_net_changes = 1,
    @index_name = N'AK_Department_Name',
    @captured_column_list = N'DepartmentID, Name, GroupName',
    @filegroup_name = N'PRIMARY';
GO

相關內容