Query detailing differences between rows for a large amount of data

Question

I have a number of large tables, each with >300 columns. The application I am using creates "archives" of changed rows by making a copy of the current row in a secondary table.

Consider a trivial example:

CREATE TABLE dbo.bigtable
(
  UpdateDate datetime,
  PK varchar(12) PRIMARY KEY,
  col1 varchar(100),
  col2 int,
  col3 varchar(20),
  .
  .
  .
  colN datetime
);

Archive table:

CREATE TABLE dbo.bigtable_archive
(
  UpdateDate datetime,
  PK varchar(12) NOT NULL,
  col1 varchar(100),
  col2 int,
  col3 varchar(20),
  .
  .
  .
  colN datetime
);

Before any updates are executed on dbo.bigtable, a copy of the row is created in dbo.bigtable_archive, then dbo.bigtable.UpdateDate is updated with the current date.

Therefore UNIONing the two tables together & grouping by PK creates a timeline of changes, when ordered by UpdateDate.

I wish to create a report detailing the differences between rows, ordered by UpdateDate, grouped by PK, in the following format:

PK,   UpdateDate,  ColumnName,  Old Value,   New Value

Old Value and New Value can be the relevant columns cast to a VARCHAR(MAX) (there are no TEXT or BYTE columns involved), as I do not need to do any post-processing of the values themselves.

At the moment I can't think of a sane way of doing this for a large amount of columns, without resorting to generating the queries programmatically - I may have to do this.

Open to lots of ideas, so I'll add a bounty to the question after 2 days.

Andriy M · Accepted Answer · 2017-06-02 11:27:06Z

This is not going to look pretty, especially given the more than 300 columns and unavailability of LAG, nor is it likely to perform exceedingly well, but just as something to start with, I would try the following approach:

