Manage indexes separately from tables
When comparing tables, additions/changes in indexes are included as part of the differences to the table. I would like a way to separate index management from table management. In other words, it would be nice to be able to select a table for modification without including the indexes. I know we can do this from a project level with settings, but if there are many tables selected for change, I would like to be able to include the indexes for some tables and not others. Also, it would be nice to be able to select individual indexes on a given table. Sometimes multiple indexes would display for a given table and it would be nice to hand pick which indexes are being selected for promotion.
At this time, we don't have any plans to separate indexes because it's a big architectural change.
Dean Parker commented
This is surprising. We cannot vote for this any more even though it is still important?
I was going to repost this but I see that in new versions you've actually started implementing this idea.
Very disappointing. No way to exclude indexes automatically created by Azure SQL Database.
Would like: /Exclude:Index:nci_*
Simon Collis commented
It may be a big architectural change, but I suspect I (and a lot of others) would very strongly consider moving to a competing product simply for this feature.
Jason Mowbray commented
Very disappointed to hear this is considered too much effort.
Vote ++. I'd even be happy with just an indicator telling whether the table, the index , the trigger, or privileges are different if that is an easier idea. Ideally I want to be able to list all the index differences. I've had this need for privileges as well.
Davis Henely commented
It has been awhile, but yes, this feature was a part of the compare tool. I have been using this tool for over 12 years, so, it may have been a bit ago, but as has been described.
It really shouldn't be that big of a lift.
BTW, we could also exclude schema in the comparisons, too. There is inherent issues with that, yes, but it was something I used in days of yore, and I could use that now. But that is a different topic. :)
Louis Somers commented
I voted for this but to be fair I do have some 2nd thoughts. In SqlSourceControl (which is clearly a shell with a different UI around the same product) it is really handy to go up to a table, view history and see when indexes on a table have changed in the past.
If a separate directory with all indexes would be introduced, then this would no longer be possible.
To keep the trace-ability, each table would have its own directory and the index files should be stored in that directory.
By the way, when table schema's do not match (columns have been added) it will really complicate the index comparison. Obviously one cannot add non-existing columns to an index, so ripping the two apart would cause quite some complexity. Especially if you allow the user to select the index for synchronization without selecting the underlying table.
I guess the best route for RedGate would be:
- Create a separate tool for the scenario where one wants to take the indexes of a tuned production system and merge those back into the development trunk or master scripts.
- Then integrate that tool into SqlCompare via a menu / tab or whatever the UX experts come up with.
That way the main migration code will not be "bugged" with the complexities of migrating individual properties (fill factor, padding, allowing locks etc) across schemes while columns may no longer match. This also would require a different UI.
I could really use this feature. Vote++
I see this has been a request for many years. It is also one that I've needed for many years; and no other tool seems to offer this. Putting it simply, this is one of the most important functions I need and would consider buying your product if it supported index only comparisons.
Yes, this is a much-needed function.
I certainly could use this option today as we are upgrading an huge application and knowing City created indexes missing from the upgraded system would be very helpful
With the number of client databases we're supporting, testing, refactoring, missing indexes is the issue most often. This tool with the index-only option would be a 100% improvement, in our environment. Makes me sick going through the output line by line to find missing indexes.
This would be helpful when you only want to sync the indexes and don't want to worry about the other differences. I've had this need several times when copying data for development use.
Ankita Patel commented
this option is highly required to compare missing indexes between dev and Prod server .
This is much needed feature when i only have to deploy the indexes of one database to another. Going through each table definition to identify the index difference is a very tedious job and wastes a lot of time.
Mike Boyle commented
This feature would really be useful I actually could not believe I had to go thru each table to look for index differences. Hope this feature will exist in very near future.
Having bought SQLCompare for my client a few months ago but not had a reason to use it in anger until now, I just spun it up assuming that comparing indexes would be a feature, and somewhat shocked that it's not!
Please do what you can to include it ASAP as it's so important to compare database indexing when trying to work out why one database is outperforming another.