Data comparison fails when table primary key is oh hierarchyId type

• 
  Hi and thanks for your post @myoldhouse!
  
  Just to confirm, is LocationID the primary key?
  
  Are you able to share the CREATE scripts for the tables in both source and target?
  
  Can you also please double-click on a few LocationID values in both the source and target to see the "Value Details" window and send a screenshot of what's shown for both? (I'm curious if the Value details view may shed some light on what SQL Data Compare finds different. )
  
  ​
  
  

  Jessica R April 05, 2021 16:51

  - 0 +
• 
  Hi Jessica

  
  

  Thanks for your attention. Regarding your questions:

  
  

  Yes, LocationID is the primary key of this table.

  
  

  Create script for source table :

  
  

  SET ANSI_NULLS ON
  GO
  
  SET QUOTED_IDENTIFIER ON
  GO
  

  
  

  CREATE TABLE [dbo].[Locations](
      [LocationID] [hierarchyid] NOT NULL,
      [LocationHierarchyLevel]  AS ([LocationID].[GetLevel]()) PERSISTED,
      [LocationType] [tinyint] NOT NULL,
      [LocationSubtype] [tinyint] NULL,
      [LocationDescription] [varchar](25) NOT NULL,
      [Classification] [int] NULL,
      [Active] [bit] NOT NULL,
      [PropagateActivationSettings] [bit] NOT NULL,
      [OrderBy] [smallint] NOT NULL,
      [ImageCollectionID] [int] NULL,
      [GeographicData] [geography] NULL,
      [ConcurrencyCheck] [timestamp] NOT NULL,
   CONSTRAINT [PK_Locations] PRIMARY KEY CLUSTERED
  (
      [LocationID] ASC
  )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
   CONSTRAINT [IX_Locations_Type] UNIQUE NONCLUSTERED
  (
      [LocationID] ASC,
      [LocationType] ASC
  )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
  ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
  GO
  

  
  

  Create script for target table :

  
  

  SET ANSI_NULLS ON
  GO
  
  SET QUOTED_IDENTIFIER ON
  GO
  
  CREATE TABLE [dbo].[Locations](
      [LocationID] [hierarchyid] NOT NULL,
      [LocationHierarchyLevel]  AS ([LocationID].[GetLevel]()) PERSISTED,
      [LocationType] [tinyint] NOT NULL,
      [LocationSubtype] [tinyint] NULL,
      [LocationDescription] [varchar](25) NOT NULL,
      [Classification] [int] NULL,
      [Active] [bit] NOT NULL,
      [PropagateActivationSettings] [bit] NOT NULL,
      [OrderBy] [smallint] NOT NULL,
      [ImageCollectionID] [int] NULL,
      [GeographicData] [geography] NULL,
      [ConcurrencyCheck] [timestamp] NOT NULL,
   CONSTRAINT [PK_Locations] PRIMARY KEY CLUSTERED
  (
      [LocationID] ASC
  )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
   CONSTRAINT [IX_Locations_Type] UNIQUE NONCLUSTERED
  (
      [LocationID] ASC,
      [LocationType] ASC
  )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
  ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
  GO

  
  

  Screenshots with Value Details for records with id '/5/2/' in both tables. As you can see, SQL Data Compare is not matching these records but both have the same id:

  
  

  

  
  

  I performed a comparison using the following queries in Sql server management studio:

  
  

  SELECT COUNT(*) FROM caromil_v2_prod.dbo.locations [source] LEFT JOIN
  caromil_v2dev.dbo.Locations [target] ON [source].locationId = [target].LocationID
  WHERE [target].LocationID IS NULL
  
  SELECT COUNT(*) FROM caromil_v2_prod.dbo.locations [source] RIGHT JOIN
  caromil_v2dev.dbo.Locations [target] ON [source].locationId = [target].LocationID
  WHERE [source].LocationID IS NULL
  
  SELECT COUNT(*) FROM caromil_v2_prod.dbo.locations [source] INNER JOIN
  caromil_v2dev.dbo.Locations [target] ON [source].locationId = [target].LocationID WHERE [source].Classification <> [target].Classification
  
  SELECT COUNT(*) FROM caromil_v2_prod.dbo.locations [source] INNER JOIN
  caromil_v2dev.dbo.Locations [target] ON [source].locationId = [target].LocationID WHERE [source].Classification = [target].Classification

  
  

  Obtaining the following results:

  Only in source (query 1):172

  Only in target (query 2):1

  In both but different:2

  Identical:418

  
  

  Running the comparison with SQL Data Compare, the result is the following:

  Only in source (query 1):920

  Only in target (query 2):749

  In both but different:3

  Identical:440

  
  

  I will really appreciate your help.
  

  
  
  
  
  
  
  
  
  
  
  

  myoldhouse May 06, 2021 01:11

  - 0 +
• 
  Thank you!
  
  I haven't been able to reproduce this yet, unfortunately. Can you please send a screenshot of how you have the comparison key set for the Location table? (From Edit Project>Tables and Views)
  
  And I don't think this is contributing to the issue as I don't see any whitespace highlighted, but can you just confirm for me if enabling "Trim trailing whitespace" comparison option has any effect? Thank you!
  
  

  Jessica R May 12, 2021 00:23

  - 0 +

Add comment

Please sign in to leave a comment.