Data comparison fails when table primary key is oh hierarchyId type

myoldhouse

I am performing a comparison of the data on tables with a hierarchyId  column as the primary key, but SQL Data compare is unable to match any row based on the value of the primary key, so, after the comparison has finished,  all rows with the same primary key in both tables are displayed as existing both in source only and in target only. Is this a bug? I am using SQL Data Compare 14.5

Thanks in advance

Jessica R

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

​

myoldhouse

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.

Jessica R

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!