Saturday, May 7, 2011

Transactional Replication and Primary Key

When creating a publication, I discovered one of the tables was not allowed as an article in the publication since it had no primary key:

Create table VendorData (
  colUniqueID1    int NOT NULL,
  colUniqueID2    int NOT NULL,
  colFiller1      varchar(1000),
  colInclude1     char(4),
  colInclude2     int,
  colFiller4      varchar(1200),
  colClusterCol   int NOT NULL);
go

Create unique index ix_VendorData_colUniqueID1_ID2
      on VendorData(colUniqueID1, colUniqueID2)
      include (colInclude1, colInclude2);
go

Create clustered index ix_VendorDataClusterCol on VendorData(colClusterCol);
Go

I was not sure how to create the primary key for the purpose of replication and not change the table in a way that would change the way query results were returned.

The only difference (I am aware of) between a primary key and a unique index is that a unique index allows nulls and the primary key does not.  Since the unique index in this case is composed of two columns that each have the NOT NULL constraint, the unique index is functionally the same as a primary key and should produce the same query results.  However, this unique index in this scenario is also a covering index.  I do not know a way of creating a primary key that is also a covering index.

The solution I came up with was to keep the unique covering index and add the primary key constraint to the table using the columns in the unique index as components of the primary key – without the included columns of course.

Alter table VendorData WITH NOCHECK
Add constraint PK_VendorData_colUniqueID1_colUniqueID2 PRIMARY KEY (colUniqueID1, colUniqueID2)

This did work.  I was not sure if the index would actually be created or if the SQL parser would realize the index already existed and point the primary key to the existing unique index (with the caveat that the columns would retain their NOT NULL constraint).  The index was created in seconds on a table with >120mil rows.  My guess is that the index for the primary key was not re-generated, but used the existing unique index.

Comments welcome.

No comments:

Post a Comment