SQL Server 2008 - Change Data Capture - Part II
Hi ! It's been a long time since I last updated my blog. To continue with the Change Data Capture feature of SQL Server 2008, this is new post that helps you to know how you can write your own stored procedures / functions to get the required data in the fashion you want, using the CDC functions provided by Microsoft.
NOTE: If you haven't read the article "SQL Server 2008 - Change Data Capture - Part I" then I would recommend you to please visit the article first else you won't be able to connect well, what I am trying to express here.
The following function works in the similar fashion like cdc.fn_cdc_get_all_changes_InstanceHumanResourcesEmployee but this function will take the starting time and ending time i.e. the time range and the row filter option. The row filter option will either be 'all' or 'all update old' .
cdc.fn_cdc_get_all_changes_InstanceHumanResourcesEmployee always matches the LSN number provided by you with the LSN present in the CDC table and if it is not found it will throw an error. The reason is it will check for the exact LSN Number so that duplicate records don't creep into the result set. The following function is more generic and will tell you about the changes that happened to a table within a time range. The logic is created in a fashion that if there are no records existing in the table matching the criteria specified by you then it will return an empty set.
This is just an example to demonstrate that how we could write our own SPs and Functions to get the data from the Change Tables.
/*
This function will return all the changes happened to the table
within a specific time period.
It will accept any valid date and time range and accepts row filters
-> all
-> all update old
*/
create function [cdc].[fn_cdc_get_changes_within_time_period]
( @from_time datetime2(7), @to_time datetime2(7), @row_filter_option nvarchar(30) ) RETURNS @return_table TABLE
(
[__$start_lsn] [binary](10) NOT NULL, [__$seqval] [binary](10) NOT NULL, [__$operation] [int] NOT NULL, [__$update_mask] [varbinary](128) NULL, [EmployeeID] [int] NULL, [NationalIDNumber] [nvarchar](15) NULL, [ContactID] [int] NULL, [LoginID] [nvarchar](256) NULL, [ManagerID] [int] NULL, [Title] [nvarchar](50) NULL, [BirthDate] [datetime] NULL, [MaritalStatus] [nchar](1) NULL, [Gender] [nchar](1) NULL, [HireDate] [datetime] NULL, [SalariedFlag] [bit] NULL, [VacationHours] [smallint] NULL, [SickLeaveHours] [smallint] NULL, [CurrentFlag] [bit] NULL, [rowguid] [uniqueidentifier] NULL, [ModifiedDate] [datetime] NULL )
AS
BEGIN
DECLARE @from_lsn binary(10), @to_lsn binary(10);
SELECT @from_lsn = MIN([__$start_lsn])
FROM cdc.InstanceHumanResourcesEmployee_CT
WHERE sys.fn_cdc_map_lsn_to_time ([__$start_lsn]) >= @from_time AND
sys.fn_cdc_map_lsn_to_time ([__$start_lsn]) <= @to_time ;
SELECT @to_lsn = MAX([__$start_lsn])
FROM cdc.InstanceHumanResourcesEmployee_CT
WHERE sys.fn_cdc_map_lsn_to_time ([__$start_lsn]) <= @to_time AND
sys.fn_cdc_map_lsn_to_time ([__$start_lsn]) >= @from_time ;
IF ISNULL(@from_lsn,0) <> 0 AND ISNULL(@to_lsn,0) <> 0 AND @from_time <= @to_time
BEGIN
INSERT INTO @return_table
select
NULL as __$start_lsn, NULL as __$seqval, NULL as __$operation, NULL as __$update_mask, NULL as [EmployeeID],
NULL as [NationalIDNumber],
NULL as [ContactID],
NULL as [LoginID],
NULL as [ManagerID],
NULL as [Title],
NULL as [BirthDate],
NULL as [MaritalStatus],
NULL as [Gender],
NULL as [HireDate],
NULL as [SalariedFlag],
NULL as [VacationHours],
NULL as [SickLeaveHours],
NULL as [CurrentFlag],
NULL as [rowguid],
NULL as [ModifiedDate]
where ( [sys].[fn_cdc_check_parameters]
( N'InstanceHumanResourcesEmployee', @from_lsn, @to_lsn, lower(rtrim(ltrim(@row_filter_option))), 0) = 0) union all
select t.__$start_lsn as __$start_lsn, t.__$seqval as __$seqval, t.__$operation as __$operation, t.__$update_mask as __$update_mask, t.[EmployeeID], t.[NationalIDNumber], t.[ContactID], t.[LoginID], t.[ManagerID], t.[Title], t.[BirthDate], t.[MaritalStatus], t.[Gender], t.[HireDate], t.[SalariedFlag], t.[VacationHours], t.[SickLeaveHours], t.[CurrentFlag], t.[rowguid], t.[ModifiedDate]
from [cdc].[InstanceHumanResourcesEmployee_CT] t
where (lower(rtrim(ltrim(@row_filter_option))) = 'all') and ( [sys].[fn_cdc_check_parameters]( N'InstanceHumanResourcesEmployee', @from_lsn, @to_lsn, lower(rtrim(ltrim(@row_filter_option))), 0) = 1) and (t.__$operation = 1 or t.__$operation = 2 or t.__$operation = 4) and (t.__$start_lsn <= @to_lsn) and (t.__$start_lsn >= @from_lsn)
union all
select t.__$start_lsn as __$start_lsn, t.__$seqval as __$seqval, t.__$operation as __$operation, t.__$update_mask as __$update_mask, t.[EmployeeID], t.[NationalIDNumber], t.[ContactID], t.[LoginID], t.[ManagerID], t.[Title], t.[BirthDate], t.[MaritalStatus], t.[Gender], t.[HireDate], t.[SalariedFlag], t.[VacationHours], t.[SickLeaveHours], t.[CurrentFlag], t.[rowguid], t.[ModifiedDate]
from [cdc].[InstanceHumanResourcesEmployee_CT] t
where (lower(rtrim(ltrim(@row_filter_option))) = 'all update old') and ( [sys].[fn_cdc_check_parameters]( N'InstanceHumanResourcesEmployee', @from_lsn, @to_lsn, lower(rtrim(ltrim(@row_filter_option))), 0) = 1) and (t.__$operation = 1 or t.__$operation = 2 or t.__$operation = 4 or t.__$operation = 3 ) and (t.__$start_lsn <= @to_lsn) and (t.__$start_lsn >= @from_lsn) END
RETURN END
GO
|
I hope people are enjoying to work with SQL Server 2008, just in case if you have missed these following links, please visit them today.
Keep writing to me & keep a watch on my blog. I will bring new articles, on new features of SQL Server 2008 very soon. Till then ... Bye !