最近遇到一个误更新数据的问题，使用ApexSQL Log做挖掘事务日志的时候，发现ApexSQL Log生成的Redo Script跟原始SQL是有区别的。它们并不是完全一致的。只是逻辑上等价而已。如下所示，我们做一个测试，如下所示，创建一个表后，并模拟忘记添加条件，出现误删除数据的情况
SELECT * INTO KERRY_TEST FROM SYS.OBJECTS;
DELETE FROM KERRY_TEST
然后我们用ApexSQL Log挖掘事务日志，你会看到有很多对应的Delete记录，而且对应的Redo Script跟原始SQL是不一样的。如下所示，
下面是其中两个Redo Script（为了方便查看，对脚本进行了格式化）
--DELETE (0003F73C:000065FC:000D) done at 2019-06-13 10:35:56.876 by xxx\xxxx in transaction 0000:0ADF73EA (Committed)
BEGIN TRANSACTION;
DELETE FROM [dbo].[KERRY_TEST]
WHERE /*** WARNING: WHERE CLAUSE FOR THIS STATEMENT WAS GENERATED FOR A TABLE WITH NO PRIMARY KEY AND NO CLUSTERED INDEX ***/
[name] = N'sysfgfrag' COLLATE Chinese_PRC_CI_AS
AND [object_id] = 19
AND [principal_id] IS NULL
AND [schema_id] = 4
AND [parent_object_id] = 0
AND [type] = N'S ' COLLATE Latin1_General_CI_AS_KS_WS
AND [type_desc] = N'SYSTEM_TABLE' COLLATE Latin1_General_CI_AS_KS_WS
AND [create_date] = '20160101 10:46:55.060'
AND [modify_date] = '20160101 10:46:55.100'
AND [is_ms_shipped] = 1
AND [is_published] = 0
AND [is_schema_published] = 0;
IF @@ROWCOUNT <= 1
COMMIT TRANSACTION;
ELSE
BEGIN
ROLLBACK TRANSACTION;
PRINT 'ERROR: STATEMENT AFFECTED MORE THAN ONE ROW. ALL THE CHANGES WERE ROLLED BACK.';
END;
--DELETE (0003F73C:000065FC:000A) done at 2019-06-13 10:35:56.876 by xxx\xxx in transaction 0000:0ADF73EA (Committed)
BEGIN TRANSACTION;
DELETE FROM [dbo].[KERRY_TEST]
WHERE /*** WARNING: WHERE CLAUSE FOR THIS STATEMENT WAS GENERATED FOR A TABLE WITH NO PRIMARY KEY AND NO CLUSTERED INDEX ***/
[name] = N'sysseobjvalues' COLLATE Chinese_PRC_CI_AS
AND [object_id] = 9
AND [principal_id] IS NULL
AND [schema_id] = 4
AND [parent_object_id] = 0
AND [type] = N'S ' COLLATE Latin1_General_CI_AS_KS_WS
AND [type_desc] = N'SYSTEM_TABLE' COLLATE Latin1_General_CI_AS_KS_WS
AND [create_date] = '20160101 10:47:02.050'
AND [modify_date] = '20160101 10:47:02.057'
AND [is_ms_shipped] = 1
AND [is_published] = 0
AND [is_schema_published] = 0;
IF @@ROWCOUNT <= 1
COMMIT TRANSACTION;
ELSE
BEGIN
ROLLBACK TRANSACTION;
PRINT 'ERROR: STATEMENT AFFECTED MORE THAN ONE ROW. ALL THE CHANGES WERE ROLLED BACK.';
END;
如果有聚集索引，你会看到没有WHERE条件的UPDATE、DELETE操作都会生成很多个对应的Redo Script，对应的WHERE条件则是聚集索引字段。如下测试所示：
SELECT *
INTO KERRY_TEST2
FROM sys.objects;
CREATE CLUSTERED INDEX PK_KERRY_TEST2 ON KERRY_TEST2(OBJECT_ID);
UPDATE KERRY_TEST2
SET NAME = 'KERRY';
DELETE FROM KERRY_TEST2;
下面是对应的UPDATE、DELETE操作的redo script
--UPDATE (0003F73C:0000700A:001F) done at 2019-06-13 11:27:26.036 by xxx\xxx in transaction 0000:0ADF7404 (Committed)
BEGIN TRANSACTION;
UPDATE [dbo].[KERRY_TEST2]
SET [name] = N'KERRY' COLLATE Chinese_PRC_CI_AS
WHERE [object_id] = 3;
IF @@ROWCOUNT <= 1
COMMIT TRANSACTION;
ELSE
BEGIN
ROLLBACK TRANSACTION;
PRINT 'ERROR: STATEMENT AFFECTED MORE THAN ONE ROW. ALL THE CHANGES WERE ROLLED BACK.';
END;
-- DELETE (0003F73C:00007482:00D9) done at 2019-06-13 11:27:35.406 by xxx\xxx in transaction 0000:0ADF7405 (Committed)
BEGIN TRANSACTION;
DELETE FROM [dbo].[KERRY_TEST2]
WHERE [object_id] = 836302139;
IF @@ROWCOUNT <= 1
COMMIT TRANSACTION;
ELSE
BEGIN
ROLLBACK TRANSACTION;
PRINT 'ERROR: STATEMENT AFFECTED MORE THAN ONE ROW. ALL THE CHANGES WERE ROLLED BACK.';
END;
这里只是简单记录一下这个事情，千万不要以为Redo Script跟原始SQL是完全一致的。如果遇到这种问题，如何判断呢？可以查看Transaction ID，以及对应的Begin Time , End Time,如下所示，你会发现它们是一致的