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
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
September 8th, 2015 10:00pm