Submit My Question - Recent Questions - Recent Answer
Msdn Tech >> SQL Server Katmai >> SQL Server Katmai Replication

CDC - Inconsistent behaviour (?) in allowing PK modification

I executed below scenarios and the behaviour seems to be inconsistent. I noticed cdc.change_tables tracks table details with index_name but BOL doesn't explain what is/isn't possible interms of modifying PK. Is #1 by design, if so it needs to be clarified.

  1. Enable CDC on a table with PK. Later try to disable/drop/change PK definition on base table – It is not allowed
  2. Enable CDC on a table with no PK. Later try to create/change/drop PK definition on base table – It is allowed

Note: NET changes is not enabled in both cases.

Thanks,

Siva

[1694 byte] By [SivakumarRavindranath] at [2008-1-8]
«« TableAdapter Query Configuration Wizard: When using only a Fill method cannot add additional Fil
»» Help starting up
# 1
let me get back to you on this...
GregY at 2007-10-2 > top of Msdn Tech,SQL Server Katmai,SQL Server Katmai Replication...
# 2

I see you've already filed this issue in connect. I'll cut/paste the response here as well for anyone else that's wondering the same thing.

The behavior is by design. When CDC is enabled and if a primary key exists on the table, CDC will use the index regardless of whether net changes is enabled or not.

If there is no primary key on the table, you can still enable CDC but only with net changes set to false. You are then able to create a primary key and alter it since CDC does not use the PK.

This will be documented in BOL.

GregY at 2007-10-2 > top of Msdn Tech,SQL Server Katmai,SQL Server Katmai Replication...
SQL Server Katmai
SQL Server Katmai Replication

SQL Server Katmai Hot Topic

SQL Server Katmai New Topic

SQL Server Katmai

Java / MSDN / Linux Tech / PHP Tech / MSDN DotNet / MSDN TECH / Health Library / Programmers Tech / visual basic Tech / Developer Tech / Ipod faqs / Msdn China Tech

Site Classified