CREATE COLUMNSTORE INDEX (Transact-SQL)

Применимо к:SQL ServerБаза данных SQL AzureУправляемый экземпляр SQL AzureAzure Synapse AnalyticsAnalytics Platform System (PDW)База данных SQL в Microsoft Fabric

Преобразуйте таблицу rowstore в кластеризованный индекс columnstore или создайте некластеризованный индекс columnstore. Используйте индекс columnstore для эффективного выполнения оперативной аналитики в режиме реального времени в рабочей нагрузке OLTP или повышения производительности сжатия данных и выполнения запросов для рабочих нагрузок хранения данных.

Следуйте новым инструкциям в индексах columnstore , чтобы получить последние улучшения этой функции.

• В SQL Server 2022 (16.x) появились упорядоченные кластеризованные индексы columnstore. Дополнительные сведения см. в разделе CREATE COLUMNSTORE INDEX. Сведения о доступности упорядоченного columnstore-индекса см. в Индексах Columnstore: Обзор.

• Начиная с SQL Server 2016 (13.x), можно создать таблицу в виде кластеризованного индекса columnstore. Сначала необходимо создать таблицу rowstore, а затем преобразовать ее в кластеризованный индекс columnstore.

• Сведения о рекомендациях по проектированию индексов columnstore см. в руководстве по проектированию индексов Columnstore.

Соглашения о синтаксисе Transact-SQL

Синтаксис

Синтаксис базы данных SQL Azure и Управляемого экземпляра SQL Azure^:

-- Create a clustered columnstore index on disk-based table.
CREATE CLUSTERED COLUMNSTORE INDEX index_name
    ON { database_name.schema_name.table_name | schema_name.table_name | table_name }
    [ ORDER (column [ , ...n ] ) ]
    [ WITH ( <with_option> [ , ...n ] ) ]
    [ ON <on_option> ]
[ ; ]

-- Create a nonclustered columnstore index on a disk-based table.
CREATE [ NONCLUSTERED ]  COLUMNSTORE INDEX index_name
    ON { database_name.schema_name.table_name | schema_name.table_name | table_name }
        ( column  [ , ...n ] )
    [ ORDER (column [ , ...n ] ) ]
    [ WHERE <filter_expression> [ AND <filter_expression> ] ]
    [ WITH ( <with_option> [ , ...n ] ) ]
    [ ON <on_option> ]
[ ; ]

<with_option> ::=
      DROP_EXISTING = { ON | OFF } -- default is OFF
    | MAXDOP = max_degree_of_parallelism
    | ONLINE = { ON | OFF }
    | COMPRESSION_DELAY  = { 0 | delay [ MINUTES ] }
    | DATA_COMPRESSION = { COLUMNSTORE | COLUMNSTORE_ARCHIVE }
      [ ON PARTITIONS ( { partition_number_expression | range } [ , ...n ] ) ]

<on_option>::=
      partition_scheme_name ( column_name )
    | filegroup_name
    | "default"

<filter_expression> ::=
      column_name IN ( constant [ , ...n ]
    | column_name { IS | IS NOT | = | <> | != | > | >= | !> | < | <= | !< } constant )

Синтаксис для SQL Server:

-- Create a clustered columnstore index on disk-based table.
CREATE CLUSTERED COLUMNSTORE INDEX index_name
    ON { database_name.schema_name.table_name | schema_name.table_name | table_name }
    [ WITH ( <with_option> [ , ...n ] ) ]
    [ ORDER (column [ , ...n ] ) ]
    [ ON <on_option> ]
[ ; ]

-- Create a nonclustered columnstore index on a disk-based table.
CREATE [ NONCLUSTERED ]  COLUMNSTORE INDEX index_name
    ON { database_name.schema_name.table_name | schema_name.table_name | table_name }
        ( column  [ , ...n ] )
    [ ORDER (column [ , ...n ] ) ]
    [ WHERE <filter_expression> [ AND <filter_expression> ] ]
    [ WITH ( <with_option> [ , ...n ] ) ]
    [ ON <on_option> ]
[ ; ]

<with_option> ::=
      DROP_EXISTING = { ON | OFF } -- default is OFF
    | MAXDOP = max_degree_of_parallelism
    | ONLINE = { ON | OFF }
    | COMPRESSION_DELAY  = { 0 | delay [ MINUTES ] }
    | DATA_COMPRESSION = { COLUMNSTORE | COLUMNSTORE_ARCHIVE }
      [ ON PARTITIONS ( { partition_number_expression | range } [ , ...n ] ) ]

<on_option>::=
      partition_scheme_name ( column_name )
    | filegroup_name
    | "default"

<filter_expression> ::=
      column_name IN ( constant [ , ...n ]
    | column_name { IS | IS NOT | = | <> | != | > | >= | !> | < | <= | !< } constant )

Синтаксис для системы платформы Azure Synapse Analytics и Analytics (PDW):

CREATE CLUSTERED COLUMNSTORE INDEX index_name
    ON { database_name.schema_name.table_name | schema_name.table_name | table_name }
    [ ORDER ( column [ , ...n ] ) ]
    [ WITH ( DROP_EXISTING = { ON | OFF } ) ] -- default is OFF
[;]

Доступность версий

Некоторые параметры недоступны во всех версиях ядра СУБД. В следующей таблице показаны версии, когда параметры вводятся в кластеризованном хранилище столбцов и некластеризованных индексах columnstore:

Все параметры доступны в Базе данных SQL Azure и Управляемом экземпляре SQL Azure^AUTD.

Дополнительные сведения о доступности функций см. в статье "Новые возможности индексов columnstore".

Аргументы

СОЗДАНИЕ КЛАСТЕРИЗОВАННОГО ИНДЕКСА COLUMNSTORE

Создайте кластеризованный индекс columnstore, в котором все данные сжимаются и хранятся по столбцу. Индекс содержит все столбцы в таблице и сохраняет всю таблицу. Если существующая таблица является кучей или кластеризованным индексом, она преобразуется в кластеризованный индекс columnstore. Если таблица уже хранится в виде кластеризованного индекса columnstore, то существующий индекс удаляется и перестраивается.

Msg 35354, Level 16, State 1, Line 63, The statement failed because a clustered columnstore index cannot be created on a table enabled for Change Feed. Consider disabling Change Feed and then creating the clustered columnstore index.

index_name

