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?

Holmen

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.

Doerrer

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

Begird

Answer 3

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.

Fonz