CDC changes column-by-column instead of row-by-row, can you improve it???

I’m writing a table value function that returns a cdc recordset that, instead of returning changed data row-by-row, I need to output them column-by-column.

Say for example I have a table called auth.Account with cdc enabled on fields Email and Password. e.g.

EXEC sys.sp_cdc_enable_table @source_schema = N’auth’,
@source_name = N’Account’, @captured_column_list = N’
Email,
Password
@supports_net_changes = 1;

Right, now cdc is enabled, i need my recordset to look like this:-

ID, CDC_OPERATION, LSN, CurrentEmail, PreviousEmail, CurrentPassword, PreviousPassword

This is my solution so far, yep I know its a bit messy and could be improved, any ideas on how I could improve this? Thanks AJF

– =========================================
– Author: Andrew Fenna
– Create date: 2013-08-15
– Description: Get Changed Auth Details TVF
– =========================================
ALTER FUNCTION [auth].[ChangedAuthDetails] ( @FromLsn BINARY(10) )
RETURNS @User TABLE
(
UserID INT ,
CDC_OPERATION CHAR(1) ,
LSN BINARY(10) ,
CurrentEmail NVARCHAR(255) ,
PreviousEmail NVARCHAR(255) ,
CurrentPassword NVARCHAR(32) ,
PreviousPassword NVARCHAR(32)
)
AS
BEGIN

DECLARE @ToLsn BINARY(10)

IF ( @FromLsn IS NULL )
SELECT @FromLsn = sys.fn_cdc_get_min_lsn(N’auth_account’)
ELSE
SELECT @FromLsn = sys.fn_cdc_increment_lsn(@FromLsn)

SELECT @ToLsn = sys.fn_cdc_get_max_lsn()

IF ( @FromLsn = sys.fn_cdc_increment_lsn(@ToLsn) )
RETURN

– Query for change data
INSERT INTO @User
SELECT a.UserID ,
‘U’ AS [__$operation] ,
a.[__$start_lsn] ,
A.CurrentEmail ,
a.PreviousEmail ,
b.CurrentPassword ,
b.PreviousPassword
FROM ( SELECT Email.[__$start_lsn] ,
Email.ID AS [UserID] ,
Email.[3] AS [PreviousEmail] ,
Email.[4] AS [CurrentEmail]
FROM ( SELECT [__$start_lsn] ,
ID ,
Email ,
[__$operation]
FROM cdc.fn_cdc_get_all_changes_auth_account(@FromLsn,
@ToLsn, ‘all update old’)
WHERE [__$operation] IN ( 3, 4 )
) P PIVOT
( MAX(Email) FOR [__$operation] IN ( [3], [4] ) ) AS Email
) A
CROSS APPLY ( SELECT [password].[__$start_lsn] ,
[Password].[3] AS [PreviousPassword] ,
[Password].[4] AS [CurrentPassword]
FROM ( SELECT [__$start_lsn] ,
ID ,
[Password] ,
[__$operation]
FROM cdc.fn_cdc_get_all_changes_auth_account(@FromLsn,
@ToLsn, ‘all update old’)
WHERE [__$operation] IN ( 3, 4 )
) P PIVOT
( MAX([Password]) FOR [__$operation] IN ( [3], [4] ) ) AS [Password]
) B
WHERE A.__$start_lsn = B.__$start_lsn
UNION
SELECT [ID] AS [UserID] ,
CASE __$operation
WHEN 1 THEN ‘D’
WHEN 2 THEN ‘I’
ELSE NULL
END AS CDC_OPERATION,
[__$start_lsn] ,
CASE [__$operation]
WHEN 1 THEN NULL
WHEN 2 THEN Email
END AS [CurrentEmail] ,
CASE [__$operation]
WHEN 1 THEN Email
WHEN 2 THEN NULL
END AS [PreviousEmail] ,
CASE [__$operation]
WHEN 1 THEN NULL
WHEN 2 THEN [Password]
END AS [CurrentPassword] ,
CASE [__$operation]
WHEN 1 THEN [Password]
WHEN 2 THEN NULL
END AS [PreviousPassword]
FROM cdc.fn_cdc_get_all_changes_auth_account(@FromLsn,
@ToLsn, ‘all update old’)
WHERE [__$operation] < 3
RETURN
END

About these ads

2 thoughts on “CDC changes column-by-column instead of row-by-row, can you improve it???

  1. This is just to give an idea. I would certainly stay away from a non inline table valued function (any function that has begin and end I would not use it for this purpose unless the data is small). You can aggregate the data on the clustered index (usually something like CREATE UNIQUE CLUSTERED INDEX [dbo_CDCTable_CT_clustered_idx] ON [cdc].[dbo_CDCTable_CT] ([__$start_lsn], [__$seqval], [__$operation]) for cdc tables, first key is [__$start_lsn]).
    I may have mistaken the operation id’s in the sample, please double check them.

    CREATE TABLE Users (
    [__$start_lsn] BINARY(8)
    , [__$operation] INT
    , UserID INT
    , Email NVARCHAR(255)
    , [Password] NVARCHAR(32)
    )

    SELECT [__$start_lsn]
    , UserID
    , MAX(CASE WHEN [__$operation] IN (1, 3) THEN Email
    END) AS PreviousEmail
    , MAX(CASE WHEN [__$operation] IN (2, 4) THEN Email
    END) AS CurrentEmail
    , MAX(CASE WHEN [__$operation] IN (1, 3) THEN Password
    END) AS PreviousPassword
    , MAX(CASE WHEN [__$operation] IN (2, 4) THEN Password
    END) AS CurrentPassword
    FROM Users
    GROUP BY [__$start_lsn]
    , UserID

  2. Hi Gokhan,

    Thank-you for your reply! and sorry for my late reply, I’ve been working on a time consuming project, which i will blog at some point!

    This is exactly what I was looking for and suites the solution very well. :)

    Your a genius!

    Ta.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s