SQL 为什么我会导致聚集索引更新？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/895879/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Why am I causing a Clustered Index Update?
提问by Jesse Hallam
I'm executing the following statement:
我正在执行以下语句：
UPDATE TOP(1) dbo.userAccountInfo
SET Flags = Flags | @AddValue
WHERE ID = @ID;
The column 'ID' is an INT PRIMARY KEY with IDENTITY constraints. Flags is a BIGINT NOT NULL.
列“ID”是具有 IDENTITY 约束的 INT PRIMARY KEY。标志是一个 BIGINT NOT NULL。
The execution path indicates that a Clustered Index Update is occurring. A very expensive operation. There's no indexes covering Flags or ID, except for the primary key. I feel like the actual execution path should be:
执行路径指示正在发生聚集索引更新。非常昂贵的手术。除了主键外，没有涵盖标志或 ID 的索引。我觉得实际的执行路径应该是：
Clustered Index Seek => Update
聚集索引查找 => 更新
回答by Remus Rusanu
Tables come in two flavors: clustered indexes and heaps. You have a PRIMARY KEY constraint so you have created implicitly a clustered index. You'd have to go to extra length during the table create for this notto happen. Any update of the 'table' is an update of the clustered index, since the clustered index isthe table. As for the clustered index update being a 'very expensive operation', now that is an urban legend surrounding basic misinformation about how a database works. The correct statement is 'a clustered index update that affects the clustered key has to update the all non-clustered indexes'.
表有两种形式：聚集索引和堆。您有一个 PRIMARY KEY 约束，因此您隐式地创建了一个聚集索引。您必须在表创建期间增加额外的长度，以免发生这种情况。“表”的任何更新都是聚集索引的更新，因为聚集索引就是表。至于聚集索引更新是一项“非常昂贵的操作”，现在这是一个关于数据库如何工作的基本错误信息的都市传说。正确的说法是“影响聚集键的聚集索引更新必须更新所有非聚集索引”。
回答by BradC
The clustered index isthe physical table, so whenever you update anyrow, you're updating the clustered index.
聚集索引是物理表，因此无论何时更新任何行，都会更新聚集索引。
请参阅此 MSDN 文章
