DROP INDEX entry when using option 'Ignore Indexes'
jela
Posts: 13
Hi All,
today I stumbled across an issue when comparing tables. I knew from the start, that the source table would have some additional columns compared to the target. As the source is the production environment and the target an archiving environment I wasn't interested in permission nor indexes. Starting from the 'Red Gate Defaults' for the project options I additionally checked the boxes to ignore collations, permissions, indexes and 'NOT FOR REPLICATION'. The comparison showed differences in the columns and nothing else (as expected), but when I created a deployment script suddenly a 'DROP INDEX' appeared in the script.
I wouldn't have expected this DROP INDEX as I checked the option to (completely) ignore indexes.
After investigating a little further, it turned out, that an index with the name (given in the DROP INDEX) existed in both databases. Going back to MS SQL Server Management Studio I checked the index in both databases. The names are the same as are the indexed columns - some of the options differed though (namely PAD_INDEX and IGNORE_DUP_KEY). The two main differences I found were, that on the source the index is unique non-clustered and lives on a non-PRIMARY filegroup whereas it is only non-clustered on the target and is located on PRIMARY. The option to ignore 'Filegroups, partition schemes and partition functions' is part of the 'Red Gate Defaults' for the project options though. Seeing that an index with the same name exists in both databases, I also tested changing the direction for the generation of the deployment script - the index would also have been dropped on the (initial) source.
In addition to my confusion about the initial DROP INDEX mentioned above, I now would have at least expected some sort of re-creation of the index (probably with different options), but this re-creation could not be found in the deployment script!
Trying a few other things, it turned out, that the option to ignore 'Constraint and index names' would change the observed behavior (and the DROP INDEX would disappear).
We were able to duplicate the same behavior with different version of SQL Compare (as I only upgraded to 11.5.0 this morning and a colleague of mine was still using 11.4.3).
To summarise all the above:
Has anybody observed a similar behavior? Would anybody know what exactly may cause this behavior and/or whether this is 'by design'?
Thanks and kind regards,
jela
today I stumbled across an issue when comparing tables. I knew from the start, that the source table would have some additional columns compared to the target. As the source is the production environment and the target an archiving environment I wasn't interested in permission nor indexes. Starting from the 'Red Gate Defaults' for the project options I additionally checked the boxes to ignore collations, permissions, indexes and 'NOT FOR REPLICATION'. The comparison showed differences in the columns and nothing else (as expected), but when I created a deployment script suddenly a 'DROP INDEX' appeared in the script.
I wouldn't have expected this DROP INDEX as I checked the option to (completely) ignore indexes.
After investigating a little further, it turned out, that an index with the name (given in the DROP INDEX) existed in both databases. Going back to MS SQL Server Management Studio I checked the index in both databases. The names are the same as are the indexed columns - some of the options differed though (namely PAD_INDEX and IGNORE_DUP_KEY). The two main differences I found were, that on the source the index is unique non-clustered and lives on a non-PRIMARY filegroup whereas it is only non-clustered on the target and is located on PRIMARY. The option to ignore 'Filegroups, partition schemes and partition functions' is part of the 'Red Gate Defaults' for the project options though. Seeing that an index with the same name exists in both databases, I also tested changing the direction for the generation of the deployment script - the index would also have been dropped on the (initial) source.
In addition to my confusion about the initial DROP INDEX mentioned above, I now would have at least expected some sort of re-creation of the index (probably with different options), but this re-creation could not be found in the deployment script!
Trying a few other things, it turned out, that the option to ignore 'Constraint and index names' would change the observed behavior (and the DROP INDEX would disappear).
We were able to duplicate the same behavior with different version of SQL Compare (as I only upgraded to 11.5.0 this morning and a colleague of mine was still using 11.4.3).
To summarise all the above:
- A DROP INDEX appeared in a deployment script when the ignore indexes option was checked
- Indexes with the same name exist in both databases, thus a re-creation would at least have been expected
- Ignore constraint and index names changed the created deployment script
- Replication of the observed behavior in two different versions of SQL Compare was possible
Has anybody observed a similar behavior? Would anybody know what exactly may cause this behavior and/or whether this is 'by design'?
Thanks and kind regards,
jela
Comments
Sorry that you're experiencing a problem with SQL Compare. I've been able to recreate the problem here. I'll speak to the developers and let you know when I have some more information.
Redgate Software
thanks for the quick update on this topic.
I'm looking forward to hearing from you with a solution as we currently would have to properly check every deployment script.
Kind regards,
jela
This bug has been fixed in the latest frequent updates release (11.5.2.509). You can get this update via Check for Updates in SQL Compare or you can use this download link.
Redgate Software