Microsoft Knowledge Base Article
This article contents is Microsoft Copyrighted material.
©2005-©2007 Microsoft Corporation. All rights reserved.
Terms
of Use |
Trademarks
Article ID: 912389 - Last Review: January 11, 2006 - Revision: 1.1
BUG: The query does not use the indexed view in SQL Server 2005 Enterprise Edition
BUG #: 414902 (SQLBUDT)
BUG #: 416347 (SQLBUDT)
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.
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.
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
Microsoft has confirmed that this is a bug in the Microsoft products that are
listed in the "Applies to" section.
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
APPLIES TO
- Microsoft SQL Server 2005 Enterprise Edition
| kbsql2005tsql kbbug KB912389 |
Community Feedback System
Very often, it takes hours to solve a problem. Very often, you've looked high
and low, and have tried a lot of solutions. When you finally found it, chances
are, it was because someone else helped you. Here's your chance to give back.
Use our community feedback tool to let others know what worked for you and what
didn't.
Please also understand that the community feedback system is not warranted to be
correct, it's simply a system that we've built to let people try and help each
other. If something in a feedback response doesn't make sense to you, or you're
not comfortable making changes that the feedback talks about (like registry
edits), please consult a professional.
Thank you for using kbAlertz.com Feedback System.
-- Scott Cate