Unnecessary manual setting of comparison key - Data compare

• 
  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.

  Chris Auckland February 01, 2010 07:38

  - 0 +
• 
  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

  BruceBau February 02, 2010 11:25

  - 0 +
• 
  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?

  Chris Auckland February 02, 2010 14:15

  - 0 +
• 
  I am using 8.0.2.5. Is that what you used?

  BruceBau February 02, 2010 14:29

  - 0 +
• 
  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 February 02, 2010 18:25

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

  BruceBau February 03, 2010 11:58

  - 0 +
• 
  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.

  Chris Auckland February 04, 2010 11:59

  - 0 +
• 
  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?

  BruceBau February 04, 2010 14:42

  - 0 +
• 
  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.

  Chris Auckland February 08, 2010 08:19

  - 0 +
• 
  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.

  BruceBau February 09, 2010 10:37

  - 0 +

Add comment

Please sign in to leave a comment.