索引视图在事务期间更新吗？
假设我有一个 SQL Server 索引视图 vwIndexedView，它从两个表 tbTableOne 和 tbTableTwo 读取数据。
如果我对事务内的两个表之一执行操作，我的索引视图会发生什么？ 它是立即刷新，还是我必须在结果输入之前提交我的事务？
例如：
BEGIN TRANSACTION testTransaction
INSERT INTO tbTableOne VALUES ('1', '2')
SELECT * FROM vwIndexedView
COMMIT TRANSACTION
SELECT * FROM vwIndexedView
第一个 SELECT 的结果会与第二个不同吗？
Let's say I've got a SQL Server Indexed View, vwIndexedView, that reads from two tables, tbTableOne, and tbTableTwo.
If I perform an action on one of the two tables inside a transaction, what happens to my indexed view? Is it refreshed immediately, or do I have to commit my transaction before the results will feed into it?
For instance:
BEGIN TRANSACTION testTransaction
INSERT INTO tbTableOne VALUES ('1', '2')
SELECT * FROM vwIndexedView
COMMIT TRANSACTION
SELECT * FROM vwIndexedView
Would the results of the first SELECT be different than the second?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
索引或索引视图作为执行更新的 DML 语句的一部分立即更新。 在您的情况下，如果您分析 INSERT 的实际执行计划，您将看到它包含用于更新所有物理“分区”的运算符，并且您的索引视图就是此类分区之一。
An index or an indexed view is updated immediately as part of the DML statement doing the update. In your case if you analyze the actual execution plan of the INSERT you'll see that it contains operators for update of all physical 'partitions', and you indexed view is one of such partitions.
索引视图立即刷新，您可以查看执行计划并亲自查看。 这会导致大量的锁争用： 准备好放弃索引视图
Indexed views refresh immediately, you can have a look at the execution plan and see for yourself. This causes a lot of lock contention: be ready to drop your indexed view
这是一个集中竞争的桌子吗？ 通过锁定 IE 以进行事务中的插入，您是否会导致大量其他 spid 等待？
简单的答案是肯定的，视图将使用新值进行更新，但这会造成一些性能问题，如果您从其他位置插入这些基础表，您也应该考虑一下。
Is this a centrally contested table? I.E by locking it for the insert in the transaction are you going to be causing tons of other spids to wait?
The simple answer is yes, the views will update with the new values but this will create some performance headaches you should think about as well if there are other places that you insert into these underlying tables from.