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?