0

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;
GO
ALTER TABLE Production.TransactionHistoryArchive 
    ADD CONSTRAINT PK_TransactionHistoryArchive_TransactionID 
    PRIMARY KEY CLUSTERED (TransactionID);
GO

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. – ypercubeᵀᴹ Sep 29 '16 at 13:32
  • yes correct .... – Karan Salvi Sep 29 '16 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 '16 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. – Karan Salvi Sep 29 '16 at 13:50
  • 1
    I don't believe there is a way around the PK requirement. – Kris Gruttemeyer Sep 29 '16 at 14:35
3

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. – Shawn Beddes Sep 30 '16 at 23:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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