We have a partition table with 2 billion records and we need to assign a primary key to one of the columns for enabling change tracking feature on it.

After searching we found out that primary key can be assigned by using below query :-

USE AdventureWorks2012;
ALTER TABLE Production.TransactionHistoryArchive 
    ADD CONSTRAINT PK_TransactionHistoryArchive_TransactionID 

But wanted to know if it is really possible to assign a PK to such huge table without dropping it and if yes , how much time will it consume in doing so?

Also is there any other option to enable Change tracking on this table as it does not consist a Primary key. Help on this query will be much appreciated.

Note:- This column does not contain duplicate values and is also set to NOT NULL. Also the existing column is set as identity (1,1), how do we convert the identity column to a Primary key?

  • So you have a table that has no PRIMARY KEY or UNIQUE constraint? It would help if you added in the question the CREATE TABLE statement of the table and its existing indexes. Sep 29, 2016 at 13:32
  • yes correct .... Sep 29, 2016 at 13:34
  • Do you have a test environment with similar hardware? There are so many variables it will be impossible to give a proper time estimate.
    – datagod
    Sep 29, 2016 at 13:48
  • Yes we do have a TEST environment with almost similar hardware were we will be testing it before proceeding to PROD . But also do we have any other option to enable Change tracking without a PK on a table. Sep 29, 2016 at 13:50
  • 1
    I don't believe there is a way around the PK requirement. Sep 29, 2016 at 14:35

1 Answer 1


Yes what you want to do is possible. It is impossible, from what you've given, to know how long it will take or how many additional resources will be required in terms of disk space. Testing will get you those answers.

Some things to take into consideration...

  • You are going to need additional space for your new constraint/index. Ensure the TransactionID is as small and unique as possible to maximize on space usage
  • Is your TransactionID ever growing or can it be changed for a given record? You want the former to minimize fragmentation leading to wasted disk and performance issues
  • Assuming you have other indexes, you will want to script those indexes out and drop them prior to creating the PK/clustering key. Reapply the indexes once completed; this will add additional time against your maintenance window.
  • You will essentially be ordering your data since you are also making your Primary Key your clustered index as well; you will need to ensure there is plenty of additional disk space to accommodate the sorting operations
  • 1
    Also keep in mind what this will do to your transaction logs and make sure you have enough space in them to handle the transacition. Sep 30, 2016 at 23:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.