Um blog sobre nada

Um conjunto de inutilidades que podem vir a ser úteis

Primary Key on CDC

Posted by Diego em Fevereiro 13, 2013


So yeah, CDC “needs” a Primary Key on the table that you want to enable it with supports_net_changes

But what if you don’t have one in your table? The thing is that a PK is only a logical concept. What’s behind it it’s the important thing. When you create a PK, behind the scenes, you are actually creating a UNIQUE clustered index on the column (not great news here). But what’s important is to separate the CLUSTERED part from the UNIQUE part.  The clustered part tells how the table’s data will be arranged on the DB while the UNIQUE enforces that the data on that columns is UNIQUE among all columns. And that’s what CDC wants.

 

Ok, long story short, 99.9% of the examples you find on the internet are like this:

EXEC sys.sp_cdc_enable_table

@source_schema = N’MySchema’,

@source_name = N’MyTable’,

@role_name = N’cdc_role’,

@supports_net_changes = 1

 

but if you try to run it on a table without a PK, you’ll get this error:

 

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.

 

 

The catch is on the end of the statement, “no alternate unique index has been specified”, and that can be easily solved by adding a new parameter

 

EXEC sys.sp_cdc_enable_table

@source_schema = N’MySchema’,

@source_name = N’MyTable’,

@role_name = N’cdc_role’,

@supports_net_changes = 1,

@index_name = N’MyUniqueIndexName’

 

Deixe uma Resposta

Preencha os seus detalhes abaixo ou clique num ícone para iniciar sessão:

Logótipo da WordPress.com

Está a comentar usando a sua conta WordPress.com Terminar Sessão / Alterar )

Imagem do Twitter

Está a comentar usando a sua conta Twitter Terminar Sessão / Alterar )

Facebook photo

Está a comentar usando a sua conta Facebook Terminar Sessão / Alterar )

Google+ photo

Está a comentar usando a sua conta Google+ Terminar Sessão / Alterar )

Connecting to %s

 
%d bloggers like this: