1

I know that Express edition of SQL Server 2005 does not include materialized views (with schemabinding) automatically while estimating optimal execution plan. However when the view is queried directly - i still get underyling table displayed in actual execution plan. Why isn't my clustered index created on materialized view used in the query?

Code for view is:

CREATE VIEW [dbo].[athObjectNames] WITH SCHEMABINDING
AS
SELECT     convert(nvarchar(440),oTextValue) as oTextValue, oObjectID, oTypeID, oVersion
FROM         dbo.athObjects
WHERE     (oTypeID < 100)

And an unique clustered index is created on oTextValue columns.

However when I execute a query such as:

select * from dbo.athObjectNames
where oTextValue = 'Alibabki'

...i see that underlying table (dbo.athObjects) is used in execution plan with condition (oTypeID < 100). There is no index on oTextValue column of athObjects table. Even when I change the condition to "< 'Alibabki'" still index has no use.

Thanks in advance for help.

Dustin Laine
  • 37,935
  • 10
  • 86
  • 125
Kuba
  • 11
  • 1

1 Answers1

1

OK, I've found that "with (noexpand)" hint should be used to force optimizer to use materialized view. Maybe this will help others. :-)

Kuba
  • 11
  • 1