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
    );
    
    create unique clustered index IX_T on #T(PK, ColumnName, Version);

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

That should give you what I believe is a decent query plan with a merge join.[![enter image description here][1]][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;

<!-- language:lang-xml -->

    <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 #T
    (
      PK varchar(12) not null,
      UpdateDate datetime not null,
      ColumnName nvarchar(128) not null,
      Value varchar(max),
      Version int not null
    );
    
    
    with C as 
    (
      select bt.PK,
             bt.UpdateDate,
             (select bt.* for xml path(''), elements xsinil, type) as X
      from dbo.bigtable as bt
    
    )
    insert into #T (PK, UpdateDate, ColumnName, Value, Version)
    select C.PK,
           C.UpdateDate,
           T.X.value('local-name(.)', 'nvarchar(128)'),
           T.X.value('text()[1]', 'varchar(max)'),
           0
    from C
      cross apply C.X.nodes('*') as T(X);
    
    with C1 as 
    (
      select bt.PK,
             bt.UpdateDate,
             (select bt.* for xml path(''), elements xsinil, type) as X
      from dbo.bigtable_archive as bt
    ),
    C2 as
    (
      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('*') as T(X)
    )
    insert into #T (PK, UpdateDate, ColumnName, Value, Version)
    select C2.PK,
           C2.UpdateDate,
           C2.ColumnName,
           C2.Value,
           row_number() over(partition by C2.PK, C2.ColumnName order by C2.UpdateDate desc)
    from C2;
    
    create unique clustered index IX_T on #T(PK, ColumnName, Version);
    
    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;

  [1]: https://i.sstatic.net/nBihy.png