Sql-server – Conditionally track CDC on a table in SQL Server 2016

change-data-capturesql serversql-server-2016

I have CDC working on many tables in SQL Server 2016 without any issues. I now have a requirement to capture data changes (CDC) on a table when a specific criteria is met. Is it possible to capture data using a filter criteria? In other words, track data changes into (_CT) table using a specific "where clause" condition (for example, Where Status = 'Active' and Flag = 1 etc.). How do I go about it? Any help would be appreciated.

Best Answer

Unfortuantely, what you want to do is currently not possible. Documentation here.

However what you can do is on the CDC table put a view that will apply your filter post-capture. You will loose the extra space, but you will get a view of only the data you want filtered. Recommendation would be to run your queries on the CDC table with filters in the WHERE clause though rather than bothering with an extra view.

Hope this helps bring ideas.

Related Solutions

Sql-server – When to use CDC to track history

Firstly,

Change data capture is available only on the Enterprise, Developer, and Evaluation editions of SQL Server.

So that may decide for you if any of your customers will not have the enterprise editions, or you don't yet know you will be using the enterprise editions. (As the spec includes "multiple future applications" this may be an real issue for you)

Unlike triggers it is not real time, this is both an advantage and a disadvantage. Using triggers always slow down an update.

I worked on one system when we used triggers (generated by CodeSmith), as well as tracking all the changes to the records, we also linked the changes together to a “history” table that included the module of the application that made the change, and the UI item the user used to make the change.

However you may be best solving this at the application level, by say writing all update to a message queue that is then replayed to create a database at any given point of time, see Temporal Patterns on Martin Flowler blog for a good overview of options.

Sql-server – Find the identity of the client firing a query in SQL Server without using triggers

I am not sure about CDC, but if the login has view server state permission you can use DMVs to get some information.

This is given in Books Online here. I changed the query to add columns which would give you the IP address:

SELECT 
    c.session_id, c.net_transport, c.encrypt_option, c.auth_scheme,
    s.host_name, s.program_name, s.client_interface_name,
    c.local_net_address, c.client_net_address, s.login_name, s.nt_domain, 
    s.nt_user_name, s.original_login_name, c.connect_time, s.login_time 
FROM sys.dm_exec_connections AS c
JOIN sys.dm_exec_sessions AS s
    ON c.session_id = s.session_id
WHERE c.session_id = SPID;  --session ID you want to track

Best Answer

Related Solutions

Sql-server – When to use CDC to track history

Sql-server – Find the identity of the client firing a query in SQL Server without using triggers

Related Question