THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Page Free Space

See also my SQL Server technical articles on

Incorrect Results with Indexed Views

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:

CREATE TABLE dbo.Parent 
    parent_id integer IDENTITY NOT NULL,
    value varchar(20) NOT NULL,
    CONSTRAINT PK_Parent_id 
        PRIMARY KEY CLUSTERED (parent_id)
    child_id integer IDENTITY NOT NULL,
    parent_id integer NOT NULL,
    CONSTRAINT PK_Child_id 
        PRIMARY KEY CLUSTERED (child_id)

And a few rows of data:

INSERT dbo.Child (parent_id)
SELECT New.parent_id 
    INSERT Parent 
    OUTPUT inserted.parent_id 
    ) AS New;

The tables now look like this (parent first):

Original Data

We can now add the required FOREIGN KEY relationship:

FOREIGN KEY (parent_id)
REFERENCES dbo.Parent (parent_id);

Next, a very simple indexed view that joins the two tables (the view could contain more complex features like aggregates):

CREATE VIEW dbo.ParentsAndChildren
FROM dbo.Parent AS p
JOIN dbo.Child AS c ON 
    c.parent_id = p.parent_id;
ON dbo.ParentsAndChildren (child_id);

The final step is to use a MERGE statement to make some changes to the Parent table:

    parent_id integer PRIMARY KEY, 
    value varchar(20) NOT NULL
INSERT @ParentMerge
    (parent_id, value)
    (1, 'Kiwi Fruit'),
    (4, 'Dragon Fruit');
MERGE dbo.Parent AS p
USING @ParentMerge AS s ON 
    s.parent_id = p.parent_id
    UPDATE SET value = s.value
    INSERT (value) VALUES (s.value)
    deleted.value AS old_value, 
    inserted.value AS new_value;

This MERGE performs two actions:

  1. Updates the value column of parent row 1 from Apple to Kiwi Fruit
  2. 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):

MERGE changes

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:

SELECT * FROM dbo.Parent AS p;
SELECT * FROM dbo.Child AS c;

Updated Base Table Data

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):

FROM dbo.ParentsAndChildren 

Incorrect indexed view data

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:

FROM dbo.ParentsAndChildren

Expand Views Hint

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:

-- Enterprise Edition ONLY
FROM dbo.ParentsAndChildren;

Indexed View Matching

Perhaps adding a child row to match the new parent row 4 will somehow fix things up?

INSERT dbo.Child (parent_id) VALUES (4);
SELECT * FROM dbo.ParentsAndChildren WITH (NOEXPAND);

After Insert

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:

DBCC CHECKTABLE (ParentsAndChildren);

DBCC output 1

Unless we use the EXTENDED_LOGICAL_CHECKS option:


DBCC output 2

The damage is repairable:


DBCC repair

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.


For the MERGE statement above, the query optimizer builds a plan that does not update the indexed view (click to enlarge):

Incorrect Update Plan

In a version of SQL Server with the fix applied, the same MERGE statement produces a plan that does maintain the indexed view:

Correct Update Plan

The plan operators used to keep the view index in step with the base tables are highlighted. Without these operators, the changes to the base table are not correctly written to any indexes defined on the view. The root cause is related to the same simplification that allows the optimizer to remove the reference to the Parent table in this query:

FROM dbo.Parent AS p 
JOIN dbo.Child AS c ON 
    c.parent_id = p.parent_id;

Simplification Example Plan

The FOREIGN KEY relationship and NOT NULL constraints on the referencing column together mean that the join to Parent cannot affect the result of the query, so the join is simplified away. In SQL Server 2012, we can see when this simplification is performed because the following message appears when undocumented trace flags 8619 and 3604 are enabled during compilation:

Trace Flag 8619 output

The same message is emitted when a MERGE statement contains a WHEN MATCHED THEN UPDATE clause and either a WHEN NOT MATCHED THEN INSERT or WHEN MATCHED … THEN DELETE clause. These conditions combine such that the optimizer incorrectly concludes that a table reference can be removed, when in fact it is needed later on when the update side of the plan is built.

