Sqlserver: 循环删除表数据

USE mydatabase
GO

/*******************************************************
* Data_ItemList_Additional is Data_ItemList'Additional
* Data_ItemList_Additional store bigdata for another table
*
* delete userid'info from table Data_ItemList_Additional
* Database: mydatabase
* author: greatwqs
* createtime: 2012-08-15
*********************************************************/

DECLARE @icount INTEGER
    
SELECT @icount = COUNT(1)
FROM [dbo].[Data_ItemList] WITH(NOLOCK)
WHERE [userid] = 2

SELECT @ICOUNT 
    
WHILE @icount > 0 
BEGIN
    DELETE TOP (2000)
    FROM [dbo].[Data_ItemList_Additional]   AS a
    INNER JOIN (
        SELECT top 20000 ItemID
        FROM [dbo].[Data_ItemList] WITH(NOLOCK)
        WHERE [userid] = 2
    )AS b
    ON a.ItemId = b.ItemID
    
    SET @icount = @icount -20000
    WAITFOR DELAY '00:00:05'

END

?

删除Data_ItemList_Additional表中数据,隔5秒钟删除2万条数据,sqlserver不至于负载过大.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!