SQLServer – TableDiff – Comparing limited columns

Background

In our previous post, we discussed using TableDiff against a simple table.

Let us modify our table structure by adding columns that track the person adding the data and when it was added.

 

LAB

DDL

 


set noexec off
go

if db_id('DBLAB') is null
begin

    exec('create database [DBLAB]')

end
go

use [DBLAB]
go

if schema_Id('Academia') is null
begin

    exec('create schema [Academia] authorization [dbo]')

end
go

if object_id('[Academia].[courseLoggingLegacy]') is not null
begin

    set noexec on

end

create table [Academia].[courseLoggingLegacy]
(

      [courseID] nvarchar(20)
    , [courseName] nvarchar(100)

    , [addedBy] sysname not null
        constraint defaultcourseLoggingLegacyAddedBy default system_user

    , [addedOn] sysname not null
        constraint defaultcourseLoggingLegacyAddedOn default  SYSUTCDATETIME()

    , constraint PK_Academia_courseLoggingLegacy
        primary key
        (
            [courseID]
        )

    , constraint unique_Academia_courseLoggingLegacy_CourseName
        unique
        (
            [courseName]
        )

)
go

set noexec off
go

if object_id('[Academia].[courseLoggingRevised]') is not null
begin

    set noexec on

end

create table [Academia].[courseLoggingRevised]
(
      [courseID] nvarchar(20)
    , [courseName] nvarchar(100)

    , [addedBy] sysname not null
        constraint defaultcourseLoggingRevisedAddedBy default system_user

    , [addedOn] sysname not null
        constraint defaultcourseLoggingRevisedAddedOn default  SYSUTCDATETIME()

    , constraint PK_Academia_courseLoggingRevised
        primary key
        (
            [courseID]
        )

    , constraint unique_Academia_courseLoggingRevised_CourseName
        unique
        (
            [courseName]
        )
)
go

set noexec off
go

 

 

DML

Let us add data.  Notice that we will not explicitly add data in our audit columns; as we will let the system handle that.  We will also wait a second between adding data to the two tables; in doing so we will ensure that the dateAdded column is a bit off.


set nocount on
go

truncate table [Academia].[courseLoggingLegacy]
go

insert into [Academia].[courseLoggingLegacy]
([courseID], [courseName])
values
 ( 'BIO 1036', 'Biology of the Cell')
 ,( 'BIO 2020', 'Microbiology')
 ,( 'BIO 2110', 'Anatomy and Physiology I')
 ,( 'CTA 1102', 'Human Communication')
 ,( 'DGN 1101', 'The Responsible Self')
 ,( 'DGN 1102', 'And Dignity for All')
 ,( 'EGN 1110', 'ENG 1110')
 ,( 'NSG 2200', 'ENG 2200')
 ,( 'NSG 3200', 'Holistic Approach/Hlth Transis')
 ,( 'NSG 3225', 'Hlth Transitions - Holistic')
 ,( 'NSG 3300', 'Holistic Nursing Approaches')
 ,( 'NSG 3325', 'Acute and Chronic Illness')
 ,( 'NSG 3335', 'Evidence Based Practice')
 ,( 'NSG 4200', 'Holistic NSG Approaches')
 ,( 'NSG 4225', 'Multi-System Illness - Holistic Nursing Interventions')
 ,( 'NSG 4235', 'Public Health Nursing')
 ,( 'NSG 4240', 'Community as Client')
 ,( 'NSG 4343', 'NSG Leadership & Management')
 ,( 'NSG 4500', 'Transition into Professional Nursing Practice')
 ,( 'PSY 2208', 'Lifespan Developmental PSY')
 ,( 'PSY 3341', 'Introduction to Counseling')
 ,( 'SOC 2433', 'The Family and Society')
go

/*
 wait 1 seconds to ensure that that the time entered can be off by 1 seconds
*/
WAITFOR DELAY '00:00:01';
go

truncate table [Academia].[courseLoggingRevised]
go

insert into [Academia].[courseLoggingRevised]
([courseID], [courseName])
select [courseID], [courseName]
from [Academia].[courseLoggingLegacy]

