You are here:Home » SQL Server » Copy nonclustered indexes to subscriber while generating snapshot - SQL Server Replication

Copy nonclustered indexes to subscriber while generating snapshot - SQL Server Replication

Few days back, I was working to add new table in a publication in transaction replication. After adding an article in a publication and after reinitialized, I see non-clustered index was not copied at subscriber database. I tried reinitialized one more time and had a same issue. Let me generate scenario and look forward to the solution after that,
USE [PublisherDB]
GO

CREATE TABLE [dbo].[EmpList]
(
 [EmpId] [int] NOT NULL PRIMARY KEY,
 [EmpName] [varchar](100) NULL,
 [EmpCity] varchar(100) NULL,
 [EmpAddress] [varchar](200) NULL
)

CREATE NONCLUSTERED INDEX IX_EmpList_Name ON [dbo].[EmpList] ([EmpName]) 
I added above created a table in publication and reinitialized subscription with new snapshot and check indexes of that table at both publisher and subscriber side,

EXEC sp_helpindex [EmpList]


Non-clustered is not copied to subscriber after new snapshot applied. After some workaround I found a solution. Make true for the option of copy nonclustered indexes from article property,
Go to Publication property –>  Articles tab –> Article properties –> Set Properties of Highlighted Table Article or Set Properties of All Table Articles and apply changes as following,


After applying changes above and new snapshot generated after that, it copied non clustered indexes to the subscriber database. There are some other options you can find there,


All existing non-clustered indexes copied after new snapshot applied, but I created one more non-clustered index on that table and it's not copied subscriber database without generating a new snapshot. That new index copied after only new snapshot applied of that table. So I am leaving one discussion open here, why that newly created non-clustered index not copied without generating a new snapshot? Any alternative way or recommends solution?