如何从sql server上的一个表中删除8500,000条记录

delete activities where unt_uid is null

删除unt_uid为空的活动

would be the fastest way but nobody can access the database / table until this statement has finished so this is a no-go.

这将是最快的方法，但是在此语句完成之前没有人可以访问数据库/表，所以这是不允许的。

I defined a cursor to get this task done during working time but anyway the impact to productivity is to big. So how to delete these record so that the normal use of this database is guaranteed?

我定义了一个游标来在工作时间完成这项任务，但是无论如何，它对生产力的影响是巨大的。那么，如何删除这些记录，以保证数据库的正常使用呢?

It's a SQL-2005 Server on a 32-bit Win2003. Second Question is: How Long would you estimate for this job to be done (6 hours or 60 hours)? (Yes, i know that depends on the load but assume that this is a small-business environment)

它是32位Win2003上的SQL-2005服务器。第二个问题是:你估计这项工作要完成多长时间(6小时或60小时)?(是的，我知道这取决于负载，但假设这是一个小型企业环境)

6 个解决方案

#1

You can do it in chunks. For example, every 10 seconds execute:

你可以用块来做。例如，每10秒执行一次:

delete from activities where activityid in 
  (select top 1000 activityid from activities where unt_uid is null)

Obviously define the row count (I arbitrarily picked 1000) and interval (I picked 10 seconds) which makes the most sense for your application.

显然，定义行计数(我任意选择了1000)和interval(我选择了10秒)，这对您的应用程序最有意义。

6 个解决方案

#1

更多相关文章

随机推荐