StarQuest Technical Documents

Preserving Non-Unique Indexes for SQL Server Destinations

Last Update: 12 July 2016
Product: StarQuest Data Replicator
Version: SQDR 4.6 or later
Article ID: SQV00DR035


This tech note describes stored procedures which can be installed on a SQL Server destination database to preserve existing non-unique or nonclustered indexes for replicated tables. The procedures are installed by executing SQL scripts and may be modified to change the schema associated with each procedure. After the procedures are created, any subscription may be configured to use them. The result is that all non-unique indexes on the destination tables are rebuilt at the conclusion of baselines.

There are three stored procedures included in this package:

SQDR.DISABLEINDEXES - Disables all NONCLUSTERED indexes for a table
SQDR.REBUILDINDEXES - Rebuild all NONCLUSTERED indexes for a table
SQDR.STATUSINDEXES - Check the status of indexes for a table


  1. Add destination schema "SQDR" using SQL Server Management Studio or execute the following dynamic SQL:
    create schema SQDR
    If you prefer to use a different schema for the procedures, you can modify the installation scripts:
  2. Using SQL Server Management Studio or dynamic SQL, create the stored procedures by executing the scripts disableidx.sqlrebuildidx.sql and statusidx.sql (optional).
  3. Configure the subscription to “Use existing table for baseline”:

  1. Add Destination Before and After Processing statements for the subscription:

  1. Save the subscription and run the baseline.

Syntax for executing the stored procedures:

EXEC SQDR.<stored_procedure_name> "<schema>", "<table_name>"


  • To Disable Indexes:
    EXEC SQDR.DISABLEINDEXES "dbo", "mytable"
  • To Rebuild Indexes:
    EXEC SQDR.REBUILDINDEXES "dbo", "mytable"
  • To Check Status of Indexes:
    EXEC SQDR.STATUSINDEXES "dbo", "mytable"
    This returns the unqualified name of the index(s) and 0=false or 1=true to indicate if the index(s) is disabled.


The information in technical documents comes without any warranty or applicability for a specific purpose. The author(s) or distributor(s) will not accept responsibility for any damage incurred directly or indirectly through use of the information contained in these documents. The instructions may need to be modified to be appropriate for the hardware and software that has been installed and configured within a particular organization.  The information in technical documents should be considered only as an example and may include information from various sources, including IBM, Microsoft, and other organizations.