You are currently offline, waiting for your internet to reconnect

BUG: The query does not use the indexed view in SQL Server 2005 Enterprise Edition

BUG #: 414902 (SQLBUDT)
BUG #: 416347 (SQLBUDT)
SYMPTOMS
When you define an indexed view that involves an ISNULL function on a nonnullable column in Microsoft SQL Server 2005 Enterprise Edition, the query does not use the indexed view. The query does not use the indexed view even if the predicate in the query is identical to the predicate in the indexed view. SQL Server 2005 Enterprise Edition creates an execution plan for the query to access the base tables instead of taking advantage of the indexed view.
CAUSE
This problem occurs because SQL Server 2005 Enterprise Edition automatically removes the ISNULL function on the nonnullable column from the query. Therefore, the SQL Server 2005 Enterprise Edition optimizer cannot match the ISNULL function from the view definition to the query where the unnecessary ISNULL function was removed.
WORKAROUND
To work around this problem, you can drop the view, rewrite the view definition without using the ISNULL function on the nonnullable column, and then re-create the index. This lets SQL Server 2005 Enterprise Edition match the indexed view to the query.

Note that this does not change the semantics of the view because only the nonnullable column is affected.

The following sample code provides a workaround for this problem.

Note Before you run the following sample code, you must run the code in the "More information" section to set up the working environment. Otherwise, the following sample code does not work.
USE AdventureWorksDWGO--Remove the view if it exists.IF EXISTS (   SELECT *   FROM INFORMATION_SCHEMA.VIEWS   WHERE TABLE_NAME = 'VProdQty')DROP VIEW VProdQtyGOCREATE VIEW VProdQty WITH SCHEMABINDING ASSELECT ProductKey, SUM(OrderQuantity) qty, COUNT_BIG(*) cntFROM dbo.factResellerSalesGROUP BY ProductKeyGO-- Re-create the index.CREATE UNIQUE CLUSTERED INDEX VProdQty_idx ON VProdQty(ProductKey)GO-- The query now uses the indexed view.SELECT ProductKey, SUM(ISNULL(OrderQuantity, 0)) qtyFROM dbo.factResellerSales GROUP BY ProductKey ORDER BY qty DESCGO
STATUS
Microsoft has confirmed that this is a bug in the Microsoft products that are listed in the "Applies to" section.
MORE INFORMATION
You can display the exact execution plan that SQL Server 2005 Enterprise Edition applies to the query on the Execution Plan tab. To do this, click Include Actual Execution Plan on the Query menu.

Steps to reproduce the problem

Run the following code in SQL Server 2005 Enterprise Edition. Note that the query that is created does not automatically match the indexed view.
USE AdventureWorksDWGOALTER TABLE factresellersales ALTER COLUMN Orderquantity smallint NOT NULLGOCREATE VIEW VProdQty WITH SCHEMABINDING ASSELECT ProductKey, SUM(ISNULL(OrderQuantity,0)) qty, COUNT_BIG(*) cntFROM dbo.factResellerSalesGROUP BY ProductKeyGOCREATE UNIQUE CLUSTERED INDEX VProdQty_idx ON VProdQty(ProductKey)GO-- The following query does not automatically match the indexed view.-- Instead, the query plan uses the base table.SELECT ProductKey, SUM(ISNULL(OrderQuantity, 0)) qtyFROM dbo.factResellerSales GROUP BY ProductKey ORDER BY qty DESCGO
Properties

Article ID: 912389 - Last Review: 01/11/2006 16:59:12 - Revision: 1.1

  • Microsoft SQL Server 2005 Enterprise Edition
  • kbsql2005tsql kbbug KB912389
Feedback