Other details of the query and database can affect whether the simplification can be misapplied. For example, if the FOREIGN KEY constraint contains an ON DELETE CASCADE clause, and the MERGE contains a DELETE clause, the simplification is not performed, the TF 8619 message does not appear, and the bug does not manifest.

The key to determining whether a particular query is vulnerable to this bug (TF 8619 aside) is to check whether the query plan includes operators to maintain the indexed view. At a minimum, you should see a update operator for the view:

View Update Operator

SQL Sentry Plan Explorer identifies the operator as applying to a view explicitly, in SSMS you need to click on the graphical operator and inspect the Properties window.


The updated conditions for incorrect results are:

  • An indexed view that joins tables
  • Two of the tables have a single-column FOREIGN KEY constraint
  • A MERGE statement contains an UPDATE action that affects one of the tables
  • The MERGE statement also contains an INSERT or DELETE action (or both)
  • The optimizer applies a simplification that removes a table reference based on the FK relationship and other metadata
  • As a result, the MERGE execution plan does not contain the operators necessary to correctly maintain the indexed view
  • A subsequent query plan accesses an index on the view, either explicitly or via indexed-view matching (Enterprise Edition)


  • The simplification is not applied in tempdb
  • The simplification is not applied to multi-column foreign key constraints

Under these conditions, the indexes on the view do not reflect the state of the base tables and incorrect results are returned. Once the hot fix is applied, the optimizer does not misapply the simplification so the correct indexed view maintenance features are built into execution plans.

Update: I am adding this based on Ian Yates’ great question in the comments: my expectation is that applying this hotfix will not remove any existing indexed view corruption. You would need to test the hotfix as usual, apply it, and then either rebuild all affected indexed views manually, or run DBCC CHECKDB with the EXTENDED_LOGICAL_CHECKS option (which could take a long time).

© 2013 Paul White – All Rights Reserved


Twitter: @SQL_Kiwi

Published Wednesday, February 6, 2013 11:18 AM by Paul White

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS



jamiet said:

Paul, you are on another planet. Awesome post - great detective work!

February 5, 2013 5:32 PM

Rob Farley said:

I keep meaning to find the time to update that Join Simplification post. It's been a lot of years since I wrote about it, and I don't even tend to give that talk any more either.

February 5, 2013 5:35 PM

Paul White said:

@Jamie You're welcome, thanks for tweeting about the problem - that got me interested!

@Rob I always think of your post whenever this simplification thing comes up. One of my all-time favourites, as you know.

February 5, 2013 5:41 PM

Boris Hristov said:

Is it only me that can see this:

"Updates the value column of parent row 1 from Apple to Kiwi Fruit

Adds a new parent row 4 for Dragon Fruit"

in the first screenshot of what data is inside the table dbo.Parents. I cannot see anywhere a record for Apple.

Paul, am I correct?

February 6, 2013 12:34 AM

Paul White said:

Hi Boris,

No it wasn't just you. The T-SQL script was correct, but I must have pasted the wrong screenshot at some stage. Corrected now, thanks for pointing that out.


February 6, 2013 1:31 AM

SSIS Junkie said:

Three days ago at my current gig we stumbled across a problem where use of the NOEXPAND query hint was

February 6, 2013 4:01 AM

tobi said:

The MERGE statement strikes again.

February 6, 2013 4:53 PM

AlexK said:

This is awesome research, Paul!

I guess you could do your research in a fraction of the time if the source code was available, so that you could just debug right through. If that were the case, we might have fixed this problem already. Peer reviews are very good for product quality.

I'm using MERGE less and less. It has too many problems. I am not sure we know them all. This does not smell good. I do not want my users to discover yet another bug in MERGE, which will ruin their day and my weekend or vacation. In some cases a DELETE followed by an INSERT even performs better.

February 7, 2013 12:46 PM

Paul White said:

Tobi and Alex,

Yes, it's all rather sad. Every time I revisit MERGE and begin to warm to it, another problem comes up. Many of the past bugs have been edge cases that not too many people would encounter, but this one requires only foreign keys and an indexed view - not an uncommon combination at all!


February 7, 2013 1:18 PM

