1

I just found out that a report I quickly threw together years ago has been the sole means of collecting millions of dollars, and there isn't anything being done to check if it is correct.

For performance reasons, the report makes heavy use of indexed views. This concerns me, since while I have used indexed views a lot, I tend not to use them for anything this critical.

Is it possible that the indexed views can fail to update or otherwise return information different from the data in the tables? How real of a risk is this? Is there a good SQL script I can run periodically to check for errors?

SchmitzIT
  • 9,227
  • 9
  • 65
  • 92
Brad
  • 1,360
  • 4
  • 18
  • 27
  • Think not of how reliable indexed views are. This may be your Superman III moment :) – Paul Alan Taylor Nov 05 '12 at 15:43
  • 1
    Your underlying data is much more likely to be incorrect than any indexed views lying on top of it. – Data Masseur Nov 05 '12 at 20:58
  • The above 2 comments are basically saying I shouldn't be asking this question. I think it is still relevant, even if other issues are more relevant. – Brad Nov 06 '12 at 17:19

1 Answers1

1

There is zero risk for inconsistency according to the docs.

In practice, you have to deal with product bugs. They are not a realistic concern.

Indexed view maintenance is based on the exact same mechanism that indexes are based on: They are updated as part of the DML query plan. I guess you wouldn't expect indexes to become corrupt so you should trust indexes views with about the same strength.

usr
  • 168,620
  • 35
  • 240
  • 369
  • According to the docs, I should expect indexes to become corrupt. There is a lot documented on the DBCC command and index corruption. – Brad Nov 06 '12 at 17:16
  • I posted looking for someone with more experience using indexed views in the real world, to see if anyone has dealt with this issue. I read the documentation before posting. – Brad Nov 06 '12 at 17:18
  • @Brad indexes should *never ever* become corrupt. `DBCC CHECKDB` is for fixing corruption due to hardware bugs and 3rd-party software bugs in emergency situations. Very rarely SQL Server bugs. Usually, when you notice corruption you go to your backups. `CHECKDB` repair is for emergencies where you cannot do that; Also, I *have* lots of experience with indexed views. It is hard to quantify that, of course. Let's say I use them extensively to pre-generate joins and keep summary tables.; It looks like you have evidence that indexed views are unreliable. Can you share that? – usr Nov 06 '12 at 17:50
  • I do not have evidence that indexed views are unreliable. I am very much concerned with hardware 'bugs', 3rd-party software bugs, emergency situations and very rare SQL Server bugs. If I were writing a video game, I would not hesitate to recommend them. If something has to be 100% reliable, I try to keep things as simple as possible and stick only with features I've used extensively for many years. Any time data is physically stored in 2 places, there is at least a theoretical chance of synchronization issues. – Brad Nov 06 '12 at 21:12
  • @Brad, I understand. They have been in the product since 2005. If you mistrust them, you have to mistrust indexes es well because they, too, are redundant and use the same update mechanism (look at the DML plan). I think it is impractical to mistrust these basic things.; On a meta-level this question might be unanswerable if you require strong evidence that indexed views are not buggy. Nobody can provide that.; Second meta-point: start mistrusting your code first because it is far more likely to be the cause for downtime or error than basic SQL Server features. Your time is spent better there. – usr Nov 06 '12 at 23:17