@MaxID为删除ID最大值,每次删除十万,有个数据表9亿条,一次删除十万约3秒,删除一千万四分多钟;
SET STATISTICS TIME ON;
DECLARE @Timer DATETIME = GETDATE();
DECLARE @MaxID bigint =10000000,@DeleteCount int =0;
SET ROWCOUNT 100000;
WHILE 1 = 1
BEGIN
BEGIN TRAN
DELETE FROM [tableName] where id <@MaxID;
Set @DeleteCount=@@ROWCOUNT
COMMIT
IF @DeleteCount = 0 BREAK;
print 'Commit'
END
print 'End'
SET ROWCOUNT 0;
SELECT DATEDIFF(MS, @Timer, GETDATE()) AS [执行时间(毫秒)];
SET STATISTICS TIME OFF;
MSSQL数据删除大量数据
最新推荐文章于 2023-05-31 16:11:00 发布