Compare Data

Let us compare data using tablediff; keep in mind that this is done via the command line.

Here is the OS Script for doing so:

 


rem db server is loal named instance SQLEXPRESS_V2014
set DBSERVER=.\SQLEXPRESS_V2014

rem db name is DBLAB
set DBNAME=DBLAB

set sourceTable=courseLoggingLegacy

set destinationTable=courseLoggingRevised

rem write DDL Changes to bring revised table inline
set TransactFileDMLChanges=courseLoggingRevisedDMLChanges.sql

rem version specific path to tablediff
set TableDiffPath=C:\Program Files\Microsoft SQL Server\120\COM\

rem if file exist, remove it
IF EXIST %TransactFileDMLChanges% del /F %TransactFileDMLChanges%

rem tablediff
"%TableDiffPath%\tablediff.exe" -sourceserver %DBSERVER% -sourcedatabase %DBNAME% -sourceTable %sourceTable% -sourceschema Academia -destinationserver %DBSERVER%  -destinationdatabase %DBNAME% -destinationTable %destinationTable% -destinationschema Academia -f %TransactFileDMLChanges%

 

And, here is the console output:


User-specified agent parameter values:
-sourceserver .\SQLEXPRESS_V2014
-sourcedatabase DBLAB
-sourceTable courseLoggingLegacy
-sourceschema Academia
-destinationserver .\SQLEXPRESS_V2014
-destinationdatabase DBLAB
-destinationTable courseLoggingRevised
-destinationschema Academia
-f courseLoggingRevisedDMLChanges.sql

Table [DBLAB].[Academia].[courseLoggingLegacy] on .\SQLEXPRESS_V2014 and Table [
DBLAB].[Academia].[courseLoggingRevised] on .\SQLEXPRESS_V2014 have 22 differenc
es.
Fix SQL written to courseLoggingRevisedDMLChanges.sql.
Err     courseID        Col
Mismatch        N'BIO 1036'     addedOn
Mismatch        N'BIO 2020'     addedOn
Mismatch        N'BIO 2110'     addedOn
Mismatch        N'CTA 1102'     addedOn
Mismatch        N'DGN 1101'     addedOn
Mismatch        N'DGN 1102'     addedOn
Mismatch        N'EGN 1110'     addedOn
Mismatch        N'NSG 2200'     addedOn
Mismatch        N'NSG 3200'     addedOn
Mismatch        N'NSG 3225'     addedOn
Mismatch        N'NSG 3300'     addedOn
Mismatch        N'NSG 3325'     addedOn
Mismatch        N'NSG 3335'     addedOn
Mismatch        N'NSG 4200'     addedOn
Mismatch        N'NSG 4225'     addedOn
Mismatch        N'NSG 4235'     addedOn
Mismatch        N'NSG 4240'     addedOn
Mismatch        N'NSG 4343'     addedOn
Mismatch        N'NSG 4500'     addedOn
Mismatch        N'PSY 2208'     addedOn
Mismatch        N'PSY 3341'     addedOn
Mismatch        N'SOC 2433'     addedOn
The requested operation took 0.2370136 seconds.

 

We can see from the output above that just about all our entries are marked off courtesy of the addedOn column.

 

Correct

Let us correct by creating views and thus explicitly listing which columns to target.

 

DDL – Create view – Using isNull to “decorate” primary key

Let us create our views.  We are keeping a couple of things in mind:

  • We will only reference the core data
  • We will also use isNull around our primary columns to help SQL know that they are such; unfortunately one is unable to specify primary or unique columns via traditional create/alter view syntax

 


if db_id('DBLAB') is null
begin

    exec('create database [DBLAB]')

end
go

use [DBLAB]
go

if schema_Id('Academia') is null
begin

    exec('create schema [Academia] authorization [dbo]')

end
go

set noexec off
go

if object_id('[Academia].[v_courseLoggingLegacy]') is null
begin

    exec('create view [Academia].[v_courseLoggingLegacy] as select 1/0 as [select] ');

