Tablediff using -strict option complains about different schema. But when scripting the schema's and doing a file compare it's equal.

Tim Braes 0 Reputation points
2024-04-30T00:17:51.54+00:00

Dear,

When using tablediff -strict mode, I encounter a strange to me situation.

When generating the export like this for the duplicate table:
$TableExportoptions = New-DbaScriptingOption

$TableExportoptions.ClusteredIndexes = $true

$TableExportoptions.Default = $true

$TableExportoptions.FullTextIndexes = $true

$TableExportoptions.Indexes = $true

$TableExportoptions.NonClusteredIndexes = $true

$TableExportoptions.SchemaQualify = $true

$TableExportoptions.ScriptSchema = $true

$TableExportoptions.ColumnStoreIndexes = $true

$TableExportoptions.Statistics = $true

$TableExportoptions.Triggers = $true

$TableExportoptions.WithDependencies = $true

$TableExportoptions.DriAll = $true

-> Tablediff -strict says the schema's are different and it cannot continue it's effort.

When I choose to omit the indexes in the export settings:

$TableExportoptions = New-DbaScriptingOption

$TableExportoptions.ClusteredIndexes = $true

$TableExportoptions.Default = $true

$TableExportoptions.FullTextIndexes = $true

$TableExportoptions.Indexes = $FALSE

$TableExportoptions.NonClusteredIndexes = $FALSE

$TableExportoptions.SchemaQualify = $true

$TableExportoptions.ScriptSchema = $true

$TableExportoptions.ColumnStoreIndexes = $true

$TableExportoptions.Statistics = $true

$TableExportoptions.Triggers = $true

$TableExportoptions.WithDependencies = $true

$TableExportoptions.DriAll = $true

-> Tablediff -strict is happy

Is it possible to understand what exactly is being checked?

And more importantly what causes it to say it's not the same schema?

I've used TableDiff version 2019 and 2022.

If somebody can get in touch with me, would be appreciated.

Many thanks,

Tim

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,957 questions
0 comments No comments
{count} votes

2 answers

Sort by: Most helpful
  1. MikeyQiaoMSFT-0444 3,190 Reputation points
    2024-04-30T03:09:53.1266667+00:00

    Hi,Tim Braes

    #$TableExportoptions.Indexes = $true #$TableExportoptions.NonClusteredIndexes = $true

    Even if you comment out these two lines, the default value will still be executed at runtime. Judging from the results of two runs, the default value should be True.

    Comparing the results of the two runs, the two tables should differ in their indexes. The check will only pass when the number of indexes, index types (clustered, non-clustered, columnstore, etc.), the columns included in the indexes and their order, and other index properties (such as uniqueness, fill factor, filter expressions, etc.) are all the same.

    You can manually check the index differences between two tables using T-SQL:

    USE [TestDatabase]
    SELECT i.name AS IndexName, 
    OBJECT_NAME(i.object_id) AS TableName, 
    COL_NAME(ic.object_id, ic.column_id) AS ColumnName, 
    i.type_desc AS IndexType,
    ic.index_column_id AS IndexColumnId,
    i.is_primary_key AS IsPrimaryKey, 
    i.is_unique AS IsUnique, 
    i.is_unique_constraint AS IsUniqueConstraint 
    FROM sys.indexes i INNER JOIN sys.index_columns ic 
    ON i.object_id = ic.object_id AND i.index_id = ic.index_id
    WHERE i.object_id = OBJECT_ID('[dbo].[TABLENAME]') ORDER BY i.name, ic.index_column_id;
    

    or analyze the differences through a redirected log file.:

    tablediff -strict > C:\Logs\analysis.log
    
    
    
    

    Best regards,

    Mikey Qiao


    If the answer is the right solution, please click "Accept Answer" and kindly upvote it.


  2. Erland Sommarskog 112.7K Reputation points MVP
    2024-05-01T21:36:24.86+00:00

    The /strict option appears to be broken.

    I have a script to create a database. I run this script on a instance running SQL 2022, and then I modify the script to create a second database on the same instance. Then I run:

    "C:\Program Files\Microsoft SQL Server\160\COM\tablediff.exe" /strict -sourceserver .\TJUTVÅ -sourcedatabase NorthDynamic2 -sourcetable Orders -destinationserver .\TJUTVÅ -destinationdatabase NorthDynamic -destinationtable Orders
    

    And I am told that

    Table [NorthDynamic2].[dbo].[Orders] on .\TJUTVÅ and Table [NorthDynamic].[dbo].[Orders] on .\TJUTVÅ have different schemas and cannot be compared.

    Using Profiler, I can see that tablediff submits:

     SET FMTONLY OFF; SET NO_BROWSETABLE ON; SET FMTONLY ON;SELECT * FROM [dbo].[Orders] WITH (READUNCOMMITTED) WHERE 1=2 SET FMTONLY OFF; SET NO_BROWSETABLE OFF;
    

    It uses SET FMTONLY ON to deduce the schema - but there are better methods to do this. But even then, it should get the same result - but maybe SQL Server is pulling its legs.


Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.