Constraints and Indexes order

LeeCLeeC Posts: 12
edited August 14, 2006 11:03AM in SQL Compare Previous Versions
How can I make my comparisons ignore the order of table "Constraints and Indexes"? I get false mismatches due to index ordering.

Thanks,
Lee
· Share on Twitter

Comments

  • Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    Hi Lee,

    Are you sure that this is the case? SQL Compare doesn't concern itself with the order of indexes, keys and constraints.
    · Share on Twitter
  • LeeCLeeC Posts: 12
    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])

    sqlcompare.jpg
    · Share on Twitter
  • LeeCLeeC Posts: 12
    It's been a month now. I took the time to prove the issue. The courtesy of a reply would be appreciated.
    · Share on Twitter
  • david connelldavid connell Posts: 172 Silver 1
    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
    · Share on Twitter
  • LeeCLeeC Posts: 12
    Thanks, David. I keep running into this issue. It causes a lot of false positives for me. I look forward to a solution. :)

    , Lee
    · Share on Twitter
  • david connelldavid connell Posts: 172 Silver 1
    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
    · Share on Twitter
  • LeeCLeeC Posts: 12
    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
    · Share on Twitter
  • david connelldavid connell Posts: 172 Silver 1
    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
    · Share on Twitter
  • LeeCLeeC Posts: 12
    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
    · Share on Twitter
  • NationwidePaulNationwidePaul Posts: 3
    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
    · Share on Twitter
  • NationwidePaulNationwidePaul Posts: 3
    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.
    · Share on Twitter
  • david connelldavid connell Posts: 172 Silver 1
    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
    · Share on Twitter
  • NationwidePaulNationwidePaul Posts: 3
    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.
    · Share on Twitter
  • Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    Hello,

    NationwidePaul, I'm about to send you a fix for this. Please let me know if you don't get it.
    · Share on Twitter
This discussion has been closed.