批量删除表数据

当删除表数据过大时，同时也会出现锁等待，使用小批量删除数据，减少事务锁等待，做了一个随笔小记。

/*
批量构造数据
2048*N(@index)
*/
IF OBJECT_ID('test_info') IS NOT NULL DROP TABLE test_info;
IF OBJECT_ID('test_info') IS NULL CREATE TABLE test_info(id INT,tid INT IDENTITY);

BEGIN TRAN A1;
DECLARE @index INT;
SET @index = 100;
WHILE (@index > 0)
BEGIN
    INSERT INTO dbo.test_info
    (
        id
    )
    SELECT
        number
    FROM master..spt_values
    WHERE type = 'p';
    SET @index = @index - 1;
END;
COMMIT TRAN A1;

/*
批量删除数据
*/
DECLARE @endDate DATETIME,@topSize INT,@delayCount int ;
SELECT @endDate =dateadd(month,-1,getdate()), @topSize = 5000, @delayCount=0;

IF OBJECT_ID('tempdb..#waitProcessData') IS NOT NULL DROP TABLE #waitProcessData;
IF OBJECT_ID('tempdb..#waitBlockData') IS NOT NULL DROP TABLE #waitBlockData;

CREATE TABLE #waitBlockData(tid bigint);

SELECT tid INTO #waitProcessData FROM test_info(NOLOCK) 
INSERT INTO #waitBlockData(tid) SELECT TOP (@topSize) tid FROM	#waitProcessData

WHILE EXISTS( SELECT tid FROM #waitBlockData)
BEGIN	
	BEGIN TRAN  DEL
	DELETE tb FROM test_info tb
    INNER JOIN #waitBlockData tmp ON tmp.tid = tb.tid;

	DELETE waitData FROM #waitProcessData waitData
	INNER JOIN #waitBlockData tmp ON tmp.tid = waitData.tid;

	TRUNCATE TABLE #waitBlockData;
	INSERT INTO #waitBlockData(tid) 
	SELECT  TOP (@topSize) tid FROM #waitProcessData;	
	COMMIT TRAN DEL	
	
	SET @delayCount = @delayCount+ @topSize;	
	IF(@topSize*4 = @delayCount)
	BEGIN			
		WAITFOR DELAY '00:00:00.128'
		SET @delayCount = 0;
	END	
END;