SQL Server无update_Date列时，如何获取行最后变更DateTime及目标行？

Alright, let's break down this tricky problem you're facing. Since your table only has an insert_Date column (which only tracks when rows were first added, not updated), and you can't modify the table schema or add triggers, we need to get creative with workarounds. Here are the most feasible options:

Option 1: Use SQL Server's Built-in Change Tracking (If Enabled) #

If your database has Change Data Capture (CDC) or Change Tracking enabled for the table, this is the cleanest solution:

For CDC: #

CDC captures all insert/update/delete operations for tracked tables, and you can map log sequence numbers (LSNs) to actual timestamps:

SELECT
    __$operation, -- 2 = Insert, 4 = Update (after image)
    [your_primary_key_column],
    sys.fn_cdc_map_lsn_to_time(__$start_lsn) AS last_change_datetime,
    -- Include your other columns as needed
    [column1], [column2]
FROM cdc.[your_table_name_CT] -- CDC table suffix is _CT
WHERE sys.fn_cdc_map_lsn_to_time(__$start_lsn) > '2024-01-01 00:00:00'
ORDER BY last_change_datetime DESC;

Note: CDC needs to be pre-configured on the database and table. If it wasn't enabled before, this won't help with historical changes.

For Change Tracking: #

If Change Tracking is enabled (lighter weight than CDC), you can get the latest version of rows and when they were changed:

DECLARE @last_sync_version BIGINT = CHANGE_TRACKING_CURRENT_VERSION() - 1; -- Adjust based on your sync point

SELECT
    t.[your_primary_key_column],
    t.[column1], t.[column2],
    CT.SYS_CHANGE_VERSION,
    CT.SYS_CHANGE_CREATION_VERSION,
    CHANGE_TRACKING_GET_SYSTEM_TIME_FROM_VERSION(CT.SYS_CHANGE_VERSION) AS last_change_datetime
FROM [your_table_name] t
JOIN CHANGETABLE(CHANGES [your_table_name], @last_sync_version) CT
    ON t.[your_primary_key_column] = CT.[your_primary_key_column]
WHERE CHANGE_TRACKING_GET_SYSTEM_TIME_FROM_VERSION(CT.SYS_CHANGE_VERSION) > '2024-01-01 00:00:00';

Option 2: Track Row Changes via Hash Comparison (For Smaller Tables) #

If you can create a helper table (you didn't mention restrictions on creating new tables), you can track row changes by comparing hash values of row data over time:

1. First, create a helper table to store baseline hashes:

CREATE TABLE RowChangeTracker (
    [your_primary_key_column] INT PRIMARY KEY,
    RowHash VARBINARY(64) NOT NULL,
    LastChecked DATETIME DEFAULT GETDATE()
);

2. Initialize it with your current table data:

INSERT INTO RowChangeTracker ([your_primary_key_column], RowHash)
SELECT
    [your_primary_key_column],
    -- Hash all columns to create a unique fingerprint for the row
    HASHBYTES('SHA2_256', CONCAT_WS('|', [column1], [column2], [column3]))
FROM [your_table_name];

3. To find rows changed after a specific datetime:

-- Get changed rows
SELECT
    t.*,
    GETDATE() AS last_change_datetime
FROM [your_table_name] t
LEFT JOIN RowChangeTracker r
    ON t.[your_primary_key_column] = r.[your_primary_key_column]
WHERE
    -- Either the row is new (not in tracker)
    r.[your_primary_key_column] IS NULL
    OR
    -- Or the hash doesn't match (row was updated)
    HASHBYTES('SHA2_256', CONCAT_WS('|', t.[column1], t.[column2], t.[column3])) != r.RowHash
    AND r.LastChecked > '2024-01-01 00:00:00';

-- Update the tracker with new hashes and timestamps
MERGE RowChangeTracker r
USING [your_table_name] t
    ON r.[your_primary_key_column] = t.[your_primary_key_column]
WHEN MATCHED AND HASHBYTES('SHA2_256', CONCAT_WS('|', t.[column1], t.[column2], t.[column3])) != r.RowHash THEN
    UPDATE SET r.RowHash = HASHBYTES('SHA2_256', CONCAT_WS('|', t.[column1], t.[column2], t.[column3])),
               r.LastChecked = GETDATE()
WHEN NOT MATCHED THEN
    INSERT ([your_primary_key_column], RowHash)
    VALUES (t.[your_primary_key_column], HASHBYTES('SHA2_256', CONCAT_WS('|', t.[column1], t.[column2], t.[column3])));

Pros: Works without database-level change tracking.
Cons: Slow for large tables (hashing all columns is resource-heavy), and you need to run this periodically to keep the tracker up to date.

Option 3: Parse the Transaction Log (Emergency Only) #

If all else fails, you can read the SQL Server transaction log to find changes, but this is a last-resort method—it's complex, fragile, and logs get truncated (e.g., after backups):

Use the fn_dblog function to query the log:

SELECT
    [Current LSN],
    [Operation],
    [Context],
    [Transaction ID],
    [Begin Time] AS change_start_time,
    [End Time] AS change_end_time
FROM fn_dblog(NULL, NULL)
WHERE
    -- Filter for insert/update operations
    [Operation] IN ('LOP_INSERT_ROWS', 'LOP_MODIFY_ROW')
    -- Replace with your table's allocation unit name (check sys.allocation_units)
    AND [AllocUnitName] = 'dbo.your_table_name'
    AND [Begin Time] > '2024-01-01 00:00:00';

Note: You'll need to parse the log data to map entries back to specific rows (this requires decoding binary data, which is non-trivial). This isn't suitable for regular use.

Option 4: Check the Application's Audit Logs #

Since the insert/update operations are coming from a Windows application, the most reliable solution might be to ask the application team if they already track change timestamps and row IDs in an application-level audit log. Application-side tracking is always more accurate than trying to retroactively capture changes in the database without proper schema support.

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