从游标问题中更新聚集索引
我有一个跨越多个字段的聚集索引。这些字段之一是 CustomerID。我有一个从该表中读取数据的游标，然后更新 CustomerID。问题是它会导致无限循环。我假设当它更改 customerid 字段时，聚集索引会被修改，并且表会针对该索引进行重组。此修改似乎使我的光标无效，因此 FETCH NEXT FROM Cursor 永远不会到达末尾。
FETCH NEXT FROM AccountSoftwareRegCursor
INTO @CurrentAccountSoftwareRegUId
WHILE (@@FETCH_STATUS = 0)
BEGIN
UPDATE
Licensing.AccountSoftwareRegistration
SET
AccountUid = @ToAccountUid,
CompanyId = @ToCompanyId,
UpdatedBy = isnull(@UpdatedBy,'Asset Transfer'),
UpdatedByAccount = @UpdatedByAccount,
UpdatedOn = GETUTCDATE()
WHERE
AccountSoftwareRegUid = @CurrentAccountSoftwareRegUId
我可以发出任何命令来阻止表更新聚集索引的表，直到游标完成之后吗？
I have a clustered index that is over several fields. One of these fields is CustomerID. I have a cursor that reads from that table, and then updates the CustomerID. The problem is that it cause an infinite loop. I assume that when it changes the customerid field, the clustered index is modified and the table is restructured for the index. This modification seems to invalidate my cursor so the FETCH NEXT FROM Cursor never reaches the end.
FETCH NEXT FROM AccountSoftwareRegCursor
INTO @CurrentAccountSoftwareRegUId
WHILE (@@FETCH_STATUS = 0)
BEGIN
UPDATE
Licensing.AccountSoftwareRegistration
SET
AccountUid = @ToAccountUid,
CompanyId = @ToCompanyId,
UpdatedBy = isnull(@UpdatedBy,'Asset Transfer'),
UpdatedByAccount = @UpdatedByAccount,
UpdatedOn = GETUTCDATE()
WHERE
AccountSoftwareRegUid = @CurrentAccountSoftwareRegUId
Is there any command i can issue to stop the table from updating the table for the clustered index until after the cursor finishes?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
如果您无法重写此代码以消除光标，则需要将光标声明为 STATIC。
从声明光标文档中：
If you cannot rewrite this to eliminate the cursor, you'll want to declare your cursor as STATIC.
From the DECLARE CURSOR documentation:
除了将其更改为基于集合的操作而不是使用游标之外，具有正在更改的列的聚集索引应该是一个危险信号：
请查看 Microsoft 的聚集索引设计指南
聚集索引应为 独特、狭窄、静态且不断增加
In addition to changing this into a set-based operation instead of using a cursor, your clustered index having a column which is changing should be a red flag:
Please review Microsoft's Clustered Index Design Guidelines
The clustered index should be unique, narrow, static and ever-increasing