Constraints and Indexes order

LeeC

How can I make my comparisons ignore the order of table "Constraints and Indexes"? I get false mismatches due to index ordering.

Thanks,
Lee

Brian Donahue

Hi Lee,

Are you sure that this is the case? SQL Compare doesn't concern itself with the order of indexes, keys and constraints.

LeeC

In the linked image, there is a constraint line that appears identically in both sides:
CREATE UNIQUE NONCLUSTERED INDEX [UX_BuildingName] ON [dbo].[ServiceLocations] ([LocationName])

LeeC

It's been a month now. I took the time to prove the issue. The courtesy of a reply would be appreciated.

david connell

Hi Lee,
Very sorry for the delay.
I need to verify some information with a developer and I will get back this afternoon.
Best Regards
David Connell

LeeC

Thanks, David. I keep running into this issue. It causes a lot of false positives for me. I look forward to a solution.

, Lee

david connell

Hi Lee,
I understand that this will lead to false positives within the SQL Difference viewer, however it is the expected functionality. The alter tables are ordered before the create indexes and as a result the SQL is not correctly aligned. The SQL Differences panel will highlight the Create Index/Alter Table as a difference. Although it is not considered when determining the status for an object (Different, Identical, Only in db 1 or db 2).

We will investigate in the future splitting up the SQL labelled as “Constraints and Indexes” into smaller more logical blocks, which could help in your situation.

Best regards,
David Connell

LeeC

I appreciate the reply, David, but I do not see any logic in calling it "expected functionality" in a tool that has the primary purpose of comparing databases.

I expect to only be shown differences--true difference, not false differences that result from so-called developer-expected functionality.

Bugs are not features, even if the developers expect them. This bug will result in two identical databases (with indexes established in varying orders) being deemed different, when they are not different in the real world.

Sincerely,
Lee

david connell

Hi Lee,
Thank you for your comments.
Please note that in the case of two identical databases with indexes established in varying orders, the objects will be marked as identical. However the SQL Difference viewer will highlight the order difference.
Best regards
David

LeeC

Thanks for the clarification, David. I was not understanding that before now. It does lessen the impact in cases where index order is the only "difference", but it still makes for work to factor it out in cases where there are other actual differences.

Cordially,
Lee

NationwidePaul

We have been getting the same problem (message sent to support@red-gate today). By where SQL Compare is detecting differences in the order of the columns in the INDEX Include clause.

We have performed a test by where we compare the database against itself (physically) and SQL Compare still sees the indexes as being different.

It does look like a bug to me

NationwidePaul

Additional information :

I have just discovered the SQL Compare returns a random number of differences relating to the order of columns in the Index Include clause, each time it is run.

Just by hitting "Refresh Comparison" I have got 23,6, 40, 30 differences from the same database compared against itself, without changing any of the SQL Compare options.

david connell

Hi Paul,
Thank you for your additional information. We wil investigate this issue here. This problem will be fixed in a future release.
Regards
David

NationwidePaul

Any update on this problem Please ?

We are comparing a large datawarehouse with 10oos of tables and this fault with SQL compare makes the tool of little use to us.

Brian Donahue

Hello,

NationwidePaul, I'm about to send you a fix for this. Please let me know if you don't get it.