Share via


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.

  1. SQL Server 2008 RC0 Download
  2. SQL Server 2008 Feature Pack

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