snippetsqlModerate
How to update views outside of update/insert transaction in SQL Server
Viewed 0 times
updateinsertsqlviewsoutsidetransactionhowserver
Problem
Looking at SQL Server indexed views I see that these views are updated within a transaction that updates dependent tables. I wonder if there is a way around it?
There will be about 10 views with 20 indexes that depend on the same table. I don't want to wait for updating views and indexes on views when I do an update on dependent table. I want to be sure that tables are updated within a transaction and views are updated after in some kind of a daemon mode.
There will be about 10 views with 20 indexes that depend on the same table. I don't want to wait for updating views and indexes on views when I do an update on dependent table. I want to be sure that tables are updated within a transaction and views are updated after in some kind of a daemon mode.
Solution
No, there isn't. The C (Consistency) in ACID dictates that the dependent (indexed) views are updated.
If you want to workaround this, you'd need to build views on a separate table that is updated on demand in your own time. Say via Service Broker or scheduled
If you want to workaround this, you'd need to build views on a separate table that is updated on demand in your own time. Say via Service Broker or scheduled
Context
StackExchange Database Administrators Q#29434, answer score: 10
Revisions (0)
No revisions yet.