end
go

alter view [Academia].[v_courseLoggingLegacy]
as

    select
              isNull([courseID], -1) as courseID
            , [courseName]

    from  [Academia].[courseLoggingLegacy]

go

if object_id('[Academia].[v_courseLoggingRevised]') is null
begin

    exec('create view [Academia].[v_courseLoggingRevised] as select 1/0 as [select] ');

end
go

alter view [Academia].[v_courseLoggingRevised]
as

    select
              isNull([courseID], 1) as [courseID]
            , [courseName]

    from  [Academia].[courseLoggingRevised]

go

 

OS Script

 


rem db server is loal named instance SQLEXPRESS_V2014
set DBSERVER=.\SQLEXPRESS_V2014

rem db name is DBLAB
set DBNAME=DBLAB

set sourceTable=v_courseLoggingLegacy

set destinationTable=v_courseLoggingRevised

rem write DDL Changes to bring revised table inline
set TransactFileDMLChanges=courseLoggingRevisedDMLChanges.sql

rem version specific path to tablediff
set TableDiffPath=C:\Program Files\Microsoft SQL Server\120\COM\

rem if file exist, remove it
IF EXIST %TransactFileDMLChanges% del /F %TransactFileDMLChanges%

rem tablediff
"%TableDiffPath%\tablediff.exe" -sourceserver %DBSERVER% -sourcedatabase %DBNAME% -sourceTable %sourceTable% -sourceschema Academia -destinationserver %DBSERVER%  -destinationdatabase %DBNAME% -destinationTable %destinationTable% -destinationschema Academia -f %TransactFileDMLChanges%

 

Output:


Microsoft (R) SQL Server Replication Diff Tool
Copyright (c) 2014 Microsoft Corporation

User-specified agent parameter values:
-sourceserver .\SQLEXPRESS_V2014
-sourcedatabase DBLAB
-sourceTable v_courseLoggingLegacy
-sourceschema Academia
-destinationserver .\SQLEXPRESS_V2014
-destinationdatabase DBLAB
-destinationTable v_courseLoggingRevised
-destinationschema Academia
-f courseLoggingRevisedDMLChanges.sql

The replication table difference tool requires the comparison tables/views to have either a primary key, identity, rowguid or unique key column.

 

Unfortunately, we are getting an error:


The replication table difference tool requires the comparison tables/views to have either a primary key, identity, rowguid or unique key column.

 

 

 

DDL – Create view – Using Indexed View

Let us create our views.  We are keeping a couple of things in mind:

  • We will reference only the core data
  • We will also create a unique clustered index on our views using schema binding and create unique clustered index

 


if db_id('DBLAB') is null
begin

    exec('create database [DBLAB]')

end
go

use [DBLAB]
go

if schema_Id('Academia') is null
begin

    exec('create schema [Academia] authorization [dbo]')

end
go

set noexec off
go

if object_id('[Academia].[v_courseLoggingLegacy]') is null
begin

    exec('create view [Academia].[v_courseLoggingLegacy] as select 1/0 as [select] ');

end
go

alter view [Academia].[v_courseLoggingLegacy]
with schemabinding
as

    select
              isNull(courseID, -1) as [courseID]
            , [courseName]

    from  [Academia].[courseLoggingLegacy]

go

create unique clustered index idx_CourseID
on [Academia].[v_courseLoggingLegacy]
(
    [courseID]
)
go

if object_id('[Academia].[v_courseLoggingRevised]') is null
begin

    exec('create view [Academia].[v_courseLoggingRevised] as select 1/0 as [select] ');

end
go

alter view [Academia].[v_courseLoggingRevised]
with schemabinding
as

    select
              isNull(courseID, -1) as [courseID]
            , [courseName]

    from  [Academia].[courseLoggingRevised]

go

create unique clustered index idx_CourseID
on [Academia].[v_courseLoggingRevised]
(
    [courseID]
)
go

 

When we tried running tableDiff again, we got the same problem.

 


if db_id('DBLAB') is null
begin

    exec('create database [DBLAB]')

end
go

