T-SQL——关于删除

目录

shanzm-2024年5月21日 09:20:38

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 是删除指定表的所有行，并将自增值重置

    • 用于删除指定表的所有行，并且它不会记录每行的删除。这意味着它比使用 DELETE 命令删除所有行更快，因为它不扫描表中的每一行来删除，并且它不会为每行的删除生成日志

    • 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修改名称为表A，同时将原表A有的主键和索引创建在当前表上
posted @ 2024-05-22 17:21  shanzm  阅读(33)  评论(0)  编辑  收藏  举报
TOP