Задает имя нового индекса.

Если в таблице уже есть кластеризованный индекс columnstore, можно указать то же имя, что и существующий индекс, или использовать параметр DROP EXISTING для указания нового имени.

ON [ database_name. [ schema_name ] . | schema_name . ] table_name

Указывает одно-, двух или трехкомпонентное имя таблицы, хранящееся в виде кластеризованного индекса columnstore. Если таблица является кучей или имеет кластеризованный индекс, таблица преобразуется из хранилища строк в columnstore. Если таблица уже является columnstore, эта инструкция перестроит кластеризованный индекс columnstore.

ORDER для кластеризованного columnstore

column_store_order_ordinal Используйте столбец в sys.index_columns, чтобы определить порядок столбцов для кластеризованного индекса columnstore. Упорядочение columnstore помогает устранить сегмент, особенно с строковыми данными. Дополнительные сведения см. в разделе "Настройка производительности" с упорядоченными индексами columnstore и индексами Columnstore. Руководство по проектированию.

Чтобы преобразовать в упорядоченный кластеризованный индекс columnstore, существующий индекс должен быть кластеризованным индексом columnstore. Использовать параметр DROP_EXISTING.

Типы данных бизнес-аналитики (максимальной длины) не могут быть ключом упорядоченного кластеризованного индекса columnstore.

При создании упорядоченного кластеризованного индекса columnstore используйте MAXDOP = 1 параметр для сортировки высокого качества в обмен на значительно большую продолжительность инструкции CREATE INDEX . Чтобы создать индекс как можно быстрее, не ограничивайте MAXDOP. Максимальное качество сжатия и сортировки может помочь запросам в индексе columnstore.

Сведения о доступности упорядоченного columnstore-индекса см. в Индексах Columnstore: Обзор.

Параметры WITH

DROP_EXISTING = [OFF] | НА

DROP_EXISTING = ON указывает, чтобы удалить существующий индекс и создать новый индекс columnstore.

CREATE CLUSTERED COLUMNSTORE INDEX cci ON Sales.OrderLines
WITH (DROP_EXISTING = ON);

Значение по умолчанию, DROP_EXISTING = OFF, ожидает, что имя индекса совпадает с существующим именем. Ошибка возникает, если указанное имя индекса уже существует.

MAXDOP = max_degree_of_parallelism

Этот параметр может переопределить существующую максимальную степень конфигурации сервера параллелизма во время операции индекса. Используйте MAXDOP, чтобы ограничить количество процессоров, используемых в параллельном выполнении плана. Максимальное значение — 64 процессора.

max_degree_of_parallelism значения могут быть:

• 1, что означает подавление параллельного создания плана.
• >1, что означает ограничение максимального количества процессоров, используемых в параллельной операции индекса, указанным числом или меньшем, на основе текущей системной рабочей нагрузки. Например, если MAXDOP = 4, количество используемых процессоров равно 4 или меньше.
• 0 (по умолчанию), что означает использование фактического числа процессоров или меньшего числа на основе текущей системной рабочей нагрузки.

CREATE CLUSTERED COLUMNSTORE INDEX cci ON Sales.OrderLines
WITH (MAXDOP = 2);

Дополнительные сведения см. в разделе "Конфигурация сервера": максимальная степень параллелизма и настройка параллельных операций с индексами.

COMPRESSION_DELAY = 0 | задержка [ МИНУТЫ ]

Для таблицы на основе диска задержка указывает минимальное количество минут, которые разностная группа строк в закрытом состоянии должна оставаться в разностной строковой группе. ЗАТЕМ SQL Server может сжать его в сжатые группы строк. Так как таблицы на основе дисков не отслеживают время вставки и обновления отдельных строк, SQL Server применяет задержку к разностным группам строк в закрытом состоянии.

Значение по умолчанию — 0 минут.

CREATE CLUSTERED COLUMNSTORE INDEX cci ON Sales.OrderLines
WITH ( COMPRESSION_DELAY = 10 MINUTES );

Рекомендации по использованию COMPRESSION_DELAY см. в статье "Начало работы с Columnstore" для оперативной аналитики в режиме реального времени.

DATA_COMPRESSION = COLUMNSTORE | COLUMNSTORE_ARCHIVE

Задает параметр сжатия данных для указанной таблицы, номера секций или диапазона секций. Существуют следующие варианты выбора.

• COLUMNSTORE — значение по умолчанию и указывает, чтобы сжаться с помощью наиболее эффективного сжатия columnstore. Этот вариант является типичным вариантом.
• COLUMNSTORE_ARCHIVE дополнительно сжимает таблицу или секцию до меньшего размера. Используйте этот вариант для таких ситуаций, как архивация, которая требует меньшего размера хранилища и может позволить себе больше времени для хранения и извлечения.

CREATE CLUSTERED COLUMNSTORE INDEX cci ON Sales.OrderLines
WITH ( DATA_COMPRESSION = COLUMNSTORE_ARCHIVE );

Дополнительные сведения о сжатии см. в разделе Сжатие данных.

ONLINE = [ON | OFF]

• ON указывает, что индекс columnstore остается в сети и доступен, пока создается новая копия индекса.
• OFF указывает, что индекс недоступен для использования во время создания новой копии.

CREATE CLUSTERED COLUMNSTORE INDEX cci ON Sales.OrderLines
WITH ( ONLINE = ON );

Параметры ON

С помощью этих параметров можно указать параметры хранения данных, такие как схема секционирования, определенная файловая группа или файловая группа по умолчанию. Если параметр ON не указан, индекс использует параметры секции или параметров файловой группы существующей таблицы.

partition_scheme_name ( column_name ) задает схему секционирования для таблицы. Схема секционирования уже должна существовать в базе данных. Сведения о создании схемы секционирования см. в разделе CREATE PARTITION SCHEME (Transact-SQL).

column_name указывает столбец, по которому секционирован индекс. Этот столбец должен соответствовать типу данных, длине и точности аргумента функции секционирования, которая partition_scheme_name используется.

filegroup_name указывает файловую группу для хранения кластеризованного индекса columnstore. Если расположение не указано и таблица не секционирована, индекс использует ту же файловую группу, что и базовая таблица или представление. Файловая группа уже должна существовать.

Чтобы создать индекс в файловой группе по умолчанию, используйте "default" или [default]. При указании "default"QUOTED_IDENTIFIER параметр должен быть ON для текущего сеанса. QUOTED_IDENTIFIER по умолчанию равен ON. Дополнительные сведения см. в статье SET QUOTED_IDENTIFIER (Transact-SQL).

CREATE [NONCLUSTERED] COLUMNSTORE INDEX

Создайте некластеризованный индекс columnstore в таблице rowstore, хранящейся в виде кучи или кластеризованного индекса. Индекс может иметь отфильтрованное условие и не должен включать все столбцы базовой таблицы. Индекс columnstore требует достаточно места для хранения копии данных. Вы можете обновить индекс и обновить его по мере изменения базовой таблицы. Некластеризованный индекс columnstore в кластеризованном индексе обеспечивает аналитику в режиме реального времени.

index_name

Указывает имя индекса. index_name должны быть уникальными в таблице, но не должны быть уникальными в базе данных. Имена индексов должны соответствовать правилам идентификаторов.

( столбец [ ,... n ] )

Указывает столбцы для хранения. Некластеризованный индекс columnstore ограничен 1024 столбцами.

Каждый столбец должен иметь поддерживаемый тип данных для индексов columnstore. Ознакомьтесь с ограничениями и ограничениями для списка поддерживаемых типов данных.

ON [ database_name. [ schema_name ] . | schema_name . ] table_name

Указывает одно-, двух или трехкомпонентное имя таблицы, содержащей индекс.

ORDER для некластеризованного columnstore

Столбцы, указанные в ORDER предложении для некластеризованного индекса columnstore, должны быть подмножеством ключевых столбцов индекса.

column_store_order_ordinal Используйте столбец в sys.index_columns, чтобы определить порядок столбцов для некластеризованного индекса columnstore. Упорядочение columnstore помогает устранить сегмент, особенно с строковыми данными. Дополнительные сведения см. в разделе "Настройка производительности" с упорядоченными индексами columnstore и индексами Columnstore. Руководство по проектированию. Рекомендации по проектированию и производительности в этих статьях обычно применяются как к кластеризованным, так и некластеризованным индексам columnstore.

Типы данных бизнес-аналитики (максимальной длины) не могут быть ключом упорядоченного некластеризованного индекса columnstore.

При создании упорядоченного некластеризованного индекса columnstore используйте MAXDOP = 1 параметры для сортировки высокого качества, в обмен на значительно большую продолжительность инструкции CREATE INDEX . Чтобы создать индекс как можно быстрее, не ограничивайте MAXDOPего. Максимальное качество сжатия и сортировки может помочь запросам в индексе columnstore.

Сведения о доступности упорядоченного columnstore индекса см. в разделе "Доступность упорядоченного columnstore индекса".

Параметры WITH

DROP_EXISTING = [OFF] | НА

DROP_EXISTING = ON Существующий индекс удаляется и перестраивается. Указанное имя индекса должно совпадать с существующим индексом; однако определение индекса можно изменить. Например, можно указать различные столбцы или параметры индекса.

DROP_EXISTING = OFF
Ошибка отображается, если указанное имя индекса уже существует. Тип индекса нельзя изменить с помощью DROP_EXISTING. В синтаксисе с обратной совместимостью WITH DROP_EXISTING эквивалентен WITH DROP_EXISTING = ON.

MAXDOP = max_degree_of_parallelism

Переопределяет конфигурацию сервера: максимальный уровень конфигурации параллелизма во время операции индекса. Используйте MAXDOP, чтобы ограничить количество процессоров, используемых в параллельном выполнении плана. Максимальное значение — 64 процессора.

max_degree_of_parallelism значения могут быть:

• 1, что означает подавление параллельного создания плана.
• >1, что означает ограничение максимального количества процессоров, используемых в параллельной операции индекса, указанным числом или меньшем, на основе текущей системной рабочей нагрузки. Например, если MAXDOP = 4, количество используемых процессоров равно 4 или меньше.
• 0 (по умолчанию), что означает использование фактического количества процессоров или меньшего числа на основе текущей системной рабочей нагрузки.

Дополнительные сведения см. в статье Настройка параллельных операций с индексами.

ONLINE = [ON | OFF]

• ON указывает, что индекс columnstore остается в сети и доступен, пока создается новая копия индекса.
• OFF указывает, что индекс недоступен для использования во время создания новой копии. В некластеризованном индексе базовая таблица остается доступной. Только некластеризованный индекс columnstore не используется для удовлетворения запросов до завершения нового индекса.

CREATE COLUMNSTORE INDEX ncci ON Sales.OrderLines (StockItemID, Quantity, UnitPrice, TaxRate)
WITH ( ONLINE = ON );

COMPRESSION_DELAY = 0 | задержка [ МИНУТЫ ]

Указывает нижняя граница о том, как долго строка должна оставаться в разностной строковой группе, прежде чем она имеет право на миграцию в сжатую группу строк. Например, можно сказать, что если строка не изменяется в течение 120 минут, эта строка может сжиматься в формат хранилища столбцов.

Для индекса columnstore в таблицах на основе дисков время вставки или обновления строки не отслеживается. Вместо этого время закрытия разностной группы строк используется в качестве прокси-сервера для строки. Длительность по умолчанию — 0 минут. Строка переносится в хранилище столбцов после того, как 1 миллион строк накапливается в разностной группе строк и помечается закрыто.

СЖАТИЕ_ДАННЫХ

Задает параметр сжатия данных для указанной таблицы, номера секций или диапазона секций. Применяется только к индексам columnstore, включая некластеризованные и кластеризованные. Существуют следующие варианты выбора.

• COLUMNSTORE — значение по умолчанию и указывает, чтобы сжаться с помощью наиболее эффективного сжатия columnstore. Этот вариант является типичным вариантом.
• COLUMNSTORE_ARCHIVE дополнительно сжимает таблицу или секцию до меньшего размера. Этот параметр можно использовать для архивации или для других ситуаций, требующих меньшего размера хранилища, и может позволить себе больше времени для хранения и извлечения.

Дополнительные сведения о сжатии см. в разделе Сжатие данных.

WHERE <filter_expression> [ AND <filter_expression> ]

Вызывается предикат фильтра, этот параметр указывает, какие строки следует включить в индекс. SQL Server создает отфильтрованную статистику по строкам данных в отфильтрованном индексе.

