Index is being dropped and recreated for no reason

JackAce

We are currently using the SQLCompare.exe command line application to generate deployment scripts for our main database. We are also using SQL Source Control to version it.

After generating the synchronization script for our production deployment, I noticed that there is an index that is being dropped at the beginning of the script and then recreated at the end. The table is not being touched as part of the deployment. Why SQL Compare is dropping and recreating the index is a mystery to me.

We'd really prefer that the index not be dropped, since the table is quite large (around 3 million rows) and recreating the index adds about 10 minute to our deployment time.

Two questions:
* Can you tell me why the index might be dropped when the table is not changing at all?
* Is there a command line option that we can use to prevent the index from being dropped? We are using the /argfile parameter in SQLCompare to generate the script.

Below is a cleansed version of the argument file that we are passing to SQLCompare:

<?xml version="1.0"?>
<commandline>
	<scripts1>RedGateScripts</scripts1>
	<server2>Server</server2>
	<database2>DB</database2>
	<include>Assembly</include>
	<include>Function</include>
	<include>StoredProcedure</include>
	<include>Table</include>
	<include>View</include>
	<include>UserDefinedType</include>
	<include>Contract</include>
	<include>MessageType</include>
	<include>Queue</include>
	<include>Service</include>
	<include>Additional</include>
	<include>Different</include>
	<include>Missing</include>
	<include>Identical</include>
	<options>IgnoreStatisticsNorecompute</options>
	<options>IgnoreConstraintNames</options>
	<options>IgnoreNotForReplication</options>
	<options>IgnoreQuotedIdentifiersAndAnsiNullSettings</options>
	<options>IgnoreUsersPermissionsAndRoleMemberships</options>
	<scriptfile>UpdateSchema_DB_Server.sql</scriptfile>
	<force/>
</commandline>

Brian Donahue

If you need to know the reason why the index gets recreated, can you try running the same comparison in SQL Compare (the UI version), go through the process of synchronizing the databases, and on the last page of the sync wizard, look at the warnings tab. There may be some information there.