How can we help you today? How can we help you today?

matching things that are obviously not the same

Things that have no correlation to one another shouldn't have the ability to overwrite the item. For example, in the supplied script, there are indexes:

In one database:
create index DefinitelyDifferent on fred(value)

In another:
create index AbsolutelyDifferent on fred(fredId)

By default DefinitelyDifferent will replace AbsolutelyDifferent, which makes no sense:

After stripping out the transaction/temp table code:

PRINT N'Dropping index [AbsolutelyDifferent] from [dbo].[fred]'
GO
DROP INDEX [AbsolutelyDifferent] ON [dbo].[fred]
CREATE NONCLUSTERED INDEX [DefinitelyDifferent] ON [dbo].[fred] ([value])

Nothing destructive should happen by default to the objects in the database. If the names were the same, then changing the index contents by default makes sense. Or if the index had the same contents, then changing the name makes sense. But not completely different ones.
use RedGateCompare1
go
create table fred
(
    fredId int primary key,
    value  varchar(20)
)
go
create index same on fred (value)
go
create index DefinitelyDifferent on fred(value)
go
use RedGateCompare2
go
create table fred
(
    fredId int primary key,
    value  varchar(20)
)
go
create index same on fred (value)
go
create index AbsolutelyDifferent on fred(fredId)
go
[/code]
drsql
0

Comments

6 comments

Sort by
  • JonathanWatts
    Hi Dr,

    What you are describing appears to be wanting SQL Compare to merge two tables, which unfortunately is not supported by the product at the moment.

    SQL Compare will only fully synchronize an object in a particular direction, including any that are displayed with that in the SQL Difference Viewer (indexes and triggers on tables for example). SQL Compare will completely overwrite the target object with the original object, just in a way that preserves database integrity and data in tables (where possible - warning about cases in which it is not possible).

    So in your example, because the first database doesn't have an index called AbsolutelyDifferent, when you synchronize the table to the second database, it drops the AbsolutelyDifferent index in order to make the second database's table exactly the same as the first database's table. Compare doesn't modify the source database, only the target, so the only way to make the tables identical is to drop the 'extra' index on the target database.

    The challenges involved in merging a database have been left to a future release of the product, due to the number of internal changes we were forced to making whilst implementing using scripts as a data source.

    Regards,

    Jonathan
    JonathanWatts
    0
  • drsql
    I see what you think I meant, but all I wanted was to have the totally different items on different lines so I could choose to add the new index and not drop the existing index. When they are on the same line with an arrow between them, it looks like the tool thought they were the same.

    Just like if you have two tables with different names, you don't match them up, I would prefer that the two indexes be clearly denoted as distinct, different things so I could choose to keep both indexes.
    drsql
    0
  • Andras B
    drsql wrote:
    Things that have no correlation to one another shouldn't have the ability to overwrite the item. For example, in the supplied script, there are indexes:
    ...

    This is the expected behaviour in SQL Compare. It synchronizes differences, so it makes the two tables the same. If it means removing indexes, it removes them. If it needs new ones, it adds them. So at the end of the synchronization the two tables will be identical. Adding only indexes could be a problem sometimes, let's say the indexes are named automatically by SQL Server, or the table has different columns.

    Customizing migration at individual index level therefore is not something we have on our immediate roadmap. The only thing that could help if one needs to have customized migration is to go through the generated script and remove index drops that one does not need. SQL Refactor's summarize script could potentially help, but this is a synchronization that would need a human to decide on what needs and what does not need to be migrated.

    Andras
    Andras B
    0
  • drsql
    I guess what confuses me is that if I have two tables fred and bob, the UI doesnt say:
    Fred -->  Bob

    It says
    Fred   --> 
                        Bob  (red)


    So say I add:
    create index AnotherAbsolutelyDifferent on fred(fredId)

    To my RedGateCompare2 database.. This time the result is:
    DefinitelyDifferent  ->  AbsolutelyDifferent
Same                    ->  AnotherAbsolutelyDifferent
                             X   Same

    Now, same actually does match, so this seems to be a real bug, but the results I would have desired would be:
    Same                 Same
DefinitelyDifferent  ->  
                             X    AbsolutelyDifferent
                             X    AnotherAbsolutelyDifferent

    Technically, the net effect is exactly the same, but it is completely clear what is taking place.

    Now, if I could check only the subordinate items I want to ignore individually, that would be super too :) (Should I make another post on that?)
    drsql
    0
  • JonathanWatts
    Hey dr,

    SQL Compare actually carries out two types of comparisons on objects, a semantic comparison, the status of which is displayed in the top grid.

    The SQL Differences Panel, however carries out a straight textual comparison of the objects, we do this for speed of displaying the differences and in 95% of cases textual differences are also semantic differences.

    However, sometimes the difference matching algorithm we use doesn't match things up correctly on indexes, constraints and other objects that are related to tables. This gives us the behaviour you are seeing.

    I agree it is a bug and one that I personally would dearly love to get fixed, but it would mean we would have to make some fairly hefty changes to code that controls both the engine and the SQL Differences Panel, which we cannot sanction at the moment.

    Regards,

    Jonathan
    JonathanWatts
    0
  • drsql
    That actually explains it quite well. I had always wondered why when things were clearly the exact same sometimes it suggested moving them.

    Thanks,

    Louis
    drsql
    0

Add comment

Please sign in to leave a comment.