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.
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.