Summary: If you use MERGE, indexed views and foreign keys, your queries can return incorrect results.
Microsoft have released a fix for incorrect results returned when querying an indexed view. The problem applies to:
SQL Server 2012
SQL Server 2008 R2
SQL Server 2008
The Knowledge Base article does not go into much detail, or provide a reproduction script, but this blog entry does :)
The KB does say that reproducing the bug requires these features:
An indexed view on two tables that have a foreign key relationship
An update performed against the base tables
A query executed against the indexed view using a NOEXPAND hint
There are two important details I would like to add right away:
The NOEXPAND hint is not required to reproduce the bug on Enterprise Edition
The update must be performed by a MERGE statement
The fix is available in the following cumulative update packages:
Cumulative Update 2 for SQL Server 2012 SP1 [build 11.0.3339]
Cumulative Update 5 for SQL Server 2012 RTM [build 11.0.2395]
Cumulative Update 4 for SQL Server 2008 R2 SP2 [build 10.50.4270]
Cumulative Update 10 for SQL Server 2008 R2 SP1 [build 10.50.2868]
Cumulative Update 8 for SQL Server 2008 SP3 [build 10.00.5828]
No service pack contains this fix, you must apply one of the hotfix packages above.
Steps to Reproduce
The first thing we will need is two tables:
And a few rows of data:
The tables now look like this (parent first):
We can now add the required FOREIGN KEY relationship:
Next, a very simple indexed view that joins the two tables (the view could contain more complex features like aggregates):
The final step is to use a MERGE statement to make some changes to the Parent table:
This MERGE performs two actions:
Updates the value column of parent row 1 from Apple to Kiwi Fruit
Adds a new parent row 4 for Dragon Fruit
The statement includes an OUTPUT clause to show the changes it makes (this is not required for the repro):
This confirms that the changes have been made as we requested: parent row 1 has changed; and row 4 has been added. The changes are reflected in the base tables:
As highlighted, row 1 has changed from Apple to Kiwi Fruit and row 4 has been added.
We do not expect to see row 4 in the indexed view because there are no child records for that row, and the indexed view uses an inner join. Checking the indexed view using the NOEXPAND table hint (required in non-Enterprise SKUs to use indexes on a view):
The results are incorrect. They show the old value of the data for parent row 1.
Now we try using the EXPAND VIEWS query hint to force SQL Server to access the base tables rather than reading view indexes:
This query produces correct results.
On SQL Server Enterprise Edition, the optimizer chooses whether to access the indexed view or the base tables. For following query, without any hints, the optimizer chooses not to expand the view. It reads the view index and produces incorrect results:
Perhaps adding a child row to match the new parent row 4 will somehow fix things up?
No. The query plan that accesses the view index still returns an incorrect value for row 1. It seems MERGE has corrupted our indexed view.
Analysis using DBCC CHECKTABLE
Checking the view with DBCC CHECKTABLE returns no errors:
Unless we use the EXTENDED_LOGICAL_CHECKS option:
The damage is repairable:
You probably do not want to set your database to SINGLE_USER mode and run a DBCC repair after every MERGE statement, however. We could also rebuild the indexed view’s clustered index manually, of course.
Cause
For the MERGE statement above, the query optimizer builds a plan that does not update the indexed view (click to enlarge):
In a version of SQL Server with the fix applied, the same MERGE statement produ