unique and non-unique indexes should be separate options
drw
Posts: 4 Bronze 1
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?
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?
Comments
I've checked at this end and we already have one or two feature requests around the area of indexes, although not specifically what you're suggesting. I'll add a new request for it and hopefully we can implement it in a future version.
Thanks!
Redgate Software