sys.fn_cdc_map_time_to_lsn (Transact-SQL)sys.fn_cdc_map_time_to_lsn (Transact-SQL)

本主題適用於:是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

傳回記錄序號 (lsn) 值,從start_lsn中的資料行cdc.lsn_time_mapping系統資料表。 指定的時間。Returns the log sequence number (LSN) value from the start_lsn column in the cdc.lsn_time_mapping system table for the specified time. 您可以使用此函式,有系統地將日期時間範圍對應至異動資料擷取列舉函數所需的 LSN 架構範圍cdc.fn_cdc_get_all_changes_ < capture_instance >cdc.fn_cdc_get_net_changes_ < capture_instance >傳回該範圍內的資料變更。You can use this function to systematically map datetime ranges into the LSN-based range needed by the change data capture enumeration functions cdc.fn_cdc_get_all_changes_<capture_instance> and cdc.fn_cdc_get_net_changes_<capture_instance> to return data changes within that range.

主題連結圖示 Transact-SQL 語法慣例Topic link icon Transact-SQL Syntax Conventions

語法Syntax


sys.fn_cdc_map_time_to_lsn ( '<relational_operator>', tracking_time )  

<relational_operator> ::=  
{  largest less than  
 | largest less than or equal  
 | smallest greater than  
 | smallest greater than or equal  
}  

引數Arguments

'< relational_operator >' {小於比最大 | 較不超過最大或等於 | 最小大於 | 最小大於或等於}'<relational_operator>' { largest less than | largest less than or equal | smallest greater than | smallest greater than or equal }
用來識別中的不同 LSN 值內cdc.lsn_time_mapping資料表相關之tran_end_time ,可滿足此關聯性,相較於tracking_time值。Is used to identify a distinct LSN value in within the cdc.lsn_time_mapping table with an associated tran_end_time that satisfies the relation when compared to the tracking_time value.

relational_operatornvarchar (30)relational_operator is nvarchar(30).

tracking_timetracking_time
這是要比對的日期時間值。Is the datetime value to match against. tracking_timedatetimetracking_time is datetime.

傳回類型Return Type

binary(10)binary(10)

備註Remarks

若要了解如何sys.fn_cdc_map_time_lsn可用來將日期時間範圍對應至 LSN 範圍,請考慮下列案例。To understand how the sys.fn_cdc_map_time_lsn can be used to map datetime ranges to LSN ranges, consider the following scenario. 假設某位取用者想要每天擷取變更資料。Assume that a consumer wants to extract change data on a daily basis. 也就是說,該取用者只需要指定當天的變更 (直到且包括午夜)。That is, the consumer wants only changes for a given day up to and including midnight. 此時間範圍的下限應該是直到但不包括前一天的午夜。The lower bound of the time range would be up to but not including midnight of the previous day. 其上限應該是直到且包括指定當天的午夜。The upper bound would be up to and including midnight of the given day. 下列範例會示範如何函式sys.fn_cdc_map_time_to_lsn可用於有系統地將這個時間架構範圍對應至異動資料擷取列舉函數傳回所有所需的 LSN 架構範圍該範圍內的變更。The following example shows how the function sys.fn_cdc_map_time_to_lsn can be used to systematically map this time-based range into the LSN-based range needed by the change data capture enumeration functions to return all changes within that range.

DECLARE @begin_time datetime, @end_time datetime, @begin_lsn binary(10), @end_lsn binary(10);

SET @begin_time = '2007-01-01 12:00:00.000';

SET @end_time = '2007-01-02 12:00:00.000';

SELECT @begin_lsn = sys.fn_cdc_map_time_to_lsn('smallest greater than', @begin_time);

SELECT @end_lsn = sys.fn_cdc_map_time_to_lsn('largest less than or equal', @end_time);

SELECT * FROM cdc.fn_cdc_get_net_changes_HR_Department(@begin_lsn, @end_lsn, 'all ');SELECT * FROM cdc.fn_cdc_get_net_changes_HR_Department(@begin_lsn, @end_lsn, 'all ');

關係運算子 'smallest greater than' 是用來限制前一天午夜之後所發生的變更。The relational operator 'smallest greater than' is used to restrict changes to those that occurred after midnight on the previous day. 如果多個項目含有不同 LSN 值的共用tran_end_time識別為下限值cdc.lsn_time_mapping資料表,此函數會傳回的最小 LSN,如此可確保所有項目都包含在內。If multiple entries with different LSN values share the tran_end_time value identified as the lower bound in the cdc.lsn_time_mapping table, the function will return the smallest LSN ensuring that all entries are included. 為上限,關係運算子 'largest less than or equal to' 用來確保此範圍包括所有項目,包括以午夜做為一天其tran_end_time值。For the upper bound, the relational operator ‘largest less than or equal to’ is used to ensure that the range includes all entries for the day including those than have midnight as their tran_end_time value. 如果多個項目含有不同 LSN 值的共用tran_end_time值識別為上限,此函式會傳回確保所有項目都包括在內的最大 LSN。If multiple entries with different LSN values share the tran_end_time value identified as the upper bound, the function will return the largest LSN ensuring that all entries are included.

PermissionsPermissions

需要 public 角色中的成員資格。Requires membership in the public role.

範例Examples

下列範例會使用sys.fn_cdc_map_time_lsn函式來判斷是否有任何資料列中的cdc.lsn_time_mapping資料表具有tran_end_time大於或等於午夜的值。The following example uses the sys.fn_cdc_map_time_lsn function to determine whether there are any rows in the cdc.lsn_time_mapping table with a tran_end_time value that is greater than or equal to midnight. 例如,這個查詢可用來判斷擷取處理序是否已經處理了直到前一天午夜所認可的變更,如此當天的變更資料擷取才能繼續進行。This query can be used to determine, for example, whether the capture process has already processed the changes committed through midnight of the previous day, so that the extraction of change data for that day can proceed.

DECLARE @extraction_time datetime, @lsn binary(10);  
SET @extraction_time = '2007-01-01 12:00:00.000';  
SELECT @lsn = sys.fn_cdc_map_time_to_lsn ('smallest greater than or equal', @extraction_time);  
IF @lsn IS NOT NULL  
BEGIN  
<some action>  
END  

另請參閱See Also

cdc.lsn_time_mapping (Transact SQL) cdc.lsn_time_mapping (Transact-SQL)
sys.fn_cdc_map_lsn_to_time (Transact-SQL) sys.fn_cdc_map_lsn_to_time (Transact-SQL)
cdc.fn_cdc_get_net_changes_<capture_instance> (Transact-SQL) cdc.fn_cdc_get_net_changes_<capture_instance> (Transact-SQL)
cdc.fn_cdc_get_all_changes_<capture_instance> (Transact-SQL)cdc.fn_cdc_get_all_changes_<capture_instance> (Transact-SQL)