Create an index on a table in Transactional Replication

Not long ago I took over a database that uses Transactional Replication. Not long after I needed to add an index.  So here is the syntax and misc I found for doing this:

CREATE NONCLUSTERED INDEX ListingBigBay_NC6f_NULL

ON [Bigtiva].[listing_BigBay] ([adid],[ListingDate])
INCLUDE ([AccountID])
WHERE (adid IS NULL)
WITH (PAD_INDEX = ON,
STATISTICS_NORECOMPUTE = OFF,
SORT_IN_TEMPDB = ON,
DROP_EXISTING = OFF,
ONLINE = ON,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON,
FILLFACTOR = 90,
MAXDOP = 4)

GO

The important point is that you use ONLINE=ON, and since our TEMPDB is on SSD, go ahead and use SORT_IN_TEMPDB.

Leave a comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.