SQL Server指定时间点已删除记录查询的函数/存储过程开发需求

Got it, let's break this down step by step. The core goal here is to pull a snapshot of all records that existed at a specific timestamp—including any records that were deleted after that time but were still present when the target time rolled around.

Step 1: Adjust Your Table Structure #

Your current students table only tracks when records are created (createdate), but we need a way to track when records are deleted too. Physical deletions won't work here (since we can't bring those records back to check their past existence), so we'll use a soft delete approach by adding a deletion timestamp field.

Run this to update your table:

-- For SQL Server
ALTER TABLE students
ADD deletedate DATETIME NULL;

-- For MySQL
ALTER TABLE students
ADD deletedate DATETIME NULL;

Update Your Delete Workflow #

Instead of using DELETE statements to remove records, use UPDATE to mark them as deleted with the current time:

-- SQL Server example: "delete" 30 records at 10:30
UPDATE students
SET deletedate = GETDATE()
WHERE id IN (SELECT TOP 30 id FROM students); -- Replace with your actual filter

-- MySQL example
UPDATE students
SET deletedate = NOW()
WHERE id IN (SELECT id FROM students LIMIT 30);

Step 2: Build the Stored Procedure #

Below are working examples for both SQL Server and MySQL that accept a target datetime and return the correct snapshot.

SQL Server Stored Procedure #

CREATE PROCEDURE GetStudentsAtDateTime
    @TargetDateTime DATETIME
AS
BEGIN
    SET NOCOUNT ON; -- Hide extra row count messages

    SELECT id, name, createdate
    FROM students
    WHERE 
        -- Record was created on or before the target time
        createdate <= @TargetDateTime
        AND
        -- Either the record was never deleted, or deletion happened AFTER the target time
        (deletedate IS NULL OR deletedate > @TargetDateTime);
END

MySQL Stored Procedure #

DELIMITER //
CREATE PROCEDURE GetStudentsAtDateTime(IN TargetDateTime DATETIME)
BEGIN
    SELECT id, name, createdate
    FROM students
    WHERE 
        createdate <= TargetDateTime
        AND (deletedate IS NULL OR deletedate > TargetDateTime);
END //
DELIMITER ;

How It Aligns With Your Example #

Let's walk through your scenario to confirm it works:

1. Before 10:30, 100 records exist—all have deletedate = NULL.
2. At 10:30, 30 records are "deleted" (their deletedate is set to 2024-XX-XX 10:30:00).
3. At 10:35, call the procedure with @TargetDateTime = '2024-XX-XX 10:35:00':
   • The 30 deleted records have deletedate <= 10:35, so they're excluded. You get the 70 active records as expected.
4. If you call it with @TargetDateTime = '2024-XX-XX 10:29:00':
   • All 100 records have deletedate = NULL (since deletion happened later) or deletedate > 10:29, so all are returned.

Key Tips #

• Soft Delete is Non-Negotiable: Physical deletions erase historical data, so this approach relies on marking records as deleted instead of removing them.
• Performance Boost: For large tables, add a composite index on (createdate, deletedate) to speed up the snapshot query.
• Extended Use Cases: If you need to track edits (not just deletions), you'd need a full history table or change data capture (CDC), but this solution covers your core requirement perfectly.

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