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

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

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