Предикат фильтра использует простую логику сравнения. Сравнения, использующие NULL литералы, не допускаются с операторами сравнения. IS NULL IS NOT NULL Вместо этого используйте операторы.

Ниже приведены некоторые примеры предикатов фильтра для Production.BillOfMaterials таблицы:

• WHERE StartDate > '20000101' AND EndDate <= '20000630'
• WHERE ComponentID IN (533, 324, 753)
• WHERE StartDate IN ('20000404', '20000905') AND EndDate IS NOT NULL

Рекомендации по отфильтрованным индексам см. в разделе "Создание отфильтрованных индексов".

Параметры ON

Следующие параметры указывают файловые группы, для которых создается индекс.

partition_scheme_name ( column_name )

Указывает схему секционирования, определяющую файловые группы, на которые сопоставляются секции секционированного индекса. Схема секционирования должна существовать в базе данных путем выполнения CREATE PARTITION SCHEME.

column_name указывает столбец, по которому секционирован индекс. Этот столбец должен соответствовать типу данных, длине и точности аргумента функции секционирования, которая partition_scheme_name используется. column_name не ограничивается столбцами в определении индекса. При секционировании индекса columnstore ядро СУБД добавляет столбец секционирования в качестве столбца индекса, если он еще не указан.

Если таблица секционирована, а partition_scheme_name или файловая группа не указаны, индекс помещается в ту же схему секционирования и использует тот же столбец секционирования, что и базовая таблица.

Индекс columnstore в секционированной таблице должен быть выровнен. Дополнительные сведения о секционировании индексов см. в разделах Секционированные таблицы и индексы.

filegroup_name

Указывает имя файловой группы, в которой создается индекс. Если filegroup_name не заданы и таблица не секционирована, индекс использует ту же файловую группу, что и базовая таблица. Файловая группа уже должна существовать.

по умолчанию

Создает указанный индекс в файловой группе по умолчанию.

Термин по умолчанию в этом контексте не является ключевым словом. Это идентификатор файловой группы по умолчанию и должен быть разделен как в ON "default" или ON [default]. Если "default" задано, параметр QUOTED_IDENTIFIER должен быть ВКЛЮЧЕН для текущего сеанса, который является параметром по умолчанию. Дополнительные сведения см. в разделе SET QUOTED_IDENTIFIER.

Разрешения

Требуется разрешение ALTER для таблицы.

Замечания

Индекс columnstore можно создать во временной таблице. При удалении таблицы или завершении сеанса индекс также удаляется.

В базе данных SQL Fabric таблицы с кластеризованными индексами columnstore не отражаются в Fabric OneLake.

Отфильтрованные индексы

Отфильтрованный индекс — это оптимизированный некластеризованный индекс, подходящий для запросов, которые выбирают небольшой процент строк из таблицы. Он использует предикат фильтра для индексирования части данных в таблице. Хорошо разработанный отфильтрованный индекс может повысить производительность запросов, сократить затраты на хранение и сократить затраты на обслуживание.

Обязательные параметры SET для отфильтрованных индексов

Параметры SET в столбце обязательных значений требуются при каждом из следующих условий:

• Вы создаете отфильтрованный индекс.
• Операция INSERT, UPDATE, DELETE или MERGE изменяет данные в отфильтрованном индексе.
• Оптимизатор запросов использует отфильтрованный индекс для создания плана запроса.

¹ Параметр ANSI_WARNINGS значение ON неявно устанавливает значение ARITHABORT в ON, если для уровня совместимости базы данных задано значение 90 или более поздней версии. Если для уровня совместимости базы данных задано значение 80 или более ранних версий, необходимо явно задать для параметра ARITHABORT значение ON.

Если параметры SET неверны, могут возникнуть следующие условия:

• Отфильтрованный индекс не создается.

• Ядро СУБД создает ошибку и откатывает инструкции INSERT, UPDATE, DELETE или MERGE, изменяющие данные в индексе.

• Оптимизатор запросов не учитывает индекс в плане выполнения для каких-либо операторов Transact-SQL.

Дополнительные сведения о отфильтрованных индексах см. в разделе "Создание отфильтрованных индексов".

Ограничения и запреты

Каждый столбец в индексе columnstore должен иметь один из следующих распространенных бизнес-типов данных:

• datetimeoffset [ ( n ) ]
• datetime2 [ ( n ) ]
• datetime
• smalldatetime
• дата
• time [ ( n ) ]
• float [ ( n ) ]
• real [ ( n ) ]
• decimal [ ( точность [ , шкала ] ) ]
• numeric [ ( точность [ , масштабирование ] ) ]
• деньги
• smallmoney
• bigint
• int
• смолинт
• tinyint
• бит
• nvarchar [ ( n ) ]
• nvarchar(max)¹
• nchar [ ( n ) ]
• varchar [ ( n ) ]
• varchar(max)¹
• char [ ( n ) ]
• varbinary [ ( n ) ]
• varbinary(max)¹
• binary [ ( n ) ]
• uniqueidentifier²

¹ Относится к SQL Server 2017 (14.x) и Базе данных SQL Azure на уровне "Премиум", "Стандартный" (S3 и выше) и ко всем уровням предложений виртуальных ядер только в кластеризованных индексах columnstore.

² Применимо к SQL Server 2014 (12.x) и более поздним версиям.

Если в базовой таблице есть столбец типа данных, который не поддерживается для индексов columnstore, необходимо исключить этот столбец из некластеризованного индекса columnstore.

Большие данные объекта (LOB), превышающие 8 килобайт, хранятся в автономном хранилище бизнес-объектов с указателем на физическое расположение, хранящееся в сегменте столбца. Размер хранимых данных не сообщается в sys.column_store_segments, sys.column_store_dictionaries или sys.dm_db_column_store_row_group_physical_stats.

Столбцы, использующие любой из следующих типов данных, не могут быть включены в индекс columnstore:

• ntext, text и image
• nvarchar(max), varchar(max), varbinary(max)¹
• rowversion (и метка времени)
• sql_variant
• Типы СРЕДЫ CLR (иерархия и пространственные типы)
• xml
• uniqueidentifier²

¹ Относится к SQL Server 2016 (13.x) и предыдущим версиям и некластеризованным индексам columnstore.

² относится к SQL Server 2012 (11.x).

Некластеризованные индексы columnstore:

