批量删除数据时如何提高效率?
现一数据库中有大量的表,需要将其数据清除后重新从另一数据库中转换新数据。
由于许多表相互间有设置外键关联,Truncate Table语句在处理这种表时会失败,所以仅采用 Delete From table1的方式来删除。
大致的存贮过程如下:
CREATE PROCEDURE [kn_ClearData] AS
delete from table1
delete from table2
delete from table3
delete from table4
delete from table4
delete from ....
delete from tableN
Go
有两个疑问:
1 如果在每一个delete后面加一个GO,则这些SQL会单独执行而不是成批执行,这样会否提交一些性能呢?或者有其他方法可以提高性能?
2 有些表中的Identity字段,在用 Delete From tableN 删除全部数据后如何重置其种子值,就像Truncate table的效果一样?