Using a PARTITION BY JOIN to Fill In The Gaps in Sparse Data

Transact-SQL
2

I think Oracle deals better with sparse data. With SQL Server try DISTINCT and an extra JOIN.
Something like:

WITH AuditPartitions
AS
(
	SELECT DISTINCT TABLE_NAME, CREATION_TIMESTAMP, RECORD_KEY
	FROM AUDIT_DATA
)
SELECT V.TABLE_NAME 
	,V.PROGRAM_NO 
	,V.COLUMN_NAME 
	,A.CREATION_TIMESTAMP 
	,A.RECORD_KEY 
	,A.BEFORE_VALUE 
	,A.AFTER_VALUE 
FROM AUDIT_010_V V
	JOIN AuditPartitions P
		ON V.TABLE_NAME = P.TABLE_NAME
	LEFT JOIN AUDIT_DATA A
		ON V.TABLE_NAME = A.TABLE_NAME
			AND V.COLUMN_NAME = A.COLUMN_NAME
			AND P.CREATION_TIMESTAMP = A.CREATION_TIMESTAMP
			AND P.RECORD_KEY = A.RECORD_KEY
ORDER BY TABLE_NAME, COLUMN_NAME, CREATION_TIMESTAMP, RECORD_KEY;

Also, I would not make AUDIT_DATA.CREATION_TIMESTAMP nvarchar. Try something like datetime2(0) or datetime.