Three days ago at my current gig we stumbled across a problem where use of the NOEXPAND query hint was causing different results compared to the same query without NOEXPAND. For those that do not know (which, until three days ago, included me) NOEXPAND governs the use of indexed views:
The indexed view is not expanded only if the view is directly referenced in the SELECT part of the query and WITH (NOEXPAND) or WITH (NOEXPAND, INDEX(index_value [ ,...n ] ) ) is specified.
This screenshot demonstrates the problem:
Clearly this isn’t good. I put a comment out on Twitter hoping someone knew something about it and thankfully Gonçalo Ferreira was reading. He pointed me toward a Knowledge Base article entitled FIX: Incorrect result is returned when you query an indexed view by using the NOEXPAND hint in SQL Server 2008, in SQL Server 2008 R2 or in SQL Server 2012 that confirmed this as being a bug in SQL Server. The article is dated 24th January 2013 (that is only 11 days ago folks) and describes the bug rearing its head under these circumstances:
In this scenario, you receive an incorrect result.
- You create an indexed view for two tables that have a foreign key reference in Microsoft SQL Server 2008, in Microsoft SQL Server 2012 or in Microsoft SQL Server 2008 R2.
- You update the base tables of the indexed view.
- You run a query against the indexed view that uses the NOEXPAND hint.
I don’t know about you but I read that as “If you’re using Indexed Views with NOEXPAND, they’re most likely giving you the wrong answer”. The latest cumulative update (CU) for:
- SQL Server 2012
- SQL Server 2012 SP1
- SQL Server 2008 R2 SP2
- SQL Server 2008 R2 SP1
- SQL Server 2008 SP3
fixes the problem. If you’re using indexed views you may want to install the CU, sharpish!
UPDATE. Paul White has posted a fantastic blog post where he explains how to reproduce the problem: Incorrect Results with Indexed Views (N.B. It occurs if you are using the MERGE statement) and Aaron Bertrand has followed with the equally readable If you are using indexed views and MERGE, please read this! where he offers some considered opinions and advice surrounding the issue. I highly recommend reading both.