If you’ve got indexed views, or are thinking of using them, this is important.

First, go read the blog post, “An Indexed View Bug with Scalar Aggregates” by Paul White. It will take a minute, but it’s important to understand.

Paul shows a scenario where the data in a certain kind of indexed view can become out of sync with the base tables. You can still query the indexed view and return data using the demo code on his blog– but it’s incorrect data.

He mentions that you can identify the problem with DBCC CHECKDB or DBCC CHECKTABLE, but there’s something that’s not obvious: your regularly scheduled CHECKDB jobs are probably not going to identify if your indexed views are corrupted in this way.

CHECKDB doesn’t run EXTENDED_LOGICAL_CHECKS by default anymore

If you are using compatibility level 100 or higher (SQL Server 2008+), CHECKDB won’t validate the indexed view data against the base tables unless you specifically request it to do the extra work.

Here, I’ll prove it. I used the code in Paul White’s post to reproduce the bug on a SQL Server 2014 database. And then I ran plain old CHECKDB:

But as soon as I ran it with EXTENDED_LOGICAL_CHECKS, the world wasn’t so rosy:

The same thing is true of DBCC CHECKTABLE. I only find out about the issue if I do logical checks when running it against the indexed view:

Don’t Assume CHECKDB Will Catch Everything

It doesn’t run every possible check by default.

If you use indexed views and you suspect you might be impacted by this bug, it might be time for some EXTENDED_LOGICAL_CHECKS. But be careful, Books Online warns that it doesn’t run this by default anymore because “Running this query can have a very high effect on performance, and its progress cannot be tracked.” For sensitive environments, you can restore a full backup to another server and run CHECKDB against the restored database.