Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 3 down vote favorite
1

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.

share|improve this question
2  
Maybe you should consider whether you are over-using indexed views, rather than whether SQL Server should change its behavior to suit your scenario? 10 views with 20 indexes for a single table? Perhaps you are doing far too much reporting in your OLTP database. – Aaron Bertrand Nov 28 '12 at 15:34
2  
Is it possible for you to just use non-materialized views. This will keep you from having your inserts and updates from being incredibly slow and if your table is indexed properly you should still see pretty speedy returns on your queries. – Zane Nov 28 '12 at 15:49
Aaron, completely agree with you. It is over-use of indexed views. The idea was to eliminate joins for super-fast queries, but we are not ready to pay write time for that. I have put the idea aside and trying to find smth more suitable for requirement: very-fast query/sort on about 1 to 50 properties on a collection over 10m records with frequent updates... – Denis Nov 29 '12 at 10:10
Zane, so main idea of my research is eliminating joins. We suppose them to slow-down queries/sorts. There are about 3 tables with 10m records and 3 more tables with 1m. – Denis Nov 29 '12 at 10:21

migrated from stackoverflow.com Nov 28 '12 at 15:20

1 Answer

up vote 9 down vote accepted

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

share|improve this answer
They say Oracle can do that, so I am looking for the same functionality in MS SQL – Denis Nov 28 '12 at 12:42
3  
@Denis: yes, Oracle can but SQL Server can't. Personally, I don't want to use out of date views. And I've seen issues with Oracle backends for apps that have deferred-update materialised views, it's quite counter intuitive in my opinion – gbn Nov 28 '12 at 12:44
4  
@Denis Oracle MVs really aren't that similar to MS SQL indexed views—what they have in common is skin-deep. – Jack Douglas♦ Nov 28 '12 at 13:02
1  
@Denis are you checking things off of some master feature list? Or have you actually used the feature in Oracle? – Aaron Bertrand Nov 28 '12 at 15:21
2  
@AlexKuznetsov "stale data is OK" sounds a lot like the type of justification I see a lot for NOLOCK. Yes it is ok sometimes, but you must be fully aware of - and OK with - the potential consequences. – Aaron Bertrand Nov 28 '12 at 16:35
show 5 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.