Unnecessary manual setting of comparison key - Data compare

BruceBau

Data compare requires me to set comparison key manually when clustered index in the two tables is different even though both tables have identical primary key.

Chris Auckland

Thanks for your post.

Would you be able to show me the table structure for one of these affected objects?

As far as I'm aware, a primary key is a clustered index in SQL Server and you can only have one clustered index on a table.

BruceBau

It is not an Sql Server requirement that the primary key be the clustered index, and indeed there may be very good reasons for the clustered index to be something other than the primary key. The obvious solution for us is to make the clustered index for the two compared tables to be the same (along with the primary key) but there are timing considerations that do not allow us to do this now. It would seem that since the primary key makes each record unique, that that would be all you would need. I can see that having the two tables have the same physical organization ( clustered index) would make the comapre easier to do, but setting the primary key manually in the compare operation does not prevent the process from comparing successfully. Here is a script where the primary key is nonclustered.

USE [AtlasSEA]
GO

/****** Object: Table [dbo].[TrevorTEST] Script Date: 02/02/2010 08:14:23 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[TrevorTEST](
[ID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[TrevorTypeID] [int] NOT NULL,
[Name] [varchar](100) NOT NULL,
[SortName] [varchar](100) NOT NULL,
CONSTRAINT [piTrevor] PRIMARY KEY NONCLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [AtlasDB]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

Chris Auckland

Yes, you're correct, thanks for putting me right.

However, I still can't reproduce this issue.

I added a clustered index to both the source and target object, but the automatic mapping using the primary key (piTrevor) still ocurred.

I added:
Source:
CREATE UNIQUE CLUSTERED INDEX [Test_s] ON [dbo].[TrevorTEST] ([ID], [TrevorTypeID])
Target:
CREATE UNIQUE CLUSTERED INDEX [Test_t] ON [dbo].[TrevorTEST] ([ID], [Name], [SortName])

Which exact version of SQL Data Compare 8 are you using?

BruceBau

I am using 8.0.2.5. Is that what you used?

BruceBau

I think we have identified the problem. Thanks for eliminating a red herring! With further testing we have discovered that if we have a primary key AND a clustered index which are both identical and unique, it causes the compare to force setting the compare key manually. I have no idea why we did this, but now we know what to fix. You can test this in the sample tables by making the clustered indexes unique and identical to the primary key.


Thanks again for your help!!!!!!!

BruceBau

Upon further testing, the clustered index does not need to be unique, just identical to the primary key ( in this case the ID column).

Chris Auckland

I can reproduce this now, thanks for the info.

It seems that the difference in the index makes it hard for SDC to decide what to use. If you sync the two schemas using SQL Compare first, then the tables will be automatically mapped in SQL Data Compare.

BruceBau

I understand. It just seems that if the software sees that two tables have identical primary keys, that should be enough. Why would it look at anything else?

Chris Auckland

I'm not really sure. A matching PK should be enough.

I would imagine that this situation wasn't tested for, so nothing has been added to handle a mismatched index like this.

If it would be sufficient, I can log this as a bug with SDC which can be looked at for the next release.

Let me know what you think.

BruceBau

Since matching primary keys guarantees a unique identifier for each record which can be used for the compare, I would think it would be all that is needed. When I have to do the manual set, this is what I give to Sql compare to use and it seems to be able to do the compare without a problem.