AlexK said:


I am not with you on the following assessment: "edge cases that not too many people would encounter". These problems constitute a smell: they indicate that there might be something wrong with the overall quality of the feature.

If we observed similar problems in an open source system, where we could access the code and automated tests, quite likely we would find that the full matrix of possible automated test cases is either missing altogether or incomplete.

We could build a complete matrix of test cases, fix the exposed problems, and use the fixed feature with confidence.

Beta testers that cannot read source code cannot ensure high quality, even if there are millions of them. For example, if the code does not work on the last day of a leap year, and we are beta-testing now, in 2013, we will not notice that defect no matter how many people test the black box. The bug would manifest itself on Dec 31st, 2016.

On the other hand, developers can and do find such problems, and peer reviews can and do expose such things by just reading the code. In many cases this is just so very much more efficient than testing, or blindly trusting, a black box.

February 7, 2013 5:53 PM

Ian Yates said:

Out of curiosity, if I have a corrupted table - and am unaware of it - and then apply this patch, does the corruption get fixed for me or do I still need to run the DBCC repair statement?

February 8, 2013 6:33 AM

Anonymous said:

I don't think indexed views are very common. They're so limiting, and cause so many side issues, that I think they drive a lot of people away.

That said, agreed wholeheartedly with Alex, this is an indicator that the entire feature was badly designed. And it really makes no sense from an outsider perspective.

If you imagine how you might implement an index update, there would be some central method (overloaded, naturally), and all the updates would go through there. It would control things like subsequent updates to indexed views, etc. Enforcement of constraints, perhaps. There would be no way around these things, because it would all be centrally handled. There would be no choice.

But that's clearly not how this feature has been implemented, and it's scary to imagine the spaghetti code that must exist in core parts of the engine. What other bugs are lying in wait?

February 8, 2013 12:03 PM

Paul White said:

Hi Ian,

That's a great question! I haven't tried it myself, yet, but my expectation is that the fix only prevents future corruptions caused by an incorrect update plan. I would be amazed if it did more than that (but I have been wrong before). That's another detail missing from the KB :)

The safest thing to do (it seems to me) is to test and apply the hotfix, and then either rebuild all indexed views manually or run DBCC CHECKDB with the EXTENDED_LOGICAL_CHECKS option - something which could take quite a long time, incidentally.


February 8, 2013 12:37 PM

tobi said:


I wish indexed views had less limitations (like LEFT JOINs being unsupported) and could be stacked. That would be very powerful in OLTP. Joins without runtime cost, just disk, buffer pool and DML cost.

February 9, 2013 7:58 AM

Paul White said:

I doubt we will ever see indexed views that can be stacked, or which allow outer joins, despite the fact that Microsoft Research have published papers showing that they have built code for this and tested it in SQL Server.

There might be other reasons, but I suspect Microsoft see more general technologies like column-store (for DW) and Hekaton (for OLTP) as being better solutions, in that they are more general and perhaps easier to implement. There's a bit of text about Hekaton here:

February 10, 2013 3:23 PM

tobi said:

The public link to the paper is broken. Here is an alternate one I discovered:

February 10, 2013 5:21 PM

Paul White said:

Thanks, Tobi!

February 16, 2013 4:46 AM

Lynx said:

Thank you Paul,

December 18, 2014 7:05 AM

Vladimir Moldovanenko said:

March 19, 2015 11:49 AM

Paul White said:

Thanks, Vladimir. That's an issue I have seen before. The view contains SUM on a nullable expression, which is illegal and ought to produce error 8662 when the clustered index is created on the view. Workaround by adding "ELSE 0" to each summed CASE expression to make the expression non-nullable.

March 20, 2015 12:30 AM

Vladimir Moldovanenko said:

Paul, thanks for finding time to look at it so quickly.

"ELSE 0" seems to fix it for DELETE and UPDATE but check out INSERT. On SQL 2014 I get all NULLs after it.

Thanks a lot for your idea to try.

March 20, 2015 8:40 AM

Paul White said:

