按id分段删除.DECLARE @i INT SET @i=5000 DELETE STUDENT WHERE ID<@i AND Class ='0903' AND Sex ='M' WHILE @@ROWCOUNT>0 BEGIN BEGIN TRAN SET @i=@i+5000 DELETE STUDENT WHERE ID<@i AND Class ='0903' AND Sex ='M' COMMIT TRAN END
1.开启大容量日志恢复模式 2.删除数据 3.切换到原来的回复模式。切换恢复模式: a. 连接到相应的 Microsoft SQL Server Database Engine 实例之后,在对象资源管理器中,单击服务器名称以展开服务器树。 b. 展开“数据库”,然后根据数据库的不同,选择用户数据库,或展开“系统数据库”,再选择系统数据库。 c. 右键单击该数据库,再单击“属性”,这将打开“数据库属性”对话框。 d. 在“选择页”窗格中,单击“选项”。 e. 当前恢复模式显示在“恢复模式”列表框中。 可以选择“完整”、“大容量日志”或“简单”。
SET ROWCOUNT 100 WHILE 1=1 BEGIN DELETE FROM Student WHERE lass ='0903' AND Sex ='M' IF @@ROWCOUNT<100 BREAK ENDWHILE 1=1 BEGIN DELETE TOP (100) FROM Student WHERE lass ='0903' AND Sex ='M' IF @@ROWCOUNT<100 BREAK END
--2005 DELETE top (5000) STUDENT WHERE Class ='0903' AND Sex ='M' WHILE @@ROWCOUNT>0 BEGIN DELETE top (5000) STUDENT WHERE Class ='0903' AND Sex ='M' END
--2000 or 2005 set rowcount 5000 DELETE STUDENT WHERE Class ='0903' AND Sex ='M' WHILE @@ROWCOUNT>0 BEGIN DELETE STUDENT WHERE Class ='0903' AND Sex ='M' END set rowcount 0
--就是这个 set rowcount 5000 DELETE STUDENT WHERE Class ='0903' AND Sex ='M' WHILE @@ROWCOUNT>0 BEGIN DELETE STUDENT WHERE Class ='0903' AND Sex ='M' END set rowcount 0
SET @i=5000
DELETE STUDENT WHERE ID<@i AND Class ='0903' AND Sex ='M'
WHILE @@ROWCOUNT>0
BEGIN
BEGIN TRAN
SET @i=@i+5000
DELETE STUDENT WHERE ID<@i AND Class ='0903' AND Sex ='M'
COMMIT TRAN
END
2.删除数据
3.切换到原来的回复模式。切换恢复模式:
a. 连接到相应的 Microsoft SQL Server Database Engine 实例之后,在对象资源管理器中,单击服务器名称以展开服务器树。
b. 展开“数据库”,然后根据数据库的不同,选择用户数据库,或展开“系统数据库”,再选择系统数据库。
c. 右键单击该数据库,再单击“属性”,这将打开“数据库属性”对话框。
d. 在“选择页”窗格中,单击“选项”。
e. 当前恢复模式显示在“恢复模式”列表框中。 可以选择“完整”、“大容量日志”或“简单”。
SET ROWCOUNT 100
WHILE 1=1
BEGIN
DELETE FROM Student WHERE lass ='0903' AND Sex ='M'
IF @@ROWCOUNT<100 BREAK
ENDWHILE 1=1
BEGIN
DELETE TOP (100) FROM Student WHERE lass ='0903' AND Sex ='M'
IF @@ROWCOUNT<100 BREAK
END
DELETE top (5000) STUDENT WHERE Class ='0903' AND Sex ='M'
WHILE @@ROWCOUNT>0
BEGIN
DELETE top (5000) STUDENT WHERE Class ='0903' AND Sex ='M'
END
set rowcount 5000
DELETE STUDENT WHERE Class ='0903' AND Sex ='M'
WHILE @@ROWCOUNT>0
BEGIN
DELETE STUDENT WHERE Class ='0903' AND Sex ='M'
END
set rowcount 0
set rowcount 5000
DELETE STUDENT WHERE Class ='0903' AND Sex ='M'
WHILE @@ROWCOUNT>0
BEGIN
DELETE STUDENT WHERE Class ='0903' AND Sex ='M'
END
set rowcount 0