(This is actually relevant to both SQL Compare and SQL Source Control.)

We really need to be able to distinguish between unique and non-unique indexes when comparing, checking in, and deploying database schema changes. ("Unique indexes" include primary keys, unique constraints, and unique indexes on tables and unique indexes on views; "non-unique indexes" include non-unique indexes on tables and views.)

Unique indexes are essential to the database development process. They make a significant functional difference to the behavior of the database: they enforce business rules and referential integrity; they limit the data that can be entered into the database; foreign keys depend on them. Developers should be responsible for creating and maintaining all of the unique indexes on the development database, and an automated deployment process that uses SQL Compare and SQL Source Control should be able to deploy all database schema changes, including unique indexes, from the development database to the production database.

On the other hand, non-unique indexes do not affect the database's behavior; they only affect the database's performance. Developers should not be responsible for maintaining non-unique indexes; they can't. A development database usually has a relatively small amount of test data in it, and a production database usually has a much larger amount of real data. SQL Server uses different indexes and different query execution plans depending on the amount and quality of data in the database, and the servers recommend different indexes depending on which queries get used the most frequently. A developer, running queries on the development database and looking at the execution plans to determine which indexes are needed, will get results that are not valid for the production database. In our case, it is also common for one set of queries to be heavily used one month and an entirely different set of queries to be heavily used the next month, so the developers would never be able to keep up with the production server's changing needs.

Instead, we have a SQL Agent job that runs periodically on the production server to maintain non-unique indexes. The job reads and analyzes the system view sys.dmdbindexusagestats to determine which indexes are the least-recently used and the most-frequently updated, and drops them. It also reads and analyzes the system views sys.dmdbmissingindexgroupstats, sys.dmdbmissingindexgroups, and sys.dmdbmissingindex_details to determine which missing indexes are the most needed, and creates them. This index maintenance job frees up developers to do actual development instead of tracking down performance problems and fixing them.

The problem is that SQL Compare and SQL Source Control do not distinguish between unique indexes and non-unique indexes; it will compare and synchronize either all of them or none of them. If we ignore indexes, then we can't deploy primary keys, unique constraints, unique indexes, or foreign keys (since foreign keys depend on unique indexes); if we include indexes, then the non-unique indexes created on the production server will be dropped in favor of the non-unique indexes that happen to exist on the development server, undoing all of the performance improvements made by the automatic index maintenance job.

We would like the "Ignore indexes" option of SQL Compare and SQL Source Control to be split into two options, "Ignore unique indexes" and "Ignore non-unique indexes".