-- 创建删除过期记录的存储过程
CREATE PROCEDURE sp_DeleteOldRecords
AS
BEGIN
    DELETE FROM YourTable
    WHERE CreateDate < DATEADD(day, -30, GETDATE()) -- 删除30天前的记录
END
GO

-- 创建SQL Server Agent作业定期执行该存储过程
-- 需要在SQL Server Management Studio中配置

-- 创建系统版本控制的临时表
CREATE TABLE YourTable
(
    ID INT PRIMARY KEY,
    Data VARCHAR(100),
    ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START,
    ValidTo DATETIME2 GENERATED ALWAYS AS ROW END,
    PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.YourTableHistory));

-- 定期清理历史表
CREATE PROCEDURE sp_CleanupHistory
AS
BEGIN
    DELETE FROM YourTableHistory
    WHERE ValidTo < DATEADD(month, -6, GETDATE()) -- 保留6个月历史
END

-- 创建分区函数和分区方案
CREATE PARTITION FUNCTION pf_Monthly(DATETIME)
AS RANGE RIGHT FOR VALUES ('20230101', '20230201', '20230301');

CREATE PARTITION SCHEME ps_Monthly
AS PARTITION pf_Monthly
TO (fg1, fg2, fg3, fg4);

-- 创建分区表
CREATE TABLE YourPartitionedTable
(
    ID INT,
    CreateDate DATETIME,
    Data VARCHAR(100)
) ON ps_Monthly(CreateDate);

-- 定期切换和删除旧分区
ALTER PARTITION FUNCTION pf_Monthly()
SPLIT RANGE ('20230401'); -- 添加新分区

ALTER TABLE YourPartitionedTable
SWITCH PARTITION 1 TO YourOldDataTable; -- 切换旧分区

TRUNCATE TABLE YourOldDataTable; -- 删除旧数据

-- 分批删除
WHILE EXISTS (SELECT 1 FROM YourTable WHERE CreateDate < DATEADD(day, -30, GETDATE()))
BEGIN
    DELETE TOP (1000) FROM YourTable
    WHERE CreateDate < DATEADD(day, -30, GETDATE())
    
    WAITFOR DELAY '00:00:00.1' -- 短暂暂停
END

-- 使用UTC时间避免时区问题
DELETE FROM YourTable
WHERE CreateDate < DATEADD(day, -30, GETUTCDATE())