• Не может содержать более 1024 столбцов.
• Невозможно создать как индекс на основе ограничений. Можно иметь уникальные ограничения, ограничения первичного ключа и ограничения внешнего ключа для таблицы с индексом columnstore. Ограничения всегда применяются с индексом хранилища строк. Ограничения не могут быть применены с индексом columnstore (кластеризованный или некластеризованный).
• Не удается включить разреженный столбец.
• Невозможно изменить с помощью инструкции ALTER INDEX. Чтобы изменить некластеризованный индекс, необходимо удалить и повторно создать индекс columnstore. С помощью ALTER INDEX можно отключить и перестроить индекс columnstore.
• Невозможно создать с помощью ключевого слова INCLUDE.
• Не удается указать ASC ключевые DESC слова в списке столбцов индекса. Индексы columnstore упорядочены в соответствии с алгоритмами сжатия.
• В Базе данных SQL Azure база данных SQL в Microsoft Fabric, Управляемом экземпляре SQL Azure^AUTD и предварительная версия SQL Server 2025 (17.x) можно упорядочить, включив ORDER предложение. Дополнительные сведения см. в разделе Настройка производительности с упорядоченными индексами columnstore.
• Не удается включить бизнес-столбцы типа nvarchar(max), varchar(max)и varbinary(max) в некластеризованные индексы columnstore. Только кластеризованные индексы columnstore поддерживают типы бизнес-объектов, начиная с версии SQL Server 2017 (14.x), базы данных SQL Azure (настроенной на уровне "Премиум", уровня "Стандартный" (S3 и выше) и всех уровней предложений виртуальных ядер). Предыдущие версии не поддерживают типы бизнес-объектов в кластеризованных и некластеризованных индексах columnstore.
• Начиная с SQL Server 2016 (13.x), можно создать некластеризованный индекс columnstore в индексизованном представлении.

Индексы Columnstore нельзя объединить со следующими функциями:

• вычисляемые столбцы. Начиная с SQL Server 2017 (14.x), кластеризованный индекс columnstore может содержать не сохраняемый вычисляемый столбец. Однако в SQL Server 2017 (14.x) кластеризованные индексы columnstore не могут содержать сохраненные вычисляемые столбцы, и нельзя создавать некластеризованные индексы в вычисляемых столбцах.
• Сжатие страниц и строк и формат хранилища vardecimal . (Индекс columnstore уже сжимается в другом формате.)
• Репликация с кластеризованными индексами columnstore. Поддерживаются некластеризованные индексы columnstore. Дополнительные сведения см. sp_addarticle.
• Файловый поток.

Курсоры или триггеры в таблице с кластеризованным индексом columnstore нельзя использовать. Это ограничение не применяется к некластеризованным индексам columnstore. Курсоры и триггеры можно использовать в таблице с некластеризованным индексом columnstore.

Определенные ограничения SQL Server 2014 (12.x):

Следующие ограничения применяются только к SQL Server 2014 (12.x). В этом выпуске можно использовать обновляемые кластеризованные индексы columnstore. Некластеризованные индексы columnstore по-прежнему доступны только для чтения.

• Отслеживание изменений. Отслеживание изменений нельзя использовать с индексами columnstore.
• Изменение записи данных. Эту функцию нельзя включить в таблицах с кластеризованным индексом columnstore. Начиная с SQL Server 2016 (13.x), запись измененных данных может быть включена в таблицах с некластеризованным индексом columnstore.
• Доступный для чтения дополнительный. Невозможно получить доступ к кластеризованному индексу columnstore (CCI) из доступной для чтения вторичной группы доступности AlwaysOn. Доступ к некластеризованному индексу columnstore (NCCI) можно получить из доступной для чтения вторичной.
• Несколько активных результирующих наборов (MARS). SQL Server 2014 (12.x) использует эту функцию для подключений только для чтения к таблицам с индексом columnstore. Однако SQL Server 2014 (12.x) не поддерживает эту функцию для параллельных операций языка обработки данных (DML) в таблице с индексом columnstore. При попытке использовать функцию для этой цели SQL Server завершает подключения и отменяет транзакции.
• Некластеризованные индексы columnstore нельзя создавать в представлении или индексном представлении.

Сведения о преимуществах производительности и ограничениях индексов columnstore см. в разделе "Обзор индексов Columnstore".

Метаданные

Все столбцы в индексе columnstore хранятся в метаданных как включенные столбцы. Индекс columnstore не имеет ключевых столбцов. Следующие системные представления предоставляют сведения об индексах columnstore:

• sys.indexes (Transact-SQL)
• sys.index_columns (Transact-SQL)
• sys.partitions (Transact-SQL)
• sys.column_store_segments (Transact-SQL)
• sys.column_store_dictionaries (Transact-SQL)
• sys.column_store_row_groups (Transact-SQL)

Примеры. Преобразование таблицы из rowstore в columnstore

А. Преобразование кучи в кластеризованный индекс columnstore

В этом примере создается таблица в виде кучи, а затем преобразует ее в кластеризованный индекс columnstore с именем cci_Simple. Создание кластеризованного индекса columnstore изменяет хранилище для всей таблицы из rowstore в columnstore.

CREATE TABLE dbo.SimpleTable(
    ProductKey [INT] NOT NULL,
    OrderDateKey [INT] NOT NULL,
    DueDateKey [INT] NOT NULL,
    ShipDateKey [INT] NOT NULL);
GO
CREATE CLUSTERED COLUMNSTORE INDEX cci_Simple ON dbo.SimpleTable;
GO

В. Преобразование кластеризованного индекса в кластеризованный индекс columnstore с тем же именем

В этом примере создается таблица с кластеризованным индексом, а затем демонстрируется синтаксис преобразования кластеризованного индекса в кластеризованный индекс columnstore. Создание кластеризованного индекса columnstore изменяет хранилище для всей таблицы из rowstore в columnstore.

CREATE TABLE dbo.SimpleTable2 (
    ProductKey [INT] NOT NULL,
    OrderDateKey [INT] NOT NULL,
    DueDateKey [INT] NOT NULL,
    ShipDateKey [INT] NOT NULL);
GO
CREATE CLUSTERED INDEX cl_simple ON dbo.SimpleTable2 (ProductKey);
GO
CREATE CLUSTERED COLUMNSTORE INDEX cl_simple ON dbo.SimpleTable2
WITH (DROP_EXISTING = ON);
GO

