Just a preface that I'm working at the moment as an accidental DBA so sorry for any ignorance.

I need to completely rebuild one of our transactional replications. The indexing strategy is different on the subscriber database than it is on the publisher, the primary key is the same but the clustered index on NC indexes are different. I am aware that the tables by default are completely dropped on the subscriber. I believe I can use scripts pre and post snapshot application to drop and create indexes.

Both the clustered indexes on the subscriber and publisher are UniqueIdentifier but different columns. The table has nearly 1 billion rows and is sadly pretty wide. Just thought that I'd provide that information in case it makes a difference.

My questions are:

  • Is using the post execution scripts the best way to go about this?
  • If the clustered index is different on the subscriber than the publisher am I best to allow the bulk insert to happen with the publisher clustered index or would it be better to set the alternate clustered index before the bulk insert?

Thanks in advance for any help.


In my opinion, because of the table size the better way is use the strategy truncate table in the publication options and use pre script to create all index before to proceed with the load.

Two other question:

It's work? I mean this replication was synchronized with more the 1 bilion rows? At my work a have problems to replicated table with more the 300 milions rows.

Is this table was partitioned? With this size I really recommended that.

  • 1
    Yeah it works. It takes about 6 hours to process the snapshots and then another few hours to rebuild the indexes. Unfortunately we don't have enterprise so I can't partition it unless we did a partitioned view but we often query all over the range so from what I understand a partitioned view would be quite slow. So your suggestion would be to drop the nonclustered indexes, Truncate the table and let it insert with the current clustered index in place then add the nonclustered indexes again? Just making sure I'm clear. Thanks – Bee_Riii Jun 8 '17 at 15:16
  • @Bee_Riii I corrected my answer. – Krismorte Jun 8 '17 at 15:30

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.