CDC does not require Unique Index
Change Data Capture feature of SQL 2008 is based on Transactional Replication. CDC and Transactional Replication used the same internal stored procedure sp_replcmds to retrieve data updates from the transaction log. However, unlike Transactional Replication, CDC does NOT require the tables to have a unique index (or Primary Key). In fact CDC can be use to record data changes on tables without any indexes.
The unique clustered, unique nonclustered, or PK check constraint requirement for Transactional Replication is needed for the Distribution Agent to identify a unique row on the target (subscriber) when applying changes downstream. Since CDC was designed for developers to write their own distribution feature, this unique index requirement is not needed. A unique index is still recommended because without a unique index it is more challenging to determine which rows were actually updated.
Exception (unique index required)
To enable CDC on a table using CDC option “@supports_net_changes” = 1, a unique index is required or the error below occurs.
EXEC sys.sp_cdc_enable_table
@source_schema = N’Sales’
, @source_name = N’Store’
, @role_name = N’cdc_Admin’
, @supports_net_changes = 1
GO
Msg 22939, Level 16, State 1, Procedure sp_cdc_enable_table_internal, Line 182
The parameter @supports_net_changes is set to 1, but the source table does not have a primary key defined and no alternate unique index has been specified.
In the example below a non-clustered unique index as added and specified in the sys.sp_cdc_enable_table options.
–Create unique nonclustered index
CREATE UNIQUE NONCLUSTERED INDEX [IX_Store] ON [Sales].[Store]
([BusinessEntityID] ASC
) ON [PRIMARY]
GO
–Enable CDC “net changes” using unique non-PK index
EXEC sys.sp_cdc_enable_table
@source_schema = N’Sales’
, @source_name = N’Store’
, @role_name = N’cdc_Admin’
, @supports_net_changes = 1
, @index_name = N’IX_Store’
GO