С. Обработка некластеризованных индексов при преобразовании таблицы rowstore в индекс columnstore

В этом примере показано, как обрабатывать некластеризованные индексы при преобразовании таблицы rowstore в индекс columnstore. Начиная с SQL Server 2016 (13.x), специальные действия не требуются. SQL Server автоматически определяет и перестраивает некластеризованные индексы на новом кластеризованном индексе columnstore.

Если вы хотите удалить некластеризованные индексы, используйте инструкцию DROP INDEX перед созданием индекса columnstore. Параметр DROP EXISTING удаляет только преобразованный кластеризованный индекс. Он не удаляет некластеризованные индексы.

В SQL Server 2012 (11.x) и SQL Server 2014 (12.x) невозможно создать некластеризованный индекс в индексе columnstore.

--Create the table for use with this example.
CREATE TABLE dbo.SimpleTable (
    ProductKey [INT] NOT NULL,
    OrderDateKey [INT] NOT NULL,
    DueDateKey [INT] NOT NULL,
    ShipDateKey [INT] NOT NULL);
GO
  
--Create two nonclustered indexes for use with this example
CREATE INDEX nc1_simple ON dbo.SimpleTable (OrderDateKey);
CREATE INDEX nc2_simple ON dbo.SimpleTable (DueDateKey);
GO

Только для SQL Server 2012 (11.x) и SQL Server 2014 (12.x) необходимо удалить некластеризованные индексы, чтобы создать индекс columnstore.

DROP INDEX dbo.SimpleTable.nc1_simple;
DROP INDEX dbo.SimpleTable.nc2_simple;
  
--Convert the rowstore table to a columnstore index.
CREATE CLUSTERED COLUMNSTORE INDEX cci_simple ON dbo.SimpleTable;
GO

Д. Преобразование большой таблицы фактов из rowstore в columnstore

В этом примере объясняется, как преобразовать большую таблицу фактов из таблицы rowstore в таблицу columnstore.

1. Создайте небольшую таблицу, используемую в этом примере.

   --Create a rowstore table with a clustered index and a nonclustered index.
   CREATE TABLE dbo.MyFactTable (
       ProductKey [INT] NOT NULL,
       OrderDateKey [INT] NOT NULL,
       DueDateKey [INT] NOT NULL,
       ShipDateKey [INT] NOT NULL
   INDEX IDX_CL_MyFactTable CLUSTERED  ( ProductKey )
   );
   
   --Add a nonclustered index.
   CREATE INDEX my_index ON dbo.MyFactTable ( ProductKey, OrderDateKey );
   

2. Удалите все некластеризованные индексы из таблицы rowstore. Возможно, потребуется выполнить скрипт для повторного создания индексов позже.

   --Drop all nonclustered indexes
   DROP INDEX my_index ON dbo.MyFactTable;
   

3. Преобразуйте таблицу rowstore в таблицу columnstore с кластеризованным индексом columnstore.

   Сначала найдите имя существующего кластеризованного индекса rowstore. На шаге 1 мы задали имя индекса IDX_CL_MyFactTable. Если имя индекса не было указано, он получил автоматически созданное уникальное имя индекса. Вы можете получить автоматически созданное имя с помощью следующего примера запроса:

   SELECT i.object_id, i.name, t.object_id, t.name
   FROM sys.indexes i
   INNER JOIN sys.tables t ON i.object_id = t.object_id
   WHERE i.type_desc = 'CLUSTERED'
   AND t.name = 'MyFactTable';
   

   Вариант 1. Удаление существующего кластеризованного индекса IDX_CL_MyFactTableи преобразование MyFactTable в columnstore. Измените имя нового кластеризованного индекса columnstore.

   --Drop the clustered rowstore index.
   DROP INDEX [IDX_CL_MyFactTable]
   ON dbo.MyFactTable;
   GO
   --Create a new clustered columnstore index with the name MyCCI.
   CREATE CLUSTERED COLUMNSTORE
   INDEX IDX_CCL_MyFactTable ON dbo.MyFactTable;
   GO
   

   Вариант 2. Преобразование в columnstore и повторное использование существующего имени кластеризованного индекса rowstore.

   --Create the clustered columnstore index,
   --replacing the existing rowstore clustered index of the same name
   CREATE CLUSTERED COLUMNSTORE
   INDEX [IDX_CL_MyFactTable]
   ON dbo.MyFactTable
   WITH (DROP_EXISTING = ON);
   

Е. Преобразование таблицы columnstore в таблицу rowstore с кластеризованным индексом

Чтобы преобразовать таблицу columnstore в таблицу rowstore с кластеризованным индексом, используйте инструкцию CREATE INDEX с параметром DROP_EXISTING.

CREATE CLUSTERED INDEX [IDX_CL_MyFactTable]
ON dbo.[MyFactTable] ( ProductKey )
WITH ( DROP_EXISTING = ON );

F. Преобразование таблицы columnstore в кучу rowstore

Чтобы преобразовать таблицу columnstore в кучу rowstore, удалите кластеризованный индекс columnstore. Обычно это не рекомендуется, но некоторые из них могут иметь узкое использование. Дополнительные сведения о кучах см. в статьях "Куча" (таблицы без кластеризованных индексов).

DROP INDEX [IDX_CL_MyFactTable]
ON dbo.[MyFactTable];

G. Дефрагментация путем реорганизации индекса columnstore

Существует два способа поддержания кластеризованного индекса columnstore. Начиная с SQL Server 2016 (13.x), используйте ALTER INDEX...REORGANIZE вместо ПЕРЕСТРОЕНИЯ. Дополнительные сведения см. в статье Columnstore index rowgroup. В предыдущих версиях SQL Server можно использовать CREATE CLUSTERED COLUMNSTORE INDEX с DROP_EXISTING=ON или ALTER INDEX (Transact-SQL) и параметрОМ ПЕРЕСТРОЕНИЯ. Оба метода достигли одинаковых результатов.

Начните с определения имени кластеризованного индекса columnstore в MyFactTable.

SELECT i.object_id, i.name, t.object_id, t.name
FROM sys.indexes i
INNER JOIN sys.tables t on i.object_id = t.object_id
WHERE i.type_desc = 'CLUSTERED COLUMNSTORE'
AND t.name = 'MyFactTable';

Удалите фрагментацию путем выполнения REORGANIZE в индексе columnstore.

--Rebuild the entire index by using ALTER INDEX and the REBUILD option.
ALTER INDEX IDX_CL_MyFactTable
ON dbo.[MyFactTable]
REORGANIZE;

Примеры некластеризованных индексов columnstore

А. Создание индекса columnstore в качестве вторичного индекса в таблице rowstore

В этом примере создается некластеризованный индекс columnstore в таблице rowstore. В этой ситуации можно создать только один индекс columnstore. Индекс columnstore требует дополнительного хранилища, так как он содержит копию данных в таблице rowstore. В этом примере создается простая таблица и кластеризованный индекс rowstore, а затем демонстрируется синтаксис создания некластеризованного индекса columnstore.

CREATE TABLE dbo.SimpleTable (
    ProductKey [INT] NOT NULL,
    OrderDateKey [INT] NOT NULL,
    DueDateKey [INT] NOT NULL,
    ShipDateKey [INT] NOT NULL);
GO

CREATE CLUSTERED INDEX cl_simple ON dbo.SimpleTable (ProductKey);
GO

CREATE NONCLUSTERED COLUMNSTORE INDEX csindx_simple
ON dbo.SimpleTable (OrderDateKey, DueDateKey, ShipDateKey);
GO

В. Создание базового некластеризованного индекса columnstore с помощью всех параметров

В следующем примере показано, как создать некластеризованный индекс columnstore в файловой группе DEFAULT, указав максимальные степени параллелизма (MAXDOP) как 2.

CREATE NONCLUSTERED COLUMNSTORE INDEX csindx_simple
ON SimpleTable (OrderDateKey, DueDateKey, ShipDateKey)
WITH (DROP_EXISTING =  ON,
    MAXDOP = 2)
ON "DEFAULT";
GO

С. Создание некластеризованного индекса columnstore с отфильтрованным предикатом

В следующем примере создается отфильтрованный некластеризованный индекс columnstore в Production.BillOfMaterials таблице в образце AdventureWorks2022 базы данных. Предикат фильтра может содержать столбцы, которые не являются ключевыми столбцами в отфильтрованном индексе. Предикат в этом примере выбирает только строки, в которых EndDate не равно NULL.

IF EXISTS (SELECT name FROM sys.indexes
    WHERE name = N'FIBillOfMaterialsWithEndDate'
    AND object_id = OBJECT_ID(N'Production.BillOfMaterials'))
DROP INDEX FIBillOfMaterialsWithEndDate
    ON Production.BillOfMaterials;
GO
CREATE NONCLUSTERED COLUMNSTORE INDEX "FIBillOfMaterialsWithEndDate"
    ON Production.BillOfMaterials (ComponentID, StartDate)
    WHERE EndDate IS NOT NULL;

Д. Изменение данных в некластеризованном индексе columnstore

Область применения: SQL Server 2012 (11.x) до SQL Server 2014 (12.x).

В SQL Server 2014 (12.x) и более ранних версиях после создания некластеризованного индекса columnstore в таблице нельзя напрямую изменять данные в этой таблице. Запрос с insert, UPDATE, DELETE или MERGE завершается ошибкой и возвращает сообщение об ошибке. Ниже приведены параметры, которые можно использовать для добавления или изменения данных в таблице:

• Отключите или удалите индекс columnstore. Затем можно обновить данные в таблице. При отключении колонностороннего индекса вы можете перестроить его после завершения обновления данных. Рассмотрим пример.

  ALTER INDEX mycolumnstoreindex ON dbo.mytable DISABLE;
  
  -- update the data in mytable as necessary
  ALTER INDEX mycolumnstoreindex on dbo.mytable REBUILD;
  

• Загрузите данные в промежуточную таблицу, которая не содержит индекс columnstore. Создайте индекс columnstore в промежуточной таблице. Переключите промежуточную таблицу в пустой раздел основной таблицы.

• Переключите секцию из таблицы с индексом columnstore в пустую промежуточную таблицу. Если в промежуточной таблице есть колоночный индекс, отключите колоночный индекс. Выполнение любых обновлений. Создайте (или перестройте) индекс columnstore. Переложите промежуточную таблицу в теперь пустой раздел основной таблицы.

Примеры: Azure Synapse Analytics, система платформы Аналитики (PDW)

А. Изменение кластеризованного индекса на кластеризованный индекс columnstore

Используя инструкцию CREATE CLUSTERED COLUMNSTORE INDEX с DROP_EXISTING = ON, можно:

• Измените кластеризованный индекс на кластеризованный индекс columnstore.

• Перестройте кластеризованный индекс columnstore.

В этом примере таблица создается xDimProduct в виде таблицы rowstore с кластеризованным индексом. Затем в примере используется CREATE CLUSTERED COLUMNSTORE INDEX, чтобы изменить таблицу из таблицы rowstore на таблицу columnstore.

-- Uses AdventureWorks
  
IF EXISTS (SELECT name FROM sys.tables
    WHERE name = N'xDimProduct'
    AND object_id = OBJECT_ID (N'xDimProduct'))
DROP TABLE xDimProduct;
  
--Create a distributed table with a clustered index.
CREATE TABLE xDimProduct (ProductKey, ProductAlternateKey, ProductSubcategoryKey)
WITH ( DISTRIBUTION = HASH(ProductKey),
    CLUSTERED INDEX (ProductKey) )
AS SELECT ProductKey, ProductAlternateKey, ProductSubcategoryKey FROM DimProduct;

Найдите имя кластеризованного индекса, автоматически созданного для новой таблицы в системных метаданных, с помощью sys.indexes. Рассмотрим пример.

SELECT i.object_id, i.name, t.object_id, t.name, i.type_desc
FROM sys.indexes i
INNER JOIN sys.tables t ON i.object_id = t.object_id
WHERE i.type_desc = 'CLUSTERED'
AND t.name = 'xdimProduct';

Теперь вы можете выбрать следующее:

1. Удалите существующий кластеризованный индекс columnstore с автоматически созданным именем, а затем создайте новый кластеризованный индекс columnstore с определяемым пользователем именем.
2. Удалите и замените существующий индекс кластеризованным индексом columnstore, сохраняя то же имя, созданное системой, например ClusteredIndex_1bd8af8797f7453182903cc68df48541.

Рассмотрим пример.

--1. DROP the existing clustered columnstore index with an automatically-created name, for example:
DROP INDEX ClusteredIndex_1bd8af8797f7453182903cc68df48541 on xdimProduct;
GO
CREATE CLUSTERED COLUMNSTORE INDEX [<new_index_name>]
ON xdimProduct;
GO

--Or,
--2. Change the existing clustered index to a clustered columnstore index with the same name.
CREATE CLUSTERED COLUMNSTORE INDEX [ClusteredIndex_1bd8af8797f7453182903cc68df48541]
ON xdimProduct
WITH ( DROP_EXISTING = ON );
GO

В. Перестроение кластеризованного индекса columnstore

В этом примере используется CREATE CLUSTERED COLUMNSTORE INDEX для перестроения существующего кластеризованного индекса columnstore.cci_xDimProduct

--Rebuild the existing clustered columnstore index.
CREATE CLUSTERED COLUMNSTORE INDEX cci_xDimProduct
ON xdimProduct
WITH ( DROP_EXISTING = ON );

С. Изменение имени кластеризованного индекса columnstore

Чтобы изменить имя кластеризованного индекса columnstore, удалите существующий кластеризованный индекс columnstore, а затем повторно создайте индекс с новым именем.

Рекомендуется ограничить эту операцию небольшой или пустой таблицей. Требуется много времени для удаления большого кластеризованного индекса columnstore и перестроения с другим именем.

В этом примере ссылается cci_xDimProduct на кластеризованный индекс columnstore из предыдущего примера. В этом примере удаляется cci_xDimProduct кластеризованный индекс columnstore, а затем повторно создает кластеризованный индекс columnstore с именем mycci_xDimProduct.

--For illustration purposes, drop the clustered columnstore index.
--The table continues to be distributed, but changes to a heap.
DROP INDEX cci_xdimProduct ON xDimProduct;
  
--Create a clustered index with a new name, mycci_xDimProduct.
CREATE CLUSTERED COLUMNSTORE INDEX mycci_xDimProduct
ON xdimProduct
WITH ( DROP_EXISTING = OFF );

Д. Преобразование таблицы columnstore в таблицу rowstore с кластеризованным индексом

Может возникнуть ситуация, для которой требуется удалить кластеризованный индекс columnstore и создать кластеризованный индекс. При удалении кластеризованного индекса columnstore таблица изменяется на формат rowstore. В этом примере таблица columnstore преобразуется в таблицу rowstore с кластеризованным индексом с тем же именем. Данные не будут потеряны. Все данные переходятся в таблицу rowstore, а перечисленные столбцы становятся ключевыми столбцами в кластеризованном индексе.

--Drop the clustered columnstore index and create a clustered rowstore index.
--All of the columns are stored in the rowstore clustered index.
--The columns listed are the included columns in the index.
CREATE CLUSTERED INDEX cci_xDimProduct
ON xdimProduct (ProductKey, ProductAlternateKey, ProductSubcategoryKey, WeightUnitMeasureCode)
WITH ( DROP_EXISTING = ON);

Е. Преобразование таблицы columnstore обратно в кучу rowstore

Используйте DROP INDEX , чтобы удалить кластеризованный индекс columnstore и преобразовать таблицу в кучу rowstore. В этом примере таблица преобразуется в cci_xDimProduct кучу rowstore. Таблица продолжает распространяться, но хранится как куча.

--Drop the clustered columnstore index. The table continues to be distributed, but changes to a heap.
DROP INDEX cci_xdimProduct ON xdimProduct;

F. Создание упорядоченного кластеризованного индекса columnstore в таблице без индекса

Неупорядоченный индекс columnstore охватывает все столбцы по умолчанию, не указывая список столбцов. Упорядоченный индекс columnstore позволяет указать порядок столбцов. Список не должен включать все столбцы.

Дополнительные сведения см. в разделе Настройка производительности с упорядоченными индексами columnstore.

CREATE CLUSTERED COLUMNSTORE INDEX cci ON Sales.OrderLines
ORDER (SHIPDATE);

G. Преобразование кластеризованного индекса columnstore в упорядоченный кластеризованный индекс columnstore

CREATE CLUSTERED COLUMNSTORE INDEX cci ON Sales.OrderLines
ORDER (SHIPDATE)
WITH (DROP_EXISTING = ON);

H. Добавление столбца в порядок упорядоченного кластеризованного индекса columnstore

Можно указать порядок столбцов в индексе columnstore. Исходный упорядоченный кластеризованный индекс columnstore был упорядочен только в столбце SHIPDATE . В следующем примере столбец добавляется PRODUCTKEY в упорядочение. Сведения о доступности упорядоченного columnstore-индекса см. в Индексах Columnstore: Обзор.

CREATE CLUSTERED COLUMNSTORE INDEX cci ON Sales.OrderLines
ORDER (SHIPDATE, PRODUCTKEY)
WITH (DROP_EXISTING = ON);

И. Изменение порядкового номера упорядоченных столбцов

Исходный упорядоченный кластеризованный индекс columnstore был упорядочен по SHIPDATE, PRODUCTKEY. В следующем примере изменяется порядок PRODUCTKEYв . SHIPDATE Сведения о доступности упорядоченного columnstore-индекса см. в Индексах Columnstore: Обзор.

CREATE CLUSTERED COLUMNSTORE INDEX cci ON Sales.OrderLines
ORDER (PRODUCTKEY,SHIPDATE)
WITH (DROP_EXISTING = ON);

J. Создание упорядоченного кластеризованного индекса columnstore

Вы можете создать кластеризованный индекс columnstore с помощью ключей упорядочивания. При создании упорядоченного кластеризованного индекса columnstore следует применить указание MAXDOP = 1 запроса для максимального качества сортировки и короткой длительности. Сведения о доступности упорядоченного columnstore-индекса см. в Индексах Columnstore: Обзор.

CREATE CLUSTERED COLUMNSTORE INDEX [OrderedCCI] ON dbo.FactResellerSalesPartCategoryFull
ORDER (EnglishProductSubcategoryName, EnglishProductName)
WITH (MAXDOP = 1, DROP_EXISTING = ON);

Связанный контент

• Обзор индексов Columnstore
• Что нового в колоночных индексах
• Начните использовать Columnstore для оперативной аналитики в реальном времени
• Колонные индексы в хранилище данных
• Индексы columnstore — производительность запросов
• Руководство по проектированию columnstore индексов