COUNT_BIG в индексированном виде

CREATE TABLE test2 (
id INTEGER,
name VARCHAR(10),
family VARCHAR(10),
amount INTEGER)

CREATE VIEW dbo.test2_v WITH SCHEMABINDING 
AS
SELECT id, SUM(amount) as amount
-- , COUNT_BIG(*) as tmp
FROM dbo.test2 
GROUP BY id

CREATE UNIQUE CLUSTERED INDEX vIdx ON test2_v(id)

У меня есть ошибка с этим кодом:

Невозможно создать индекс в представлении 'test.dbo.test2_v', потому что его выбор список не включает надлежащее использование COUNT_BIG. Рассмотрите возможность добавления COUNT_BIG (*) для выбора списка.

Я могу создать такой вид:

CREATE VIEW dbo.test2_v WITH SCHEMABINDING 
    AS
    SELECT id, SUM(amount) as amount, COUNT_BIG(*) as tmp
    FROM dbo.test2 
    GROUP BY id

Но мне просто интересно, какая цель этого столбца в этом случае?

Ответ 1

В этом случае вам нужен COUNT_BIG из-за того, что вы используете GROUP BY.

Это одно из многих ограничений индексированных представлений, и из-за этих ограничений индексированные представления не могут использоваться во многих местах или использование их НЕ так эффективно, как могло бы быть. К сожалению, сейчас это работает. Отстой, это сужает область использования.

http://technet.microsoft.com/en-us/library/cc917715.aspx

Ответ 2

Похоже, это просто жестко связанное с производительностью ограничение, которое команда SQL Server должна была внедрить, когда они сначала разработали агрегированные индексированные представления в SQL Server 2000.

До недавнего времени вы могли видеть это в документации по техническому обслуживанию SQL 2000 по адресу http://msdn.microsoft.com/en-us/library/aa902643(SQL.80).aspx, но документация SQL Server 2000 определенно удалена, Вы все равно можете загрузить файл в формате 92 МБ и найти соответствующие заметки на страницах 1146 и 2190: https://www.microsoft.com/en-us/download/details.aspx?id=51958

Объяснение этого ограничения можно найти на сайте SQLAuthority - фактически выдержка из книги Ицика Бен-Гана "Внутри SQL": http://blog.sqlauthority.com/2010/09/21/sql-server-count-not-allowed-but-count_big-allowed-limitation-of-the-view-5/

Стоит отметить, что Oracle имеет такое же ограничение/требование по тем же причинам (для эквивалентного быстро обновляемого материализованного представления); см. http://rwijk.blogspot.com.es/2009/06/fast-refreshable-materialized-view.html для обсуждения этой темы.

Резюме объяснения:

Почему серверу sql логически нужен материализованный столбец глобального подсчета в индексированном сводном представлении?
- Чтобы он мог быстро проверить/узнать, нужно ли изменять или перемещать конкретную строку в сводном представлении, когда указанная строка базовой таблицы обновляется или удаляется.
Почему этот столбец count должен быть COUNT_BIG(*)?
- Чтобы не было возможного риска переполнения; заставляя использовать тип данных bigint, нет риска индексированного представления "взломать", когда конкретная строка достигает слишком высокого значения.

Сравнительно легко понять, почему подсчет имеет решающее значение для эффективного обслуживания агрегатного представления - представьте себе следующую ситуацию:

Структуры таблиц указаны в вопросе

В базовой таблице есть 4 строки:

ID  | name | family | amount
--- | ---- | ------ | ------
1   | a    |        | 10    
2   | b    |        | 11    
2   | c    |        | 12    
3   | d    |        | 13

Совокупное представление материализуется примерно так:
```
ID  | amount | tmp
--- | ------ | ---
1   | 10     | 1
2   | 23     | 2
3   | 13     | 1
```
Простой случай:
- SQL Engine обнаруживает изменение базовых данных - третья строка в исходных данных (id 2, имя c) удаляется.
- Двигатель должен:
  - найти и обновить соответствующую строку совокупного материализованного представления
  - уменьшить сумму суммы на сумму удаленной базовой строки.
  - уменьшить "счет" на 1 (если этот столбец существует)
Целевой/сложный случай:
- SQL Engine обнаруживает другое изменение в базовых данных - вторая строка в исходных данных (id 2, имя b) удаляется.
- Двигатель должен:
  - найдите и удалите соответствующую строку совокупного материализованного представления, так как больше нет исходных строк с одним и тем же ключом группировки
Считайте, что в двигателе всегда есть строка "до" таблицы (-ов) в окне просмотра-обновления - она точно знает, что изменилось в обоих случаях.
Заметный "шаг" в алгоритме материализованного просмотра - , определяющий необходимость удаления конечной материализованной совокупной строки или нет
- Если у вас есть "счет", вам не нужно искать нигде за пределами целевой строки - если вы отбрасываете счетчик до 0, а затем удалите строку. Если вы обновляете любое другое значение, оставьте строку.
- Если у вас нет счета, то единственный способ понять его - запросить базовую таблицу для проверки любых других строк с тем же ключом агрегации; такой процесс явно приведет к гораздо более обременительным ограничениям:
  - это будет неявно медленнее, а
  - в случаях объединения-объединения будет не оптимизироваться!

По этим причинам существование столбца count (*) является фундаментальным требованием реализации совокупного материализованного представления. Без столбца count (*), ведение реального времени совокупного материализованного представления перед лицом базовых изменений данных будет иметь неприемлемо высокую производительность!

Вы все равно можете спросить: "Почему SQL Server не создает или не поддерживает такой столбец столбцов для меня автоматически, когда я создаю обобщенный материализованный вид?" - У меня нет особого ответа на это. В конце концов, я думаю, будет больше вопросов и путаницы в отношении "Почему у моего агрегированного материализованного представления есть столбец BIGCOUNT, если я не добавлял его?" если они это сделали, то проще сделать это основным требованием создания объекта, но это чисто субъективное мнение.

Ответ 3

Я знаю, что эта ветка немного устарела, но для тех, у кого до сих пор есть этот вопрос, http://technet.microsoft.com/en-us/library/ms191432%28v=sql.105%29.aspx говорит об индексированных представлениях

Оператор SELECT в представлении не может содержать следующие синтаксические элементы Transact-SQL:

Агрегированные функции AVG, MAX, MIN, STDEV, STDEVP, VAR или VARP. Если AVG(expression) указывается в запросах, ссылающихся на индексированное представление, оптимизатор может часто вычислять необходимый результат, если список выбора вида содержит SUM(expression) и COUNT_BIG(expression). Например, индексный список SELECT не может содержать выражение AVG(column1). Если в представлении SELECT содержатся выражения SUM(column1) и COUNT_BIG(column1), SQL Server может вычислять среднее значение для запроса, ссылающегося на представление, и указывает AVG(column1).