SQL 2005 Express 不在显式查询中使用物化视图

发布于 2024-08-19 21:46:56 字数 671 浏览 2 评论 0原文

我知道 SQL Server 2005 Express 版本在估计最佳执行计划时不会自动包含物化视图（带有架构绑定）。但是，当直接查询视图时 - 我仍然会在实际执行计划中显示底层表。为什么我的在物化视图上创建的聚集索引没有在查询中使用？

视图的代码是：

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

并且在 oTextValue 列上创建唯一的聚集索引。

但是，当我执行以下查询时：

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

...我看到基础表 (dbo.athObjects) 在执行计划中使用，条件为 (oTypeID < 100)。 athObjects 表的 oTextValue 列上没有索引。即使我将条件更改为“<'Alibabki'”，索引仍然没有用。

预先感谢您的帮助。

原文

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.

分享到QQ

分享到微博