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 Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s