UNION the two tables.
For each PK in the combined set, get its previous "incarnation" from the archive table (the implementation below uses OUTER APPLY + TOP (1) as a poor man's LAG).
Cast each data column to varchar(max) and unpivot them in pairs, i.e. the current and the previous value (CROSS APPLY (VALUES ...) works well for this operation).
Finally, filter the results based on whether the values in each pair differ from each other.

The Transact-SQL of the above as I see it:

WITH
  Combined AS
  (
    SELECT * FROM dbo.bigtable
    UNION ALL
    SELECT * FROM dbo.bigtable_archive
  ) AS derived,
  OldAndNew AS
  (
    SELECT
      this.*,
      OldCol1 = last.Col1,
      OldCol2 = last.Col2,
      ...
    FROM
      Combined AS this
      OUTER APPLY
      (
        SELECT TOP (1)
          *
        FROM
          dbo.bigtable_archive
        WHERE
          PK = this.PK
          AND UpdateDate < this.UpdateDate
        ORDER BY
          UpdateDate DESC
      ) AS last
  )
SELECT
  t.PK,
  t.UpdateDate,
  x.ColumnName,
  x.OldValue,
  x.NewValue
FROM
  OldAndNew AS t
  CROSS APPLY
  (
    VALUES
    ('Col1', CAST(t.OldCol1 AS varchar(max), CAST(t.Col1 AS varchar(max))),
    ('Col2', CAST(t.OldCol2 AS varchar(max), CAST(t.Col2 AS varchar(max))),
    ...
  ) AS x (ColumnName, OldValue, NewValue)
WHERE
  NOT EXISTS (SELECT x.OldValue INTERSECT x.NewValue)
ORDER BY
  t.PK,
  t.UpdateDate,
  x.ColumnName
;

Paul White · Accepted Answer · 2018-11-19 14:09:16Z

If you unpivot the data to a temp table

create table #T
(
  PK varchar(12) not null,
  UpdateDate datetime not null,
  ColumnName nvarchar(128) not null,
  Value varchar(max),
  Version int not null
);

You could match the rows to find new and old value with a self join on PK, ColumnName and Version = Version + 1.

The not so pretty part is, of course, doing the unpivot of your 300 columns into the temp table from the two base tables.

XML to the rescue to make things less awkward.

It is possible to unpivot data with XML without having to know what actual columns there are in the table that will be unpivoted. The column names must be valid as element names in XML or it will fail.

The idea is to create one XML for each row having all the values for that row.

select bt.PK,
       bt.UpdateDate,
       (select bt.* for xml path(''), elements xsinil, type) as X
from dbo.bigtable as bt;

<UpdateDate xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">2001-01-03T00:00:00</UpdateDate>
<PK xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">PK1</PK>
<col1 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">c1_1_3</col1>
<col2 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">3</col2>
<col3 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:nil="true" />
<colN xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">2001-01-03T00:00:00</colN>

elements xsinil is there to create elements for columns with NULL.

The XML can then be shredded using nodes('*') to get one row for each column and use local-name(.) to the get element name and text() to get the value.

  select C1.PK,
         C1.UpdateDate,
         T.X.value('local-name(.)', 'nvarchar(128)') as ColumnName,
         T.X.value('text()[1]', 'varchar(max)') as Value
  from C1
    cross apply C1.X.nodes('row/*') as T(X)

Full solution below. Note that Version is reversed. 0 = Last version.

create table #X
(
  PK varchar(12) not null,
  UpdateDate datetime not null,
  Version int not null,
  RowData xml not null
);

create table #T
(
  PK varchar(12) not null,
  UpdateDate datetime not null,
  ColumnName nvarchar(128) not null,
  Value varchar(max),
  Version int not null
);


insert into #X(PK, UpdateDate, Version, RowData)
select bt.PK,
       bt.UpdateDate,
       0,
       (select bt.* for xml path(''), elements xsinil, type)
from dbo.bigtable as bt
union all
select bt.PK,
       bt.UpdateDate,
       row_number() over(partition by bt.PK order by bt.UpdateDate desc),
       (select bt.* for xml path(''), elements xsinil, type)
from dbo.bigtable_archive as bt;

with C as 
(
  select X.PK,
         X.UpdateDate,
         X.Version,
         T.C.value('local-name(.)', 'nvarchar(128)') as ColumnName,
         T.C.value('text()[1]', 'varchar(max)') as Value
  from #X as X
    cross apply X.RowData.nodes('*') as T(C)
)
insert into #T (PK, UpdateDate, ColumnName, Value, Version)
select C.PK,
       C.UpdateDate,
       C.ColumnName,
       C.Value,
       C.Version
from C 
where C.ColumnName not in (N'PK', N'UpdateDate');

/*
option (querytraceon 8649);

The above query might need some trick to go parallel.
For the testdata I had on my machine exection time is 16 seconds vs 2 seconds
https://sqlkiwi.blogspot.com/2011/12/forcing-a-parallel-query-execution-plan.html
http://dataeducation.com/next-level-parallel-plan-forcing-an-alternative-to-8649/

*/

select New.PK,
       New.UpdateDate,
       New.ColumnName,
       Old.Value as OldValue,
       New.Value as NewValue
from #T as New
  left outer join #T as Old
    on Old.PK = New.PK and
       Old.ColumnName = New.ColumnName and
       Old.Version = New.Version + 1;

McNets · Accepted Answer · 2017-06-06 09:39:56Z

I'd suggest you another approach.

Although you cannot change current application, may be you could change the database behavior.

If possible, I'd add two TRIGGERS to the current tables.

One INSTEAD OF INSERT on dbo.bigtable_archive that adds the new record only if it doesn't currently exists.

CREATE TRIGGER dbo.IoI_BTA
ON dbo.bigtable_archive
INSTEAD OF INSERT
AS
BEGIN
    IF NOT EXISTs(SELECT 1 
                  FROM dbo.bigtable_archive bta
                  INNER JOIN inserted i
                  ON  bta.PK = i.PK
                  AND bta.UpdateDate = i.UpdateDate)
    BEGIN
        INSERT INTO dbo.bigtable_archive
        SELECT * FROM inserted;
    END
END

And a AFTER INSERT trigger on bigtable that do exactly the same job, but using data of bigtable.

CREATE TRIGGER dbo.IoI_BT
ON dbo.bigtable
AFTER INSERT
AS
BEGIN
    IF NOT EXISTS(SELECT 1 
                  FROM dbo.bigtable_archive bta
                  INNER JOIN inserted i
                  ON  bta.PK = i.PK
                  AND bta.UpdateDate = i.UpdateDate)
    BEGIN
        INSERT INTO dbo.bigtable_archive
        SELECT * FROM inserted;
    END
END

Ok, I've set up an small example here with this initial values:

SELECT * FROM bigtable;
SELECT * FROM bigtable_archive;

UpdateDate          | PK  | col1 | col2 | col3
:------------------ | :-- | :--- | ---: | :---
02/01/2017 00:00:00 | ABC | C3   |    1 | C1  

UpdateDate          | PK  | col1 | col2 | col3
:------------------ | :-- | :--- | ---: | :---
01/01/2017 00:00:00 | ABC | C1   |    1 | C1

Now you should insert into bigtable_archive all pending records from bigtable.

INSERT INTO bigtable_archive
SELECT *
FROM   bigtable
WHERE  UpdateDate >= '20170102';

SELECT * FROM bigtable_archive;
GO

UpdateDate          | PK  | col1 | col2 | col3
:------------------ | :-- | :--- | ---: | :---
01/01/2017 00:00:00 | ABC | C1   |    1 | C1  
02/01/2017 00:00:00 | ABC | C3   |    1 | C1

Now, the next time the application try to insert a record on bigtable_archive table, the triggers will detects if it exists, and the insert will be avoided.

INSERT INTO dbo.bigtable_archive VALUES('20170102', 'ABC', 'C3', 1, 'C1');
GO

SELECT * FROM bigtable_archive;
GO

UpdateDate          | PK  | col1 | col2 | col3
:------------------ | :-- | :--- | ---: | :---
01/01/2017 00:00:00 | ABC | C1   |    1 | C1  
02/01/2017 00:00:00 | ABC | C3   |    1 | C1

Obviously now you can get the timeline of changes by querying only the archive table. And the application will never realize that a triggger is quietly doing the job under the covers.

dbfiddle here

markp-fuso · Accepted Answer · 2017-06-10 21:36:49Z

Working proposal, w/ some sample data, can be found @ rextester: bigtable unpivot

The gist of the operation:

1 - Use syscolumns and for xml to dynamically generate our column lists for the unpivot operation; all values will be converted to varchar(max), w/ NULLs being converted to the string 'NULL' (this addresses issue with unpivot skipping NULL values)

2 - Generate a dynamic query to unpivot data into the #columns temp table

Why a temp table vs CTE (via with clause)? concerned with potential performance issue for a large volume of data and a CTE self-join with no usable index/hashing scheme; a temp table allows for creation of an index which should improve performance on the self-join [ see slow CTE self join ]
Data is written to #columns in PK+ColName+UpdateDate order, allowing us to store PK/Colname values in adjacent rows; an identity column (rid) allows us to self-join these consecutive rows via rid = rid + 1

3 - Perform a self join of the #temp table to generate the desired output

Cutting-n-pasting from rextester ...

Create some sample data and our #columns table:

CREATE TABLE dbo.bigtable
(UpdateDate datetime      not null
,PK         varchar(12)   not null
,col1       varchar(100)      null
,col2       int               null
,col3       varchar(20)       null
,col4       datetime          null
,col5       char(20)          null
,PRIMARY KEY (PK)
);

CREATE TABLE dbo.bigtable_archive
(UpdateDate datetime      not null
,PK         varchar(12)   not null
,col1       varchar(100)      null
,col2       int               null
,col3       varchar(20)       null
,col4       datetime          null
,col5       char(20)          null
,PRIMARY KEY (PK, UpdateDate)
);

insert into dbo.bigtable         values ('20170512', 'ABC', NULL, 6, 'C1', '20161223', 'closed')

insert into dbo.bigtable_archive values ('20170427', 'ABC', NULL, 6, 'C1', '20160820', 'open')
insert into dbo.bigtable_archive values ('20170315', 'ABC', NULL, 5, 'C1', '20160820', 'open')
insert into dbo.bigtable_archive values ('20170212', 'ABC', 'C1', 1, 'C1', '20160820', 'open')
insert into dbo.bigtable_archive values ('20170109', 'ABC', 'C1', 1, 'C1', '20160513', 'open')

insert into dbo.bigtable         values ('20170526', 'XYZ', 'sue', 23, 'C1', '20161223', 're-open')

insert into dbo.bigtable_archive values ('20170401', 'XYZ', 'max', 12, 'C1', '20160825', 'cancel')
insert into dbo.bigtable_archive values ('20170307', 'XYZ', 'bob', 12, 'C1', '20160825', 'cancel')
insert into dbo.bigtable_archive values ('20170223', 'XYZ', 'bob', 12, 'C1', '20160820', 'open')
insert into dbo.bigtable_archive values ('20170214', 'XYZ', 'bob', 12, 'C1', '20160513', 'open')
;

create table #columns
(rid        int           identity(1,1)
,PK         varchar(12)   not null
,UpdateDate datetime      not null
,ColName    varchar(128)  not null
,ColValue   varchar(max)      null
,PRIMARY KEY (rid, PK, UpdateDate, ColName)
);

The guts of the solution:

declare @columns_max varchar(max),
        @columns_raw varchar(max),
        @cmd         varchar(max)

select  @columns_max = stuff((select ',isnull(convert(varchar(max),'+name+'),''NULL'') as '+name
                from    syscolumns
                where   id   = object_id('dbo.bigtable')
                and     name not in ('PK','UpdateDate')
                order by name
                for xml path(''))
            ,1,1,''),
        @columns_raw = stuff((select ','+name
                from    syscolumns
                where   id   = object_id('dbo.bigtable')
                and     name not in ('PK','UpdateDate')
                order by name
                for xml path(''))
            ,1,1,'')


select @cmd = '
insert #columns (PK, UpdateDate, ColName, ColValue)
select PK,UpdateDate,ColName,ColValue
from
(select PK,UpdateDate,'+@columns_max+' from bigtable
 union all
 select PK,UpdateDate,'+@columns_max+' from bigtable_archive
) p
unpivot
  (ColValue for ColName in ('+@columns_raw+')
) as unpvt
order by PK, ColName, UpdateDate'

--select @cmd

execute(@cmd)

--select * from #columns order by rid
;

select  c2.PK, c2.UpdateDate, c2.ColName as ColumnName, c1.ColValue as 'Old Value', c2.ColValue as 'New Value'
from    #columns c1,
        #columns c2
where   c2.rid                       = c1.rid + 1
and     c2.PK                        = c1.PK
and     c2.ColName                   = c1.ColName
and     isnull(c2.ColValue,'xxx')   != isnull(c1.ColValue,'xxx')
order by c2.UpdateDate, c2.PK, c2.ColName
;

And the results:

Note: apologies ... couldn't figure out an easy way to cut-n-paste the rextester output into a code block. I'm open to suggestions.

Potential issues/concerns:

1 - conversion of data to a generic varchar(max) can lead to loss of data precision which in turn can mean we miss some data changes; consider the following datetime and float pairs which, when converted/cast to the generic 'varchar(max)', lose their precision (ie, the converted values are the same):

original value       varchar(max)
-------------------  -------------------
06/10/2017 10:27:15  Jun 10 2017 10:27AM
06/10/2017 10:27:18  Jun 10 2017 10:27AM

    234.23844444                 234.238
    234.23855555                 234.238

    29333488.888            2.93335e+007
    29333499.999            2.93335e+007

While data precision could be maintained it would require a bit more coding (eg, casting based on source column datatypes); for now I've opted to stick with the generic varchar(max) per the OP's recommendation (and assumption that the OP knows the data well enough to know that we won't run into any issues of data precision loss).

2 - for really large sets of data we run the risk of blowing out some server resources, whether it be tempdb space and/or cache/memory; primary issue comes from the data explosion that occurs during an unpivot (eg, we go from 1 row and 302 pieces of data to 300 rows and 1200-1500 pieces of data, including 300 copies of the PK and UpdateDate columns, 300 column names)

Dharmendar Kumar 'DK' · Accepted Answer · 2017-06-06 17:10:45Z

This approach uses dynamic query to generate a sql to get the changes. The SP takes a table & schema name and gives the output you desire.

The assumptions are that PK and UpdateDate columns are present in all tables. And all archive tables have the format originalTableName + "_archive"..

NB: I have not checked it for performance.

NB: since this uses dynamic sql, I should add caveat about security/sql injection. Restrict access to SP & add other validations to prevent sql injection.

    CREATE proc getTableChanges
    @schemaname  varchar(255),
    @tableName varchar(255)
    as

    declare @strg nvarchar(max), @colNameStrg nvarchar(max)='', @oldValueString nvarchar(max)='', @newValueString nvarchar(max)=''

    set @strg = '
    with cte as (

    SELECT  * , ROW_NUMBER() OVER(partition by PK ORDER BY UpdateDate) as RowNbr
    FROM    (

        SELECT  *
        FROM    [' + @schemaname + '].[' + @tableName + ']

        UNION

        SELECT  *
        FROM    [' + @schemaname + '].[' + @tableName + '_archive]

        ) a

    )
    '


    SET @strg = @strg + '

    SELECT  a.pk, a.updateDate, 
    CASE '

    DECLARE @colName varchar(255)
    DECLARE cur CURSOR FOR
        SELECT  COLUMN_NAME
        FROM    INFORMATION_SCHEMA.COLUMNS
        WHERE TABLE_SCHEMA = @schemaname
        AND TABLE_NAME = @tableName
        AND COLUMN_NAME NOT IN ('PK', 'Updatedate')

    OPEN cur
    FETCH NEXT FROM cur INTO @colName 

    WHILE @@FETCH_STATUS = 0
    BEGIN

        SET @colNameStrg  = @colNameStrg  + ' when a.' + @colName + ' <> b.' + @colName + ' then ''' + @colName + ''' '
        SET @oldValueString = @oldValueString + ' when a.' + @colName + ' <> b.' + @colName + ' then cast(a.' + @colName + ' as varchar(max))'
        SET @newValueString = @newValueString + ' when a.' + @colName + ' <> b.' + @colName + ' then cast(b.' + @colName + ' as varchar(max))'


    FETCH NEXT FROM cur INTO @colName 
    END

    CLOSE cur
    DEALLOCATE cur


    SET @colNameStrg = @colNameStrg  + '    END as ColumnChanges '
    SET @oldValueString = 'CASE ' + @oldValueString + ' END as OldValue'
    SET @newValueString = 'CASE ' + @newValueString + ' END as NewValue'

    SET @strg = @strg + @colNameStrg + ',' + @oldValueString + ',' + @newValueString

    SET @strg = @strg + '
        FROM    cte a join cte b on a.PK = b.PK and a.RowNbr + 1 = b.RowNbr 
        ORDER BY  a.pk, a.UpdateDate
    '

    print @strg

    execute sp_executesql @strg


    go

Sample Call:

exec getTableChanges 'dbo', 'bigTable'

If I'm not mistaken , this does not catch multiple changes made to the same row right? — Mikael Eriksson, Commented Jun 7, 2017 at 6:57
that is right.. multiple columns updated at same time will not be captured. only first column with a change will be captured. — Dharmendar Kumar 'DK', Commented Jun 7, 2017 at 16:23

KumarHarsh · Accepted Answer · 2017-06-09 04:12:53Z

I am using AdventureWorks2012`,Production.ProductCostHistory and Production.ProductListPriceHistory in my example.It may not be perfect history table example, "but script is able to put together the desire output and correct output".

     DECLARE @sql NVARCHAR(MAX)
    ,@columns NVARCHAR(Max)
    ,@table VARCHAR(200) = 'ProductCostHistory'
    ,@Schema VARCHAR(200) = 'Production'
    ,@Archivecolumns NVARCHAR(Max)
    ,@ColForUnpivot NVARCHAR(Max)
    ,@ArchiveColForUnpivot NVARCHAR(Max)
    ,@PKCol VARCHAR(200) = 'ProductID'
    ,@UpdatedCol VARCHAR(200) = 'modifiedDate'
    ,@Histtable VARCHAR(200) = 'ProductListPriceHistory'
SELECT @columns = STUFF((
            SELECT ',CAST(p.' + QUOTENAME(column_name) + ' AS VARCHAR(MAX)) AS ' + QUOTENAME(column_name)
            FROM information_schema.columns
            WHERE table_name = @table
                AND column_name NOT IN (
                    @PKCol
                    ,@UpdatedCol
                    )
            ORDER BY ORDINAL_POSITION
            FOR XML PATH('')
            ), 1, 1, '')
    ,@Archivecolumns = STUFF((
            SELECT ',CAST(p1.' + QUOTENAME(column_name) + ' AS VARCHAR(MAX)) AS ' + QUOTENAME('A_' + column_name)
            FROM information_schema.columns
            WHERE table_name = @Histtable
                AND column_name NOT IN (
                    @PKCol
                    ,@UpdatedCol
                    )
            ORDER BY ORDINAL_POSITION
            FOR XML PATH('')
            ), 1, 1, '')
    ,@ColForUnpivot = STUFF((
            SELECT ',' + QUOTENAME(column_name)
            FROM information_schema.columns
            WHERE table_name = @table
                AND column_name NOT IN (
                    @PKCol
                    ,@UpdatedCol
                    )
            ORDER BY ORDINAL_POSITION
            FOR XML PATH('')
            ), 1, 1, '')
    ,@ArchiveColForUnpivot = STUFF((
            SELECT ',' + QUOTENAME('A_' + column_name)
            FROM information_schema.columns
            WHERE table_name = @Histtable
                AND column_name NOT IN (
                    @PKCol
                    ,@UpdatedCol
                    )
            ORDER BY ORDINAL_POSITION
            FOR XML PATH('')
            ), 1, 1, '')

--SELECT @columns   ,@Archivecolumns    ,@ColForUnpivot
SET @sql = N' 
    SELECT ' + @PKCol + ', ColumnName,
            OldValue,NewValue,' + @UpdatedCol + '
    FROM    (  
    SELECT p.' + @PKCol + '
        ,p.' + @UpdatedCol + '
        ,' + @columns + '
        ,' + @Archivecolumns + '
    FROM ' + @Schema + '.' + @table + ' p
    left JOIN ' + @Schema + '.' + @Histtable + ' p1 ON p.' + @PKCol + ' = p1.' + @PKCol + '

  ) t
    UNPIVOT (
        OldValue
        FOR ColumnName in (' + @ColForUnpivot + ')
    ) up

     UNPIVOT (
        NewValue
        FOR ColumnName1 in (' + @ArchiveColForUnpivot + ')
    ) up1

--print @sql
EXEC (@sql)

Here in inner Select query consider p as Main Table and p1 as History table.In unpivot it is important to convert it into same type.

You can take any other table name with fewer column name to understand my script.Any Explanation need then ping me.

Stack Exchange Network

Query detailing differences between rows for a large amount of data

6 Answers 6

Your Answer

Not the answer you're looking for? Browse other questions tagged
sql-server
sql-server-2008-r2
or ask your own question.

Hot Network Questions

Query detailing differences between rows for a large amount of data

6 Answers 6

Your Answer

Sign up or log in

Post as a guest

Not the answer you're looking for? Browse other questions tagged sql-serversql-server-2008-r2 or ask your own question.

Related

Hot Network Questions

Not the answer you're looking for? Browse other questions tagged
sql-server
sql-server-2008-r2
or ask your own question.