Using the use_index option in a replicate database

The use_index option is used to speed up the process of setting the text, unitext, image, or rawobject columns for replication. It is specially useful for large tables containing one or more text, unitext, image, or rawobject columns. You can set use_index option at a database level, table level, or column level. For example, a table can be marked without using indexes, but you can explicitly mark only one column to use an index for replication.

When you use the use_index option with sp_reptostandby, the database is marked to use indexes on text, unitext, image, or rawobject columns, and internal indexes are created on tables that are not explicitly marked for replication.

For a database marked for replication to use indexes, if a new table with off-row columns is created, the indexes for replication are created as well. Similarly, when an alter table...add column command is executed in a database marked to use indexes, an internal index is created in the new off-row column. With the alter table...drop column command, if the column being dropped is marked to use an index, the internal index for replication is dropped as well.

The replication index status at different object levels is in this order: column, table, and database. If the database is marked to use indexes for replication, but you marked a table without using indexes, the table status overrides the database status.

You can use the use_index option if the table has a large number of rows or if the database has one or more tables with a considerable number of rows and several off-row columns.