Microsoft KB Archive/912389

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

Article ID: 912389

Article Last Modified on 1/11/2006

-

APPLIES TO


 * Microsoft 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 &quot;More information&quot; section to set up the working environment. Otherwise, the following sample code does not work. USE AdventureWorksDW GO

--Remove the view if it exists. IF EXISTS (  SELECT *   FROM INFORMATION_SCHEMA.VIEWS   WHERE TABLE_NAME = 'VProdQty') DROP VIEW VProdQty GO

CREATE VIEW VProdQty WITH SCHEMABINDING AS SELECT ProductKey, SUM(OrderQuantity) qty, COUNT_BIG(*) cnt FROM dbo.factResellerSales GROUP BY ProductKey GO

-- 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)) qty FROM dbo.factResellerSales GROUP BY ProductKey ORDER BY qty DESC GO



STATUS
Microsoft has confirmed that this is a bug in the Microsoft products that are listed in the &quot;Applies to&quot; 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 AdventureWorksDW GO

ALTER TABLE factresellersales ALTER COLUMN Orderquantity smallint NOT NULL GO

CREATE VIEW VProdQty WITH SCHEMABINDING AS SELECT ProductKey, SUM(ISNULL(OrderQuantity,0)) qty, COUNT_BIG(*) cnt FROM dbo.factResellerSales GROUP BY ProductKey GO

CREATE 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)) qty FROM dbo.factResellerSales GROUP BY ProductKey ORDER BY qty DESC GO

Keywords: kbbug kbsql2005tsql KB912389

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.