use [DBLAB]
go

if schema_Id('Academia') is null
begin

    exec('create schema [Academia] authorization [dbo]')

end
go

set noexec off
go

if object_id('[Academia].[v_courseLoggingLegacy]') is null
begin

    exec('create view [Academia].[v_courseLoggingLegacy] as select 1/0 as [select] ');

end
go

alter view [Academia].[v_courseLoggingLegacy]
as

    select
              isNull([courseID], -1) as courseID
            , [courseName]

    from  [Academia].[courseLoggingLegacy]

go

if object_id('[Academia].[v_courseLoggingRevised]') is null
begin

    exec('create view [Academia].[v_courseLoggingRevised] as select 1/0 as [select] ');

end
go

alter view [Academia].[v_courseLoggingRevised]
as

    select
              isNull([courseID], 1) as [courseID]
            , [courseName]

    from  [Academia].[courseLoggingRevised]

go

 

 

DDL – Create view – Get rid of isNull for determining primary key

Let us create our views.  We are keeping a couple of things in mind:

  • We will only reference the core data
  • And, get rid of isNull

 


if db_id('DBLAB') is null
begin

    exec('create database [DBLAB]')

end
go

use [DBLAB]
go

if schema_Id('Academia') is null
begin

    exec('create schema [Academia] authorization [dbo]')

end
go

set noexec off
go

if object_id('[Academia].[v_courseLoggingLegacy]') is null
begin

    exec('create view [Academia].[v_courseLoggingLegacy] as select 1/0 as [select] ');

end
go

alter view [Academia].[v_courseLoggingLegacy]
as

    select
              [courseID]
            , [courseName]

    from  [Academia].[courseLoggingLegacy]

go

if object_id('[Academia].[v_courseLoggingRevised]') is null
begin

    exec('create view [Academia].[v_courseLoggingRevised] as select 1/0 as [select] ');

end
go

alter view [Academia].[v_courseLoggingRevised]
as

    select
              [courseID]
            , [courseName]

    from  [Academia].[courseLoggingRevised]

go

 

Went back and re-ran our tablediff script.

Output:


Microsoft (R) SQL Server Replication Diff Tool
Copyright (c) 2014 Microsoft Corporation

User-specified agent parameter values:
-sourceserver .\SQLEXPRESS_V2014
-sourcedatabase DBLAB
-sourceTable v_courseLoggingLegacy
-sourceschema Academia
-destinationserver .\SQLEXPRESS_V2014
-destinationdatabase DBLAB
-destinationTable v_courseLoggingRevised
-destinationschema Academia
-f courseLoggingRevisedDMLChanges.sql

Table [DBLAB].[Academia].[v_courseLoggingLegacy] on .\SQLEXPRESS_V2014 and Tabl
 [DBLAB].[Academia].[v_courseLoggingRevised] on .\SQLEXPRESS_V2014 are identica
.
The requested operation took 0.1710093 seconds.

 

And, now we are good.

 

Conclusion

Unfortunately, Microsoft does not have a formal way of defining primary keys on views. And, so many ways have come forth for different needs.

For Entity Framework, in some cases, isNull is the good road.

For other tooling, indexed views might be more appropriate.

For our case, we did not have to do anything on the views; just ensure that our base table have primary keys.

 

Hints

SystemDateTime

 

Listening

Again, if not for all these guys who toil on the Net answering Q/A on http://stackoverflow.com/ and the many others, I will be long gone from this business.

So here is for them.

George Harrison – If Not For You – Lyrics

No other words will do:

If not for you
I couldn’t even find the door
I couldn’t even see the floor
I’d be sad and blue, if not for you

If not for you
The night would see me wide awake
The day would surely have to break
It would not be new, if not for you

If not for you, my sky would fall
Rain would gather, too
Without your love I’d be nowhere at all
I’d be lost, if not for you

If not for you
The winter would hold no spring
I just wouldn’t have a clue, if not for you

 

References

 

Updatable Programmable Objects

Updatable Programmable Objects (Fucntions – Table Value Functions)

 

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s