如何跟踪 SQL 更新的进度? [英] How can you track the progress of a SQL update?

问题描述

假设我有一个更新，例如:

Let's say I have an update such as:

  UPDATE [db1].[sc1].[tb1] 
  SET c1 = LEFT(c1, LEN(c1)-1) 
  WHERE c1 like '%:'

这个更新基本上会遍历数百万行并在 c1 列中有一个冒号时修剪冒号.

This update is basically going to go through millions of rows and trim the colon if there is one in the c1 column.

我如何跟踪表格中的进展情况?

How can I track how far along in the table this has progressed?

谢谢

这是 sql server 2008

This is sql server 2008

推荐答案

数据库查询，尤其是数据操作语言 (DML)，是原子的.这意味着 INSERT/UPDATE/DELETE 要么成功发生，要么没有.没有办法查看正在处理的记录——对于数据库，一旦在 UPDATE 之后发出 COMMIT，它们都已更改.即使您能够查看处理中的记录，当您看到该值时，查询也会继续进行到其他记录.

Database queries, particularly Data Manipulation Language (DML), are atomic. That means that the INSERT/UPDATE/DELETE either successfully occurs, or it doesn't. There's no means to see what record is being processed -- to the database, they all had been changed once the COMMIT is issued after the UPDATE. Even if you were able to view the records in process, by the time you would see the value, the query will have progressed on to other records.

了解进程中的哪个位置的唯一方法是编写在循环中发生的查询的脚本，因此您可以使用计数器来了解处理了多少.这样做很常见，因此会定期提交大型数据集，以最大限度地降低需要重新运行整个查询的失败风险.

The only means to knowing where in the process is to script the query to occur within a loop, so you can use a counter to know how many are processed. It's common to do this so large data sets are periodically committed, to minimize the risk of failure requiring having to run the entire query over again.

这篇关于如何跟踪 SQL 更新的进度?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！