Sql-server – Creating a Primary Key on Existing table

alter-tableprimary-keysql serversql-server-2012

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?

Best Answer

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
Related Question