THE SQL Server Blog Spot on the Web

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

Jamie Thomson

This is the blog of Jamie Thomson, a freelance data mangler in London

NOEXPAND query hint returns wrong results – CU fix now available

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.
http://msdn.microsoft.com/en-gb/library/ms181714.aspx

This screenshot demonstrates the problem:

image

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:

  • 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.
In this scenario, you receive an incorrect result.

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!

@Jamiet

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.

Published Monday, February 04, 2013 11:24 AM by jamiet

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

Comments

No Comments

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement