使用變更資料 (SQL Server)Work with Change Data (SQL Server)

本主題適用於:是SQL Server (從 2008 開始)否Azure SQL Database否Azure SQL 資料倉儲 否平行處理資料倉儲 THIS TOPIC APPLIES TO:yesSQL Server (starting with 2008)noAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

異動資料擷取取用者會透過資料表值函式 (TVF) 取得變更資料。Change data is made available to change data capture consumers through table-valued functions (TVFs). 這些函數的所有查詢都需要使用兩個參數來定義開發傳回的結果集時適合用於考量的記錄序號 (LSN) 範圍。All queries of these functions require two parameters to define the range of Log Sequence Numbers (LSNs) that are eligible for consideration when developing the returned result set. 限制間隔的上下 LSN 值會被視為包含在間隔內部。Both the upper and lower LSN values that bound the interval are considered to be included within the interval.

我們提供了許多函數,可協助您判斷查詢 TVF 時所使用的適當 LSN 值。Several functions are provided to help determine appropriate LSN values for use in querying a TVF. sys.fn_cdc_get_min_lsn 函數會傳回與擷取執行個體有效性間隔相關聯的最小 LSN。The function sys.fn_cdc_get_min_lsn returns the smallest LSN that is associated with a capture instance validity interval. 有效性間隔就是擷取執行個體目前可以使用變更資料的時間間隔。The validity interval is the time interval for which change data is currently available for its capture instances. sys.fn_cdc_get_max_lsn 函數會傳回有效性間隔中的最大 LSN。The function sys.fn_cdc_get_max_lsn returns the largest LSN in the validity interval. sys.fn_cdc_map_time_to_lsnsys.fn_cdc_map_lsn_to_time 函數可用來協助您將 LSN 值放置在傳統時間表上。The functions sys.fn_cdc_map_time_to_lsn and sys.fn_cdc_map_lsn_to_time are available to help place LSN values on a conventional timeline. 由於異動資料擷取會使用封閉的查詢間隔,因此有時候必須在序列中產生下一個 LSN 值,以便確保連續的查詢視窗中不會有重複的變更。Because change data capture uses closed query intervals, it is sometimes necessary to generate the next LSN value in a sequence to ensure that changes are not duplicated in consecutive query windows. 當您需要針對 LSN 值進行累加式調整時, sys.fn_cdc_increment_lsnsys.fn_cdc_decrement_lsn 函數就很有用。The functions sys.fn_cdc_increment_lsn and sys.fn_cdc_decrement_lsn are useful when an incremental adjustment to an LSN value is required.

驗證 LSN 界限Validating LSN Boundaries

我們建議您先驗證即將用於 TVF 查詢中的 LSN 界限,然後再加以使用。We recommend validating the LSN boundaries that are to be used in a TVF query before their use. Null 端點或位於擷取執行個體有效性間隔外部的端點將會強制異動資料擷取 TVF 傳回錯誤。Null endpoints or endpoints that lie outside the validity interval for a capture instance will force an error to be returned by a change data capture TVF.

例如,當用來定義查詢間隔的參數無效或超出範圍,或者資料列篩選選項無效時,系統就會針對所有變更的查詢傳回下列錯誤。For example, the following error is returned for a query for all changes when a parameter that is used to define the query interval is not valid, or is out of range, or the row filter option is invalid.

Msg 313, Level 16, State 3, Line 1

An insufficient number of arguments were supplied for the procedure or function cdc.fn_cdc_get_all_changes_ ...

針對 net changes 查詢傳回的對應錯誤如下所示:The corresponding error returned for a net changes query is the following:

Msg 313, Level 16, State 3, Line 1

An insufficient number of arguments were supplied for the procedure or function cdc.fn_cdc_get_net_changes_ ...

注意

「訊息 313」的訊息確實產生誤導而且並未傳達失敗的實際原因。It is recognized that the message for Msg 313 is misleading and does not convey the actual cause of the failure. 這種不當的使用方式起因為無法從 TVF 內部引發明確的錯誤。This awkward usage stems from the inability to raise an explicit error from within a TVF. 不過,我們認為傳回可辨識 (但不正確) 錯誤的價值會比單獨傳回空白結果的價值更高。Nevertheless, the value of returning a recognizable, if inaccurate, error was deemed preferable to simply returning an empty result. 空白的結果集與沒有傳回任何變更的有效查詢並無差別。An empty result set would not be distinguishable from a valid query returning no changes.

