is it possible to alter the primary key of a table (from non clustered to clustered) in the subscriber only and not break the replication?

Question

I have a table that has an clustered unique index and a non clustered primary key with the same structure as the index.

IF OBJECT_ID('[dbo].[tblBAccountHolder]') IS NOT NULL 
DROP TABLE [dbo].[tblBAccountHolder] 
GO
CREATE TABLE [dbo].[tblBAccountHolder] ( 
[lngParticipantID]  INT                              NOT NULL,
[sdtmCreated]       SMALLDATETIME                    NOT NULL,
[strUsername]       VARCHAR(20)                          NULL,
[strPassword]       VARCHAR(20)                          NULL,
[tsRowVersion]      TIMESTAMP                        NOT NULL,
CONSTRAINT   [PK_tblAccountHolder]  
PRIMARY KEY NONCLUSTERED ([lngParticipantID] asc),

CONSTRAINT   [IX_tblBAccountHolder__lngParticipantID]  
UNIQUE CLUSTERED    ([lngParticipantID] asc) 
WITH FILLFACTOR = 100)

Only one column as you can see on the definition:

 CREATE  UNIQUE CLUSTERED INDEX IX_tblBAccountHolder__lngParticipantID 
 ON [dbo].[tblBAccountHolder] (  [lngParticipantID] ASC  )

I would like to drop the unique index, and alter the primary key so that it is CLUSTERED. I will keep the same primary key, just change it from non clustered to clustered.

This table is part of transaction replication I would get this done on the subscriber database only.Not in the publisher.

It is a table with over 9,293,193 rows.

Will I mess up the replication?

the problem is that I have to drop the primary key constraint and re-create it as clustered.

this is what I would like to get done in the subscriber database:

drop INDEX IX_tblBAccountHolder__lngParticipantID 
        ON [dbo].[tblBAccountHolder]  
GO

ALTER TABLE [dbo].[tblBAccountHolder] 
       drop CONSTRAINT [PK_tblAccountHolder] 
GO

 ALTER TABLE [dbo].[tblBAccountHolder] 
   ADD  CONSTRAINT [PK_tblAccountHolder] 
 PRIMARY KEY CLUSTERED (  [lngParticipantID] ASC  )  
  WITH (  PAD_INDEX = OFF,
          FILLFACTOR = 95,
          SORT_IN_TEMPDB = OFF , IGNORE_DUP_KEY = OFF , 
          STATISTICS_NORECOMPUTE = OFF , ONLINE = ON , 
          ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON  ) ON [PRIMARY ] 
GO

score 7 · Accepted Answer · answered Oct 24 '16 at 13:47

No, It is not possible.

when the table is involved in replication it does not allow you to drop the primary key.

below is an example as how I have changed the primary key of a replicated table:

table is dbo.CategoryImportMap, database is Product_Staging.

---------------------------------------------------------------------------------------------------
-- remove the table from the replication
---------------------------------------------------------------------------------------------------

USE [Product_Staging]
GO
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

exec sp_dropsubscription @publication = N'Product_Staging', @article = N'CategoryImportMap', @subscriber = N'all', @destination_db = N'all'
GO
exec sp_droparticle @publication = N'Product_Staging',      @article = N'CategoryImportMap', @force_invalidate_snapshot = 0
GO

---------------------------------------------------------------------------------------------------
-- do the schema changes
---------------------------------------------------------------------------------------------------

BEGIN TRANSACTION;

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

SET XACT_ABORT ON;


SELECT @@TRANCOUNT


ALTER TABLE [dbo].[CategoryImportMap]
 DROP CONSTRAINT [PK_CategoryImportMap] 

DROP index [UC_SegmentCategory]
on  [dbo].[CategoryImportMap]

ALTER TABLE [dbo].[CategoryImportMap]
ADD CONSTRAINT   [PK_CategoryImportMap]  PRIMARY KEY CLUSTERED   (  [MPlanSegmentCode] ASC  , [MPlanCategoryCode] ASC  )



SELECT @@TRANCOUNT

COMMIT TRANSACTION


---------------------------------------------------------------------------------------------------
-- add the table back to the replication
---------------------------------------------------------------------------------------------------

SET TRANSACTION ISOLATION LEVEL read committed;

EXEC sp_addarticle @publication = N'Product_Staging', 
                   @article = N'CategoryImportMap',
                   @source_object=N'CategoryImportMap',
                   @destination_table =N'CategoryImportMap'
GO

--========================================================================
-- REFRESH THE SUBSCRIPTIONS
--========================================================================
EXEC sp_refreshsubscriptions @publication = N'Product_Staging'
GO

--========================================================================
-- Start the Snapshot Agent job.
--========================================================================
EXEC sp_startpublication_snapshot @publication = N'Product_Staging'
go


-- test number of rows (source and destination)
sp_count 'CategoryImportMap'

score 5 · Answer 2 · edited Apr 13 '17 at 12:43

Add the following checks before starting to deploy Marcello's scripts:

Make sure that immediate_sync and allow_anonymous are set to FALSE (0) to avoid the creation of the full snapshot and not just the snapshot of the article/s changed.

SELECT immediate_sync, allow_anonymous 
FROM Your_Published_DBName.dbo.syspublications

If those values are not set to FALSE you can change them with the following:

EXEC sp_changepublication
@publication = 'reDBA',
@property = N'allow_anonymous',
@value = 'false'

When immediate_sync is disabled the transactions are deleted as soon as they are replicated to the subscriber. It is generally safe to disable unless there is a specific business need to add new subscribers or reinitialise existing.

EXEC sp_changepublication
@publication = 'reDBA',
@property = N'immediate_sync',
@value = 'false'

After these simple checks, you can go ahead with Marcello's scripts savings loads of time, troubles and downtime.

is it possible to alter the primary key of a table (from non clustered to clustered) in the subscriber only and not break the replication?

2 Answers2

Linked