Индексирование представлений с помощью CTE

Question

Итак, я только что узнал, что SQL Server 2008 не позволяет индексировать представление с помощью CTE в определении, но позволяет alter запрос для добавления with schemabinding в определении зрения. Есть ли для этого веская причина? Имеет ли смысл по какой-то причине, о которой я не знаю? У меня сложилось впечатление, что WITH SCHEMABINDINGОсновной целью было дать вам возможность индексировать представление

новый и улучшенный с большим количеством запросов

;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)

Answer 1

1. Вы не можете индексировать представление с помощью CTE. Хотя вид может иметь SCHEMABINDING, Подумай об этом так. Чтобы проиндексировать представление, оно должно соответствовать двум (и многим другим) условиям: (а) чтобы оно было создано WITH SCHEMABINDING и (b) что он не содержит CTE. Чтобы связать схему с представлением, оно не должно удовлетворять условию, что оно не содержит CTE.

2. Я не уверен, что есть сценарий, в котором у представления есть CTE, и он получит выгоду от индексации. Это второстепенный вопрос к вашему актуальному вопросу, но мой инстинкт заключается в том, что вы пытаетесь проиндексировать это представление, чтобы волшебным образом ускорить его. Индексированное представление не обязательно будет выполняться быстрее, чем запрос к базовым таблицам - существуют ограничения по определенной причине, и есть только конкретные случаи использования, в которых они имеют смысл. Пожалуйста, будьте осторожны, чтобы не просто вслепую проиндексировать все ваши взгляды как волшебную кнопку "быстрее". Также помните, что индексированное представление требует обслуживания. Таким образом, это увеличит стоимость любых операций DML в вашей рабочей нагрузке, которые влияют на базовые таблицы.

3. Связывание схемы не только для индексирования представлений. Его также можно использовать в таких вещах, как UDF, чтобы помочь убедить детерминизм, можно использовать в представлениях и функциях, чтобы предотвратить изменения в базовой схеме, а в некоторых случаях это может повысить производительность (например, когда UDF не привязан к схеме, оптимизатору, возможно, придется создать катушку таблицы, чтобы обрабатывать любые базовые изменения DDL). Поэтому, пожалуйста, не думайте, что это странно, что вы можете привязать схему к представлению, но не можете проиндексировать его. Индексация представления требует этого, но отношения не взаимны.

---

Для вашего конкретного сценария я рекомендую это:

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 в более низкой редакции, не уверен)

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

Теперь все это предполагает, что стоит предварительно агрегировать эту информацию - если вы выполняете этот запрос нечасто, но данные сильно изменяются, может быть, лучше НЕ создавать индексированные представления.

Также обратите внимание, что SUM(std_cost) от ClaimSums вид будет одинаковым для всех pat_id + drug_class комбинация, так как она только агрегируется в pat_id, Я думаю, что может быть drug_class в claims таблица, которая также должна быть частью критериев объединения, но я не уверен. Если это так, я думаю, что это может быть свернуто в одно индексированное представление.