SQL Server无创建/更新日期字段，如何获取指定时段修改的记录用于迁移？

可以实现，但现有方案各有局限性，以下针对你的问题和尝试的代码逐一说明：

你的现有代码的局限性 #

你尝试通过sys.dm_db_index_usage_stats获取更新时间，这个方法存在明显缺陷：

• 该视图的统计数据在SQL Server服务重启后会被清空，无法持久化追溯历史变更
• 只能获取表级别的最后更新时间，无法定位到具体哪些行被修改
• 若目标表是无聚集索引的堆表，部分更新操作可能不会被统计到该视图中

可行的解决方案 #

1. 变更数据捕获（CDC） #

这是SQL Server官方提供的行级变更追踪方案，无需修改原表结构：

• 先开启数据库级CDC：

  EXEC sys.sp_cdc_enable_db;
  

• 再针对目标表开启CDC（替换dbo和tablename为你的实际架构和表名）：

  EXEC sys.sp_cdc_enable_table 
      @source_schema = N'dbo', 
      @source_name = N'tablename', 
      @role_name = NULL;
  

• 之后可通过CDC内置函数查询指定时间段的变更记录，例如：

  SELECT *
  FROM cdc.fn_cdc_get_all_changes_dbo_tablename(
      '2024-01-01 00:00:00', 
      '2024-01-31 23:59:59',
      'all'
  );
  

• 注意：CDC仅在SQL Server标准版及以上版本支持，会占用额外存储资源。

2. 读取事务日志（不推荐生产环境） #

可通过未公开的系统函数fn_dblog或fn_dump_dblog读取未被截断的事务日志，从而提取变更记录：

SELECT 
    Operation,
    CONVERT(DATETIME, SUBSTRING(TransactionTime, 1, 8) + ' ' + SUBSTRING(TransactionTime, 9, 6), 112) AS ChangeTime,
    AllocUnitName,
    RowLogContents1
FROM fn_dblog(NULL, NULL)
WHERE 
    Operation IN ('LOP_INSERT_ROWS', 'LOP_MODIFY_ROW', 'LOP_DELETE_ROWS')
    AND AllocUnitName = 'dbo.tablename'
    AND CONVERT(DATETIME, SUBSTRING(TransactionTime, 1, 8) + ' ' + SUBSTRING(TransactionTime, 9, 6), 112) BETWEEN '2024-01-01' AND '2024-01-31';

• 局限性：函数未被微软官方支持，后续版本可能变更；仅能读取未被截断的日志，若日志因备份等操作被截断，历史变更无法查询；解析日志内容复杂度高。

3. 添加追踪字段（长期推荐方案） #

如果允许修改表结构，这是最可靠的持久化方案：

• 添加创建和更新时间字段，并通过触发器自动维护：

  -- 添加创建时间字段，默认值为当前时间
  ALTER TABLE dbo.tablename ADD CreateTime DATETIME DEFAULT GETDATE();
  -- 添加更新时间字段
  ALTER TABLE dbo.tablename ADD UpdateTime DATETIME;
  
  -- 创建触发器，更新时自动同步更新时间
  CREATE TRIGGER trg_tablename_UpdateTime
  ON dbo.tablename
  AFTER UPDATE
  AS
  BEGIN
      SET NOCOUNT ON;
      UPDATE t
      SET t.UpdateTime = GETDATE()
      FROM dbo.tablename t
      JOIN inserted i ON t.PrimaryKey = i.PrimaryKey; -- 替换为你的表主键字段
  END;
  

• 优点：数据持久化，后续同步逻辑简单可靠；缺点：需要修改表结构，需评估对现有业务的影响。

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