SQL Server 2008 - Change Data Capture - Part II

Article
07/07/2008

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

)

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,

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

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 !

CDC Modified.sql

SQL Server 2008 - Change Data Capture - Part II

Additional resources