是否可以僅在訂閱者中更改表的主鍵（從非集群到集群）而不破壞複製？

我有一個表，它有一個聚集唯一索引和一個與索引結構相同的非聚集主鍵。

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)

正如您在定義中看到的那樣，只有一列：

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

我想刪除唯一索引，並更改主鍵以使其成為集群。我將保留相同的主鍵，只需將其從非集群更改為集群。

該表是事務複製的一部分，我只會在訂閱者數據庫上完成此操作。不在發布者中。

這是一個包含超過 9,293,193 行的表。

我會搞砸複製嗎？

問題是我必須刪除主鍵約束並將其重新創建為集群。

這就是我想在訂閱者數據庫中完成的事情：

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

不，這是不可能的。

當表參與複製時，它不允許您刪除主鍵。

下面是我如何更改複製表的主鍵的範例：

表是dbo.CategoryImportMap，數據庫是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'

引用自：https://dba.stackexchange.com/questions/134685