SQL Unpivot、Cross Apply、动态查询选型求助：库表变更对比咨询

Hey there, let's break down your problem step by step—since you're dealing with audit data synced via triggers and need to compare changes between your application database and audit database, here's how Unpivot, Cross Apply, and dynamic queries fit into your scenario, along with when to use each:

• Core Use Case: This is your go-to if your application tables and corresponding audit tables have fixed, consistent schemas—especially if your audit table stores old/new values as paired columns (e.g., Old_UserId, New_UserId, Old_UserName, New_UserName).
• How It Helps: Unpivot transforms column-based old/new values into row-based records, making it trivial to compare each field's change side-by-side. For example:

SELECT 
    AuditRecordId,
    REPLACE(OldFieldName, 'Old_', '') AS FieldName,
    OldValue,
    NewValue
FROM Audit_Users
UNPIVOT (
    OldValue FOR OldFieldName IN (Old_UserId, Old_UserName)
) AS OldValues
JOIN UNPIVOT (
    NewValue FOR NewFieldName IN (New_UserId, New_UserName)
) AS NewValues ON OldFieldName = REPLACE(NewFieldName, 'New_', 'Old_')
WHERE OldValue <> NewValue -- Filter to only changed fields

• Pros: Straightforward syntax, stable performance (since the schema is fixed, the database can optimize execution plans upfront), and perfect for building static audit log dashboards where you need consistent, clean change details for specific tables.

• Core Use Case: Use this when your audit data doesn't follow strict old/new column naming rules, or when you need to mix audit data with live application data (e.g., compare audit old values to the current state of records in your app DB).
• How It Helps: Cross Apply (paired with the VALUES clause) lets you manually define field mappings and mix data types (with proper casting) without being tied to rigid column structures. Example:

SELECT 
    a.AuditRecordId,
    v.FieldName,
    v.AuditOldValue,
    v.AppCurrentValue
FROM Audit_Users a
CROSS APPLY (
    VALUES
        ('User ID', CAST(a.Old_Id AS VARCHAR(100)), CAST(u.Id AS VARCHAR(100))),
        ('User Name', a.Old_Name, u.Name),
        ('Email', a.Old_Email, u.Email)
) v(FieldName, AuditOldValue, AppCurrentValue)
JOIN Users u ON a.RecordId = u.Id
WHERE v.AuditOldValue <> v.AppCurrentValue

• Pros: Unmatched flexibility—you can add custom logic (like formatting, conditional checks) directly in the VALUES clause, and it works even if your audit table has inconsistent column naming. Great for custom change validation reports where you need to cross-reference audit data with live records.

• Core Use Case: This is essential if you have many tables to audit or if your application tables frequently change (e.g., adding new fields as your business evolves). Hardcoding Unpivot/Cross Apply for every table would be a maintenance nightmare.
• How It Helps: You can query system catalog views (like INFORMATION_SCHEMA.COLUMNS) to dynamically generate the necessary Unpivot or Cross Apply logic based on the current schema of your tables. Example snippet:

DECLARE @TargetTableName NVARCHAR(128) = 'Users'
DECLARE @OldColumns NVARCHAR(MAX)
DECLARE @NewColumns NVARCHAR(MAX)

-- Build list of Old_* columns from the audit table
SELECT @OldColumns = STRING_AGG(QUOTENAME(COLUMN_NAME), ', ')
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Audit_' + @TargetTableName
AND COLUMN_NAME LIKE 'Old_%'

-- Build list of New_* columns
SELECT @NewColumns = STRING_AGG(QUOTENAME(COLUMN_NAME), ', ')
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Audit_' + @TargetTableName
AND COLUMN_NAME LIKE 'New_%'

-- Generate dynamic Unpivot SQL
DECLARE @DynamicSql NVARCHAR(MAX) = N'
SELECT 
    AuditRecordId,
    REPLACE(OldFieldName, ''Old_'', '''') AS FieldName,
    OldValue,
    NewValue
FROM Audit_' + @TargetTableName + '
UNPIVOT (
    OldValue FOR OldFieldName IN (' + @OldColumns + ')
) AS OldVals
JOIN UNPIVOT (
    NewValue FOR NewFieldName IN (' + @NewColumns + ')
) AS NewVals ON OldFieldName = REPLACE(NewFieldName, ''New_'', ''Old_'')
WHERE OldValue <> NewValue'

EXEC sp_executesql @DynamicSql

• Pros: Zero manual maintenance as schemas change—your query will automatically adapt to new fields. Just make sure to use QUOTENAME to prevent SQL injection risks. Ideal for scalable audit systems that need to support multiple tables without constant code updates.

Quick Decision Cheat Sheet #

• Use Unpivot for small, fixed-schema tables where you need simple, fast change comparisons.
• Use Cross Apply when you need custom logic or cross-reference audit data with live application records.
• Use Dynamic Queries (with Unpivot/Cross Apply) for large, evolving systems with many audit tables.

内容的提问来源于stack exchange，提问作者zanq