Article ID: 912389 - View products that this article applies to.
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.
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 problemRun the following code in SQL Server 2005 Enterprise Edition. Note that the query that is created does not automatically match the indexed view.
Article ID: 912389 - Last Review: January 11, 2006 - Revision: 1.1