无法在View上创建一个CLUSTERED INDEX，因为我引用了同一个表两次，任何解决方法？ [英] Cannot create a CLUSTERED INDEX on a View because I'm referencing the same table twice, any workaround?

问题描述

我想创建一个索引视图，它集成了几个表中的数据，但是SQL Server抱怨错误信息：

I want to create an indexed view that integrates data from several tables, however SQL Server complains with the error message:

无法在视图上创建索引我的看法。视图包含dbo.Companies上的自我加入。

视图定义是这样的（简化版）：

The view definition is something like this (simplified version):

SELECT  T.Field1
      , T.Field2
      , P.CompanyName AS ProviderName
      , C.CompanyName AS CustomerName
      , T3.Field1
FROM dbo.Table1 T 
                  INNER JOIN dbo.Companies P ON T.ProviderId = T2.Id
                  INNER JOIN dbo.Companies C ON T.CustomerId = T2.Id
                  INNER JOIN dbo.Table3 ON T.Id = T3.Id

是这种情况有什么解决方法吗？我不想将两个表格中的公司表分割。

Is there any workaround for this case? I don't want to split the Companies table in two tables.

提前感谢。

推荐答案

您将无法解决此问题，索引视图必须符合创建索引视图。除其他外，外部和自身连接不受支持（从上到下第10个限制）。它归结为引擎能力，以便在更新基表时能够更新视图索引。

You won't be able to work around this, the indexed views must conform to certain restrictions enumerated in Creating Indexed Views. Among other things, outer and self joins are not supported (10th restriction from top to bottom). It boils down to the engine ability to be able to update the view index when the base table is updated.

不知道数据模型的所有细节，是吗确保索引视图是必需的，基表索引是否足够？

Not knowing exact all the details of your data model, are you sure that an indexed view is necessary and won't the base table indexes suffice?

这篇关于无法在View上创建一个CLUSTERED INDEX，因为我引用了同一个表两次，任何解决方法？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！