Is there any way that "ignore indexes" could be separated into two options "ignore unique indexes" and "ignore non-unique indexes"?
Primary keys, unique constraints, and unique indexes make a significant functional difference in the database's behavior, and it makes sense to expect a database developer to create the unique indexes that the application needs in order to enforce business rules and data integrity. Those indexes should be created in a development environment, checked in, and then deployed to a production environment.
Non-unique indexes, on the other hand, make no difference to the behavior of a database; they only make a difference in its performance. The database server may choose whether or not to use non-unique indexes when executing queries, and its choices depends greatly on the amount and quality of data in the tables. It can be very difficult for a developer to know what indexes will be needed in a production environment, especially if the developer only has access to the development database. If the development server never uses any indexes because it has such a small amount of data, then indexes can't be created and tested in development; it makes more sense to maintain non-unique indexes directly in the production environment. Those indexes should not be overwritten with other database changes deployed from development.
SQL Compare is a great tool for deploying database changes from one environment to another, but there doesn't seem to be any way to do a synchronization that includes changes to unique indexes but not to non-unique indexes. Is there any chance that a future version will provide that ability?
Thanks for your post.
Primary keys, unique constraints, and unique indexes make a significant functional difference in the database's behavior, and it makes sense to expect a database developer to create the unique indexes that the application needs in order to enforce business rules and data integrity. Those indexes should be created in a development environment, checked in, and then deployed to a production environment.
Non-unique indexes, on the other hand, make no difference to the behavior of a database; they only make a difference in its performance. The database server may choose whether or not to use non-unique indexes when executing queries, and its choices depends greatly on the amount and quality of data in the tables. It can be very difficult for a developer to know what indexes will be needed in a production environment, especially if the developer only has access to the development database. If the development server never uses any indexes because it has such a small amount of data, then indexes can't be created and tested in development; it makes more sense to maintain non-unique indexes directly in the production environment. Those indexes should not be overwritten with other database changes deployed from development.
SQL Compare is a great tool for deploying database changes from one environment to another, but there doesn't seem to be any way to do a synchronization that includes changes to unique indexes but not to non-unique indexes. Is there any chance that a future version will provide that ability?