Activity overview
Latest activity by 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. / comments
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 i...
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? / comments
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?
Upon further testing, the clustered index does not need to be unique, just identical to the primary key ( in this case the ID column). / comments
Upon further testing, the clustered index does not need to be unique, just identical to the primary key ( in this case the ID column).
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!!!!!!! / comments
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 ...
I am using 8.0.2.5. Is that what you used? / comments
I am using 8.0.2.5. Is that what you used?
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 / comments
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 o...
Unnecessary manual setting of comparison key - Data compare
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.