What are the challenges you face when working across database platforms? Take the survey
Options

Data comparison fails when table primary key is oh hierarchyId type

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


Tagged:

Answers

  • Options
    Jessica RJessica R Posts: 1,319 Rose Gold 4
    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 Ramos | Product Support Engineer | Redgate Software

    Have you visited our Help Center?


  • Options
    myoldhousemyoldhouse Posts: 2 Bronze 1
    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.










  • Options
    Jessica RJessica R Posts: 1,319 Rose Gold 4
    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 Ramos | Product Support Engineer | Redgate Software

    Have you visited our Help Center?


Sign In or Register to comment.