I see the issue now. The view you are trying to index is essentially a manual PIVOT query implemented using scalar aggregates. The PIVOT operator is not allowed in indexed views because the work hasn't been done to make it work with the delta update logic for indexed views.

Ideally, SQL Server would recognise views that are semantically equivalent to a PIVOT and refuse to create an index, but I imagine that could be difficult. It certainly doesn't detect such problematic views today, hence the buggy behaviour.

Anyway, that seems to me to be the root cause of your problems. I would rewrite the view as a normal (not pivoted) GROUP BY on mlSetting, index that view, then layer a non-indexed pivoting view over the indexed view, if required.

March 21, 2015 7:51 AM

Vladimir Moldovanenko said:

Paul, thank you very much for your time and your input.

My intent was not to use 'illegal' SQL. My view is in accordance to the currently published SQL BOL indexed view requirements and restrictions, per every letter of it.  I didn’t not use PIVOT as per restriction. I used GROUP BY. I know that MS implements PIVOT(subset of GROUP BY) using GROUP BY but GROUP BY is not restricted, as GROUP BY is basis for aggregate indexed views subclass. I know that you are one of the TOP SQL EXPERTs in the world and you know this very well. Still I do want to draw this distinction between PIVOT and GROUP BY, as I see it.

So IMHO MS needs to either a) not support aggregated views, b) fully support all aspects of aggregated view c) describe all caveats precisely and completely. One of these was not done. And that is where the bug is IMHO. I would like MS to deal with it.

In my specific case your new workaround idea will not work.  If I use regular indexed view and then GROUP BY with CASE to pivot data, CASE statement will end up in my predicates as expression and loose cardinality, killing my queries.

I work for ERP software company and we make software for factories. Factories make products for consumers. At any time factory has active data set related to customer orders that needs to be made. Bills of Material data set for customer orders can be huge. So query to consider active data set uses this view, for example, very trivialized query

SELECT ori.olnID

FROM dbo.OrderItems ori

CROSS JOIN dbo.vwCoreMilestoneValues m WITH(NOEXPAND) -- millions and millions of rows

WHERE ori.oriIsPurchased = 0 AND ori.itmpID = 2 -- Make to Order manufacturing items

AND ori.sttValue >= m.InitialOrderItemStatusValue -- between Initial status

AND ori.sttValue < m.CompleteWorkOrderItemStatusValue -- and Complete status. These items need to be made for customer. Active set is maybe 5 - 10 % items

Because view is aggregated and pivoted, with statistics on each column, SQL Server can create effective query plans for such queries. If InitialOrderItemStatusValue = CASE WHEN … then expression is in WHERE clause and results are not good.

Practical workarounds for me are a) fixed table with pivoted columns, with trigger maintaining it on data changes b) recreating CLUSTERED index on this view with trigger (rare data change).

Ideally I would like to see MS implement proper handling of this but that is unlikely to happen.

THANK YOU again for taking interest in this issue, providing your valuable feedback and being such great help/contributor to SQL community at large.

Vladimir Moldovanenko, Sr. Database Architect/Analyst,

March 21, 2015 11:14 AM

Vladimir Moldovanenko said:

By the way I like indexed views and I am happy MS implemented them, even with these bugs. :)

They are particularly great for seldom updated base data that is needed for queries that read often.

March 21, 2015 11:22 AM

Paul White said:

Yes, I understand. The point about comparing with PIVOT was to explain a bit about *why* you are encountering problems. Certainly, the syntax is "legal", and ought to work as you expect (once the nullable expression in the SUM is corrected, anyway). I didn't mean to suggest you were doing anything "wrong".

The workarounds I suggested might not quite get you to where you need to be, but they might assist others viewing the same Connect item in future.

It's always difficult to assess whether a particular Connect item will result in a fix or not. Wrong results bugs do usually get a higher priority, but it depends on a lot of factors - not least how the initial report is triaged.

Connect is not really the best place to report production issues; you should open a case with CSS. They are best placed to investigate, identify the root cause, and work with engineering on a possible fix. There may be costs involved going this route, depending on your support agreement and relationship with Microsoft.

March 21, 2015 9:46 PM

Leave a Comment

Privacy Statement