Do Indexed Views Update During a Transaction?

Question

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?

Answer 1

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.

Answer 2

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