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