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 資料倉儲 否平行處理資料倉儲 APPLIES TO: yesSQL Server (starting with 2008) noAzure SQL Database noAzure 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_ < 擷取執行個體 >cdc.fn_cdc_get_net_changes_ < 擷取執行個體 >傳回該範圍內的資料變更。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 >' {較不超過最大 | 比大小於或等於 | 最小大於 | 最小 greater than 或 equal}'<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 (-SQL&#41;) 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)