Return to Revisions

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 (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
;