T-SQL——关于删除

• 1. 数据太多，删除太慢，日志暴增，存储不够
• 2. 使用TRUNCATE TABLE
• 3. 按照最小方式记录日志进行删除

1. 数据太多，删除太慢，日志暴增，存储不够

把一个大型的Delete操作可以分拆为多次执行Delete

分拆的越小，事务等级底，不会避免锁，而且可以重复利用事务的日志。

具体分拆方式就是通过 While和Top进行循环删除

这里：循环每次删除5000条数据，每个删除都是一个独立的事务中执行的，当删除完最后一批数据（即，受影响的行数小于5000）时，循环终止

CREATE TABLE [dbo].[TestWhileDelete](
	[Id] [BIGINT] IDENTITY(1,1) NOT NULL,
	[CreateTime] [DATETIME] NULL,
	[Value] [NVARCHAR](50) NULL,
)


INSERT INTO [dbo].[TestWhileDelete]
           ([CreateTime],[Value])
     VALUES
           (GETDATE(),'1234')
GO 1000000



SELECT * FROM  dbo.TestWhileDelete

WHILE 1=1
BEGIN
	--将筛选出来的数据，循环每次删除1000条
    DELETE TOP(5000)   FROM dbo.TestWhileDelete WHERE Id>100 
    IF @@ROWCOUNT<5000 BREAK;
END;

2. 使用TRUNCATE TABLE

• truncate table 是删除指定表的所有行，并将

• truncate table 按照最小方式记录日志，因此很快

• truncate table 不会触发表上的任何delete触发器

• truncate table 将Identity属性重置为最初的种子值，即：将自增值重置

• 使用truncate table 清空表中的百万行数据只要几秒，而使用delete可以时间要很久

• truncate table 具有一定的危险性，一定要确定是指定的表

3. 按照最小方式记录日志进行删除

• 把原表A中需要保留的数据通过SELECT * INTO table_nameA FROM table_nameB到一张新表B中
  • 注意1：table_nameB 会自动创建，所以不需要 也不可以 事先创建好
  • 注意2：从tableA复制到tableB中的仅仅是指定字段和字段的值，不包含主键、索引、约束和触发器,所以千万不要通过此种方式创建一个相同字段，相同功能的表（若是需要还是选中表右键-->编写脚本为-->create到
• 把原表A删除
• 把原表B修改名称为表B，同时将原表A有的主键和索引创建在当前表上