Search code examples
sqlsql-servertransactionsindexed-view

Do Indexed Views Update During a Transaction?

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?

Solution

  • 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.