1

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
4

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'
3

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.

  • very good observation, thanks for adding this answer. Grazie. – marcello miorelli Jan 27 '17 at 10:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.