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不至于负载过大.