使用CTE为视图编制索引 [英] Indexing views with a CTE

问题描述

因此，我刚刚发现SQL Server 2008不允许您在定义中使用CTE索引视图，但允许您更改查询到在视图定义中添加具有模式绑定。是否有充分的理由呢？出于某种我不知道的原因是否有意义？我的印象是 SCHEMABINDING 的主要目的是允许您为视图编制索引

So, I just found out that SQL Server 2008 doesn't let you index a view with a CTE in the definition, but it allows you to alter the query to add with schemabinding in the view definition. Is there a good reason for this? Does it make sense for some reason I am unaware of? I was under the impression that WITH SCHEMABINDINGs main purpose was to allow you to index a view

新的和改进的

;with x
as
(
    select   rx.pat_id
            ,rx.drug_class
            ,count(*) as counts
            from rx
            group by rx.pat_id,rx.drug_class

)
select   x.pat_id
        ,x.drug_class
        ,x.counts
        ,SUM(c.std_cost) as [Healthcare Costs]
    from x
    inner join claims as c
    on claims.pat_id=x.pat_id
    group by x.pat_id,x.drug_class,x.counts

和用于创建索引的代码

create unique clustered index [TestIndexName] on [dbo].[MyView]
( pat_id asc, drug_class asc, counts asc)

推荐答案

1. 您无法使用CTE为视图编制索引。即使视图可以具有 SCHEMABINDING 。这样想吧。为了索引视图，它必须满足两个条件（以及许多其他条件）：（a）已创建 SCHEMABINDING 和（b）它不包含CTE。为了对视图进行模式绑定，不需要不需要满足它不包含CTE的条件。

1. You can't index a view with a CTE. Even though the view can have SCHEMABINDING. Think of it this way. In order to index a view, it must meet two conditions (and many others): (a) that it has been created WITH SCHEMABINDING and (b) that it does not contain a CTE. In order to schemabind a view, it does not need to meet the condition that it does not contain a CTE.

I'我不认为在某些情况下视图具有CTE 会从索引中受益。这是您实际问题的外围，但是我的直觉是您正在尝试对该视图编制索引以神奇地使其更快。索引视图不一定比对基表的查询要快-有一定的限制，只有在特定的用例才有意义。请注意，不要盲目地将所有视图编入索引，这是一个神奇的快点按钮。还请记住，索引视图需要维护。因此，它将增加工作负载中影响基表的所有DML操作的成本。

I'm not convinced there is a scenario where a view has a CTE and will benefit from being indexed. This is peripheral to your actual question, but my instinct is that you are trying to index this view to magically make it faster. An indexed view isn't necessarily going to be any faster than a query against the base tables - there are restrictions for a reason, and there are only particular use cases where they make sense. Please be careful to not just blindly index all of your views as a magic "go faster" button. Also remember that an indexed view requires maintenance. So it will increase the cost of any and all DML operations in your workload that affect the base table(s).

模式绑定不是 仅用于索引视图。也可以在$ UDB之类的东西上使用
来帮助说服确定性，可以在
视图和函数上使用
来防止对基础架构的更改，并且在某些情况下
可以提高性能（例如，当UDF是
而不是架构绑定的时，优化器可能必须创建一个表假脱机来处理
来处理任何基础DDL更改。因此，请不要认为您可以对视图进行架构绑定但无法将其编入索引是
。
索引视图是必需的，但是关系不是相互的。

Schemabinding is not just for indexing views. It can also be used on things like UDFs to help persuade determinism, can be used on views and functions to prevent changes to the underlying schema, and in some cases it can improve performance (for example, when a UDF is not schema-bound, the optimizer may have to create a table spool to handle any underlying DDL changes). So please don't think that it is weird that you can schema-bind a view but you can't index it. Indexing a view requires it, but the relationship is not mutual.

---

对于您的特定情况，我建议这样做：

---

For your specific scenario, I recommend this:

CREATE VIEW dbo.PatClassCounts
WITH SCHEMABINDING
AS
  SELECT pat_id, drug_class, 
      COUNT_BIG(*) AS counts
    FROM dbo.rx
    GROUP BY pat_id, drug_class;
GO
CREATE UNIQUE CLUSTERED INDEX ON dbo.PatClassCounts(pat_id, drug_class);
GO
CREATE VIEW dbo.ClaimSums
WITH SCHEMABINDING
AS
  SELECT pat_id, 
    SUM(c.std_cost) AS [Healthcare Costs], 
    COUNT_BIG(*) AS counts
  FROM dbo.claims
  GROUP BY pat_id;
GO
CREATE UNIQUE CLUSTERED INDEX ON dbo.ClaimSums(pat_id);
GO

现在，您可以创建一个非索引视图，它们只是在这两个视图之间进行联接两个索引视图，它将利用索引（您可能需要在较低版本上使用 NOEXPAND ，不确定）：

Now you can create a non-indexed view that just does a join between these two indexed views, and it will utilize the indexes (you may have to use NOEXPAND on a lower edition, not sure):

CREATE VIEW dbo.OriginalViewName
WITH SCHEMABINDING
AS
    SELECT p.pat_id, p.drug_class, p.counts, c.[Healthcare Costs]
      FROM dbo.PatClassCounts AS p
      INNER JOIN dbo.ClaimSums AS c
      ON p.pat_id = c.pat_id;
GO

现在，所有这些都假定值得对这些信息进行预汇总-如果您不经常运行此查询，但是对数据进行了很多修改，则最好不要创建索引视图。

Now, this all assumes that it is worthwhile to pre-aggregate this information - if you run this query infrequently, but the data is modified a lot, it may be better to NOT create indexed views.

还要注意， ClaimSums SUM（std_cost）对于每个 pat_id + drug_class 组合，c>视图将相同，因为它仅合计为 pat_id 。我猜在 claims 表中可能会有一个 drug_class ，它也应该成为联接条件的一部分，但是我我不确定。如果是这样，我认为可以将其折叠为一个索引视图。

Also note that the SUM(std_cost) from the ClaimSums view will be the same for every pat_id + drug_class combination, since it's only aggregated to pat_id. I guess there might be a drug_class in the claims table that should be part of the join criteria too, but I'm not sure. If that is the case, I think this could be collapsed to a single indexed view.

这篇关于使用CTE为视图编制索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！