How to convert unique clustered index into clustered primary key to use with change tracking

Hi!

We are going to use SQL Sever change tracking. The problem is that some of our tables, which are to be tracked, have no primary keys. There are only unique clustered indexes. The question is what is the best way to turn on change tracking for these tables in our circumstances.

September 4th, 2015 11:04am

Hi zhichkin,

For these tables that only have unique clustered indexes, you can use the Change Data Capture (CDC) feature instead of Change Tracking (CT).

CDC does not depend on Primary key, it captures the changed data (Updated/Deleted/Inserted) and stores it in separate tables which are automatically created when we enable CDC. For more details, please review this article: Enable and Disable Change Data Capture (SQL Server).

However, if you persist to use CT for
those tables, you can drop unique clustered indexes by executing DROP INDEX firstly and create clustered primary key with the following script. Then you can turn on CT on these tables.

ALTER TABLE Yourtable

ADD CONSTRAINT PK_ Yourtable

PRIMARY KEY CLUSTERED (Yourfield)



Thanks,
Lydia

Free Windows Admin Tool Kit Click here and download it now
September 6th, 2015 10:50pm

Dear Lydia!

Thank you for your answer!

Unfortunately we doesn't have enterprise edition to use CDC.

CT is the only option for us.

Is dropping index the only way to accomplish our task?

September 8th, 2015 6:18am

Hi zhichkin,

Change Tracking (CT) depends on Primary key. You don't have to drop the existing unique clustered index. You can create a non-clustered Primary key on your table in order to use CT. 


Thanks,
Lydia Zhang
Free Windows Admin Tool Kit Click here and download it now
September 8th, 2015 10:00pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics