Sql-server – One-directional Transactional Replication is causing one table’s Clustered Index to be copied over as a Nonclustered Index in the subscriber database

clustered-indexreplicationsql serversql-server-2016transactional-replication

This seems really odd to me, as I've used SQL Server's replication feature for a while now and have never seen this occur before.

I'm using One-Directional Transactional Replication and I have ServerA – the Publisher, and ServerB the Subscriber setup. I'm only replicating about 20 tables or so total.

In ServerA, Table1 has a couple of nonclustered indexes on it (including the primary key itself), and a separate clustered index. When the snapshot is generated to create Table1 on ServerB, all of it's indexes (even the clustered index) are created as nonclustered on the subscriber database in ServerB.

I even went so far as to take the following steps:

  1. Removed Table1 from the Publication's Articles
  2. Dropped Table1 from the subscriber database
  3. Re-initialized and generated a new snapshot
  4. Then re-added Table1 with the Article Properties set to false for "Copy nonclustered indexes"
  5. Then re-initialized and generated a new snapshot again

And it still recreated Table1 on the subscriber database with it's clustered index as a nonclustered index. It did not copy any of its actual nonclustered indexes except the primary key (which again is defined as a nonclustered index in the publisher's database).

Here's some screenshots to show it (sorry for all the obfuscation):

Table1 in Publisher vs Subscriber Database:

Table1 in Publisher vs Subscriber Database

Publisher Table1's sys.Indexes:

[Publisher Table1's ​sys.Indexes2

Subscriber Table1's sys.Indexes:

Subscriber Table1's sys.Indexes

Publication's Article Properties for Table1:

Publication's Article Properties for Table1

What the heck is going on?

Best Answer

maybe it's a bug in the SSMS; try to scritp the @schema_option and verity the proper value based on the list here:

https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-addarticle-transact-sql?view=sql-server-ver15

enter image description here