查詢所有變更時,授權失敗會傳回失敗,如下所示:Authorization failures will return failures when querying for all changes, as shown:

Msg 229, Level 14, State 5, Line 1

The SELECT permission was denied on the object 'fn_cdc_get_all_changes_...', database 'MyDB', schema 'cdc'.

查詢淨變更的情況也是如此:The same is true when querying for net changes:

Msg 229, Level 14, State 5, Line 1

The SELECT permission was denied on the object fn_cdc_get_net_changes_...', database 'MyDB', schema 'cdc'.

如需如何攔截這些已知 TVF 錯誤的示範,並且傳回有關失敗的更有意義資訊,請參閱「使用 TRY CATCH 來列舉淨變更」範本。See the template Enumerate Net Changes Using TRY CATCH for a demonstration of how to intercept these known TVF errors and return more meaningful information about the failure.

注意

若要在 SQL Server Management Studio 中找出異動資料擷取範本,請在 [檢視] 功能表上,按一下 [範本總管]、展開 [SQL Server 範本],然後展開 [異動資料擷取] 資料夾。To locate change data capture templates in SQL Server Management Studio, on the View menu, click Template Explorer, expand SQL Server Templates and then expand the Change Data Capture folder.

查詢函數Query Functions

系統會根據所追蹤之來源資料表的特性以及其擷取執行個體的設定方式,產生一個或兩個 TVF 以便查詢變更資料。Depending on the characteristics of the source table being tracked and the way in which its capture instance is configured, either one or two TVFs for querying change data are generated.

  • cdc.fn_cdc_get_all_changes_<capture_instance> 函數會傳回在指定的間隔中發生的所有變更。The function cdc.fn_cdc_get_all_changes_<capture_instance> returns all changes that occurred for the specified interval. 系統一定會產生這個函數。This function is always generated. 傳回的項目一律會經過排序 (先依據變更的交易認可 LSN,然後再依據變更在交易內部排列順序的值)。Entries are always returned sorted, first by the transaction commit LSN of the change, and then by a value that sequences the change within its transaction. 根據選擇的資料列篩選選項,系統會在更新時傳回最後一個資料列 (資料列篩選選項 "all") 或在更新時傳回全新和舊的值 (資料列篩選選項 "all update old")。Depending on the row filter option chosen, either the final row is returned on update (row filter option "all") or both the new and old values are returned on update (row filter option "all update old"').

  • 啟用來源資料表時,如果將參數 @supports_net_changes 設定為 1,則會產生函式 cdc.fn_cdc_get_net_changes_<擷取執行個體>The function cdc.fn_cdc_get_net_changes_<capture_instance> is generated when the parameter @supports_net_changes is set to 1 when the source table is enabled.

    注意

    只有當來源資料表具有已定義的主索引鍵,或者 @index_name 參數已經用來識別唯一的索引時,才支援這個選項。This option is only supported if the source table has a defined primary key or if the parameter @index_name has been used to identify a unique index.

    netchanges 函數會針對每個已修改的來源資料表資料列傳回一項變更。The netchanges function returns one change per modified source table row. 如果在指定的間隔期間記錄了資料列的多個變更,資料行值將會反映資料列的最終內容。If more than one change is logged for the row during the specified interval, the column values will reflect the final contents of the row. 為了正確識別更新目標環境所需的作業,TVF 必須同時考慮資料列在間隔期間的初始作業,以及資料列的最終作業。To correctly identify the operation that is necessary to update the target environment, the TVF must consider both the initial operation on the row during the interval and the final operation on the row. 指定資料列篩選選項 'all' 時, 淨變更 查詢所傳回的作業就是插入、刪除或更新 (新值)。When the row filter option 'all' is specified, the operations that are returned by a net changes query will either be insert, delete, or update (new values). 此選項永遠會將更新遮罩傳回為 Null,因為存在與計算彙總遮罩相關聯的成本。This option always returns the update mask as null because there is a cost associated with computing an aggregate mask. 如果您需要反映資料列之所有變更的彙總遮罩,請使用 'all with mask' 選項。If you require an aggregate mask that reflects all changes to a row, use the 'all with mask' option. 如果下游處理不需要區分插入和更新,請使用 'all with merge' 選項。If downstream processing does not require inserts and updates to be distinguished, use the 'all with merge' option. 在此情況下,作業值只會使用兩個值:1 用於刪除,而 5 用於可以是插入或更新的作業。In this case, the operation value will only take on two values: 1 for delete and 5 for an operation that could be either an insert or an update. 這個選項可以排除判斷衍生之作業是插入還是更新的額外處理需求,因此可以在不需要加以區分時,增進查詢的效能。This option eliminates the additional processing needed to determine whether the derived operation should be an insert or an update, and can improve the performance of the query when this differentiation is not necessary.

    從查詢函數傳回的更新遮罩是一種精簡型的表示法,它會識別在變更資料之資料列中變更的所有資料行。The update mask that is returned from a query function is a compact representation that identifies all columns that changed in a row of change data. 一般而言,只有擷取資料行的小型子集需要這個資訊。Typically, this information is only required for a small subset of the captured columns. 但是,如果使用這些函數,將有助於以應用程式更可直接應用的形式,從遮罩擷取資訊。Functions are available to assist in extracting information from the mask in a form that is more directly usable by applications. sys.fn_cdc_get_column_ordinal 函數會針對給定的擷取執行個體,傳回具名資料行的序數位置,而 sys.fn_cdc_is_bit_set 函數則會根據傳入函數呼叫中的序數,傳回提供之遮罩中的同位位元。The function sys.fn_cdc_get_column_ordinal returns the ordinal position of a named column for a given capture instance, whereas the function sys.fn_cdc_is_bit_set returns the parity of the bit in the provided mask based on the ordinal that was passed in the function call. 同時,這兩個函數可以針對變更資料的要求,有效率地擷取並傳回更新遮罩的資訊。Together, these two functions allow information from the update mask to be efficiently extracted and returned with the request for change data. 如需如何使用這些函數的示範,請參閱「使用 All With Mask 來列舉淨變更」範本。See the template Enumerate Net Changes Using All With Mask for a demonstration of how these functions are used.

查詢函數狀況Query Function Scenarios

下列各節將描述使用 cdc.fn_cdc_get_all_changes_<capture_instance> 和 cdc.fn_cdc_get_net_changes_<capture_instance> 查詢函數來查詢異動資料擷取資料的一般狀況。The following sections describe common scenarios for querying change data capture data by using the query functions cdc.fn_cdc_get_all_changes_<capture_instance> and cdc.fn_cdc_get_net_changes_<capture_instance>.

在擷取執行個體有效性間隔內查詢所有變更Querying for All Changes Within the Capture Instance Validity Interval

變更資料最直接的要求就是在擷取執行個體的有效性間隔中傳回所有目前的變更資料。The most straightforward request for change data is one that returns all of the current change data in a capture instance’s validity interval. 若要提出這項要求,請先判斷有效性間隔的 LSN 下限與上限。To make this request, first determine the lower and upper LSN boundaries of the validity interval. 然後,請使用這些值來識別傳遞給 cdc.fn_cdc_get_all_changes_<擷取執行個體> 或 cdc.fn_cdc_get_net_changes_<擷取執行個體> 查詢函式的 @from_lsn 和 @to_lsn 參數。Then, use these values to identify the parameters @from_lsn and @to_lsn passed to the query function cdc.fn_cdc_get_all_changes_<capture_instance> or cdc.fn_cdc_get_net_changes_<capture_instance>. 您可以使用 sys.fn_cdc_get_min_lsn 函數來取得下限,而使用 sys.fn_cdc_get_max_lsn 函數來取得上限。Use the function sys.fn_cdc_get_min_lsn to obtain the lower bound, and sys.fn_cdc_get_max_lsn to obtain the upper bound. 如需使用 cdc.fn_cdc_get_all_changes_<capture_instance> 查詢函數來查詢所有目前有效變更的範例程式碼,請參閱「列舉有效範圍的所有變更」範本。See the template Enumerate All Changes for the Valid Range for sample code to query for all current valid changes by using the query function cdc.fn_cdc_get_all_changes_<capture_instance>. 如需使用 cdc.fn_cdc_get_net_changes_<capture_instance> 函數的類似範例,請參閱「列舉有效範圍的淨變更」範本。See the template Enumerate Net Changes for the Valid Range for a similar example of using the function cdc.fn_cdc_get_net_changes_<capture_instance>.

查詢自從上一組變更以來的所有新變更Querying for All New Changes Since the Last Set of Changes

對於一般應用程式而言,查詢變更資料是持續進行的程序,並且針對自從上一個要求以來發生的所有變更提出定期要求。For typical applications, querying for change data will be an ongoing process, making periodic requests for all of the changes that occurred since the last request. 您可以針對這類查詢使用 sys.fn_cdc_increment_lsn 函數,以便從上一個查詢的上限衍生出目前查詢的下限。For such queries, you can use the function sys.fn_cdc_increment_lsn to derive the lower bound of the current query from the upper bound of the previous query. 這個方法可確保不會重複任何資料列,因為查詢間隔永遠會被視為封閉的間隔,其中兩個端點都包含在間隔中。This method ensures that no rows are repeated because the query interval is always treated as a closed interval where both end-points are included in the interval. 然後,您可以使用 sys.fn_cdc_get_max_lsn 函數來取得新要求間隔的高端點。Then, use the function sys.fn_cdc_get_max_lsn to obtain the high end-point for the new request interval. 如需有系統地移動查詢視窗來取得自從上一個要求以來之所有變更的範例程式碼,請參閱「列舉自從上一個要求以來的所有變更」範本。See the template Enumerate All Changes Since Previous Request for sample code to systematically move the query window to obtain all changes since the last request.

查詢至今為止的所有新變更Querying for all New Changes Up Until Now

針對查詢函數所傳回之變更放置的一般條件約束是僅包含上一個要求到目前日期和時間之間發生的變更。A typical constraint that is placed on the changes returned by a query function is to include only the changes that occurred between the previous request until the current date and time. 若為這種查詢,您可以將 sys.fn_cdc_increment_lsn 函式套用至上一個要求所使用的 @from_lsn 值,以便判斷下限。For this query, apply the function sys.fn_cdc_increment_lsn to the @from_lsn value that was used in the previous request to determine the lower bound. 由於時間間隔的上限會表示成特定時間點,所以它必須轉換成 LSN 值,然後才能讓查詢函數使用。Because the upper bound on the time interval is expressed as a specific point in time, it must be converted to an LSN value before it can be used by a query function. 您必須確定擷取處理序已經處理透過指定之上限所認可的所有變更,然後此日期時間值才能轉換成對應的 LSN 值。Before the datetime value can be converted to a corresponding LSN value, you must ensure that the capture process has processed all changes that were committed through the specified upper bound. 這是確保所有合格變更都已經傳播至變更資料表的必要作業。This is required to ensure that all the qualifying changes have been propagated to the change table. 進行此作業的其中一種方式為建構定期檢查的等候迴圈,以便查看針對任何資料庫變更資料表所記錄的目前最大認可 LSN 是否超過要求間隔的所需結束時間。One way to do this is to structure a wait loop that periodically checks to see if the current maximum commit lsn recorded for any database change table exceeds the desired end time of the request interval.

在延遲迴圈確認擷取處理序已經處理所有相關的記錄項目之後,請使用 sys.fn_cdc_map_time_to_lsn 函數來判斷表示成 LSN 值的新高端點。After the delay loop verifies that the capture process has already processed all the relevant log entries, use the function sys.fn_cdc_map_time_to_lsn to determine the new high end-point expressed as an LSN value. 若要確定已擷取透過指定之時間認可的所有項目,請呼叫 sys.fn_cdc_map_time_to_lsn 函數並使用 'largest less than or equal' 選項。To ensure that all entries that were committed through the specified time are retrieved, call the function sys.fn_cdc_map_time_to_lsn, and use the option 'largest less than or equal'.

注意

在沒有活動的期間,空的項目會加入至 cdc.lsn_time_mapping 資料表,以便表示擷取處理序已經處理這些變更 (直到給定的認可時間)。In periods of inactivity, a dummy entry is added to the table cdc.lsn_time_mapping to mark the fact that the capture process has processed the changes up to a given commit time. 這樣會避免在完全沒有任何最近的變更要處理時,顯示擷取處理序已經落後。This prevents it from appearing that the capture process has fallen behind when there are simply no recent changes to process.

「列舉至今為止的所有變更」範本將示範如何使用先前的策略來查詢變更資料。The template Enumerate All Changes Up Until Now demonstrates how to use the previous strategy to query for change data.

將認可時間加入至所有變更結果集Adding a Commit Time to an All Changes Result Set

cdc.lsn_time_mapping資料表提供在資料庫變更資料表中具有相關聯項目之每筆交易的認可時間。The commit time of each transaction with an associated entry in a database change table is available in the table cdc.lsn_time_mapping. 您可以透過聯結傳入所有變更之要求中的 $start_lsn 值與 cdc.lsn_time_mapping 資料表項目的 start_lsn 值,傳回 tran_end_time 以及變更資料,以便使用位於來源之交易的認可時間為變更加上戳記。By joining the $start_lsn value returned in a request for all changes with the start_lsn value of a cdc.lsn_time_mapping table entry, you can return the tran_end_time along with the change data to stamp the change with the commit time of the transaction at the source. 「將認可時間附加至所有變更結果集」範本將示範如何執行這項聯結。The template Append Commit Time to All Changes Result Set demonstrates how to perform this join.

聯結變更資料與來自相同交易的其他資料Joining Change Data with Other Data from the Same Transaction

有時候,在來源認可交易時,聯結變更資料與其他所蒐集的交易相關資訊會很有用。Occasionally, it is useful to join change data with other information gathered about the transaction when it committed at the source. cdc.lsn_time_mapping 資料表中的 tran_begin_lsn 資料行會提供執行這類聯結所需的資訊。The tran_begin_lsn column in the table cdc.lsn_time_mapping provides the information needed to perform such a join. 更新來源時,來自 sys.dm_tran_database_transactions 系統動態檢視的 database_transaction_begin_lsn 值必須與要和變更資料聯結的任何其他資訊一起儲存。When the update of the source occurs, the value for database_transaction_begin_lsn from the system dynamic view sys.dm_tran_database_transactions must be saved along with any other information to be joined with the change data. 您可以使用 fn_convertnumericlsntobinary 函數來比較 database_transaction_begin_lsn 與 tran_begin_lsn 值。Use the function fn_convertnumericlsntobinary to compare the database_transaction_begin_lsn and tran_begin_lsn values. 「建立函數 fn_convertnumericlsntobinary」範本會提供要建立此函數的程式碼。The code to create this function is available in the template Create Function fn_convertnumericlsntobinary. 「使用給定的 tran_begin_lsn 來傳回所有變更」範本將示範如何完成此聯結。The template Return All Changes with a Given tran_begin_lsn demonstrates how to effect the join.

使用日期時間包裝函數來查詢Querying Using Datetime Wrapper Functions

查詢變更資料的一般應用程式狀況是使用以日期時間值所限定的滑動視窗來定期要求變更資料。A typical application scenario for querying for change data is to periodically request change data by using a sliding window bounded by datetime values. 若為這種取用者類別,異動資料擷取會提供 sys.sp_cdc_generate_wrapper_function 預存程序,以便產生指令碼來建立異動資料擷取查詢函數的自訂包裝函數。For this class of consumers, change data capture provides the stored procedure sys.sp_cdc_generate_wrapper_function that generates scripts to create custom wrapper functions for the change data capture query functions. 這些自訂包裝函數可讓查詢間隔表示成日期時間組。These custom wrappers allow the query interval to be expressed as a datetime pair.

此預存程序的呼叫選項可讓您針對呼叫者可存取的所有擷取執行個體或只針對指定的擷取執行個體產生包裝函數。Calling options for the stored procedure allow for wrappers to be generated for all capture instances that the caller has access to, or only a specified capture instance. 支援的選項還包括能夠指定擷取間隔的高端點應該開啟或關閉、哪些可用的擷取資料行應該包含在結果集中,以及哪些包含資料行應該具有相關聯的更新旗標。Supported options also include the ability to specify whether the high end-point of the capture interval should be open or closed, which of the available captured columns should be included in the result set and which of the included columns should have associated update flags. 此程序會傳回含有兩個資料行的結果集:產生的函數名稱 (可從擷取執行個體名稱衍生出) 和包裝函數預存程序的建立陳述式。The procedure returns a result set with two columns: the generated function name, which is derivable from the capture instance name, and the create statement for the wrapper stored procedure. 系統一定會產生可包裝所有變更查詢的函數。The function to wrap the all changes query is always generated. 如果建立擷取執行個體時設定 @supports_net_changes 參數,也會產生可包裝淨變更函式的函式。If the @supports_net_changes parameter was set when the capture instance was created, the function to wrap the net changes function is also generated.

應用程式設計工具必須負責呼叫指令碼產生預存程序來產生包裝函數預存程序的建立陳述式,以及執行產生的建立指令碼來建立這些函數。It is the responsibility of the application designer to call the script generation stored procedure to generate the create statements for the wrapper stored procedures, and to execute the resulting create scripts to create the functions. 這項作業不會在建立擷取執行個體時自動進行。This does not occur automatically when a capture instance is created.

日期時間包裝函數是由使用者所擁有,並非建立在呼叫者的預設結構描述中。Datetime wrappers are owned by the user, and not are created in the default schema of the caller. 產生的函數不需要修改就可適用於大部分使用者。The generated function is suitable without modification for most users. 不過,建立此函數之前,您隨時都可以將進一步的自訂套用至產生的指令碼。However, further customization can always be applied to the generated script prior to creating the function.

包裝所有變更查詢之函數的名稱是 fn_all_changes_ 後面接著擷取執行個體名稱。The name of the function to wrap the all changes query is fn_all_changes_ followed by the capture instance name. 用於淨變更包裝函數的前置詞為 fn_net_changes_。The prefix that is used for the net changes wrapper is fn_net_changes_. 這兩個函數都會接受三個引數,就如同其相關聯的異動資料擷取 TVF 一樣。Both functions take three arguments, just as their associated change data capture TVFs do. 不過,這些包裝函數的查詢間隔是以兩個日期時間值 (而非兩個 LSN 值) 所限定。However, the query interval for the wrappers is bounded by two datetime values instead of than by two LSN values. 這兩組函式的 @row_filter_option 參數都相同。The @row_filter_option parameter for both sets of functions are the same.

產生的包裝函式支援下列有系統地查核異動資料擷取時間表的慣例:先前間隔的 @end_time 參數應該要當作後續間隔的 @start_time 參數使用。The generated wrapper functions support the following convention for systematically walking the change data capture timeline: It is expected that the @end_time parameter of the previous interval be used as the @start_time parameter of the subsequent interval. 此包裝函數會負責將日期時間值對應至 LSN 值,並且確保遵循此慣例時,不會遺漏或重複任何資料。The wrapper function takes care of mapping the datetime values to LSN values and ensuring that no data is lost or repeated if this convention is followed.

您可以產生包裝函數來支援指定之查詢視窗上的封閉上限或開放上限。The wrappers can be generated to support either a closed upper bound or an open upper bound on the specified query window. 也就是說,呼叫者可以指定具有認可時間的項目是否等於要包含在間隔內之擷取間隔的上限。That is, the caller can specify whether entries having a commit time equal to the upper bound of the extraction interval are to be included within the interval. 預設會包含上限。By default, the upper bound is included.

當產生的查詢 TVF 失敗時,如果針對 @from_lsn 值或 @to_lsn 值提供 Null 值,日期時間包裝函式就會使用 Null 來允許日期時間包裝函式傳回所有目前的變更。While the generated query TVFs fail if supplied a null value for either the @from_lsn value or the @to_lsn value, the datetime wrapper functions use null to allow the datetime wrappers to return all current changes. 也就是說,如果 Null 當做查詢視窗的低端點傳遞至日期時間包裝函數,擷取執行個體有效性間隔的低端點就會用於套用至查詢 TVF 的基礎 SELECT 陳述式中。That is, if null is passed as the low end-point of the query window to the datetime wrapper, the low end point of the capture instance validity interval is used in the underlying SELECT statement that is applied to the query TVF. 同樣地,如果 Null 當做查詢視窗的高端點傳遞,擷取執行個體有效性間隔的高端點就會在從查詢 TVF 中選取時使用。Similarly, if null is passed as the high end-point of the query window, the high end-point of the capture instance validity interval is used when selecting from the query TVF.

包裝函數所傳回的結果集包括所有要求的資料行,後面接著作業資料行,而此資料行會記錄成一或兩個字元,以便識別與資料列相關聯的作業。The result set returned by a wrapper function includes all the requested columns followed by an operation column, recoded as one or two characters to identify the operation that is associated with the row. 如果已經要求更新旗標,它們就會按照 @update_flag_list 參數中指定的順序,在作業碼之後顯示成位元資料行。If update flags have been requested, they appear as bit columns after the operation code, in the order specified in the @update_flag_list parameter. 如需自訂產生之日期時間包裝函式的呼叫選項資訊,請參閱 sys.sp_cdc_generate_wrapper_function (Transact-SQL)For information about the calling options for customizing the generated datetime wrappers, see sys.sp_cdc_generate_wrapper_function (Transact-SQL).

「使用更新旗標來具現化包裝函數 TVF」範本會示範如何自訂產生的包裝函數,以便將指定之資料行的更新旗標附加至淨變更查詢所傳回的結果集。The template Instantiate a Wrapper TVF With Update Flag shows how to customize a generated wrapper function to append an update flag for a specified column to the result set returned by a net changes query. 「具現化結構描述的 CDC 包裝函數 TVF」範本會示範如何針對所有擷取執行個體 (為給定之資料庫結構描述中的來源資料表所建立),具現化查詢 TVF 的日期時間包裝函數。The template Instantiate CDC Wrapper TVFs for a Schema shows how to instantiate the Datetime Wrappers for the Query TVFs for all of the capture instances created for the source tables in a given database schema.

如需使用日期時間包裝函數來查詢變更資料的範例,請參閱「使用包裝函數搭配更新旗標來取得淨變更」範本。For an example that uses a datetime wrapper to query for change data, see the template Get Net Changes Using Wrapper With Update Flags. 這個範本會示範如何在包裝函數設定成傳回更新旗標時,使用包裝函數來查詢淨變更。This template demonstrates how to query for net changes with a wrapper function when the wrapper is configured to return update flags. 請注意,若要讓基礎查詢函數在更新時傳回非 Null 更新遮罩,就必須使用資料列篩選選項 'all with mask'。Note that the row filter option 'all with mask' is required for the underlying query function to return a non-null update mask on update. Null 值會傳遞成日期時間間隔的下限和上限,以便通知函數在執行基礎 LSN 架構查詢時,使用擷取執行個體之有效性間隔的低端點和高端點。Null values are passed for both the lower and upper datetime interval boundaries to signal the function to use the low end point and the high end point of the validity interval for the capture instance when performing the underlying LSN based query. 此查詢會針對在擷取執行個體之有效範圍內發生的每個來源資料列修改,傳回一個資料列。The query returns one row for each modification to a source row that occurred within the valid range for the capture instance.

使用日期時間包裝函數在擷取執行體之間轉換Using the Datetime Wrapper Functions to Transition Between Capture Instances

對於單一追蹤來源資料表而言,異動資料擷取最多支援兩個擷取執行個體。Change data capture supports up to two capture instances for a single tracked source table. 這項功能的主要用途是在來源資料表的資料定義語言 (DDL) 變更擴充可用於追蹤的資料行集合時,容納多個擷取執行個體之間的轉換。The principal use of this capability is to accommodate a transition between multiple capture instances when data definition language (DDL) changes to the source table expand the set of available columns for tracking. 轉換成新的擷取執行個體時,其中一種避免較高應用程式層級變更基礎查詢函數名稱的方式是使用包裝函數來包裝基礎呼叫。When transitioning to a new capture instance, one way to protect higher application levels from changes in the names of the underlying query functions is to use a wrapper function to wrap the underlying call. 然後,請確定包裝函數的名稱維持不變。Then, ensure that the name of the wrapper function remains the same. 進行切換時,可能會卸除舊的包裝函數,而且建立具有相同名稱的新包裝函數,並且參考新的查詢函數。When the switch is to occur, the old wrapper function can be dropped, and a new one with the same name created that references the new query functions. 您可以透過先將產生的指令碼修改成建立相同名稱的包裝函數,切換至新的擷取執行個體,而不影響較高的應用程式層。By first modifying the generated script to create a wrapper function of the same name, you can make the switch to a new capture instance without affecting higher application layers.

另請參閱See Also

追蹤資料變更 (SQL Server) Track Data Changes (SQL Server)
關於異動資料擷取 (SQL Server) About Change Data Capture (SQL Server)
啟用和停用異動資料擷取 (SQL Server) Enable and Disable Change Data Capture (SQL Server)
管理和監視異動資料擷取 (SQL Server)Administer and Monitor Change Data Capture (SQL Server)