CREATE COLUMNSTORE INDEX (Transact-SQL)

Статья
03/07/2014

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

О создании кластеризованного индекса columnstore см. раздел CREATE CLUSTERED COLUMNSTORE INDEX (Transact-SQL).

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

Применимо для следующих объектов: SQL Server (начиная с SQL Server 2012 до текущей версии). )

Значок ссылки на раздел Синтаксические обозначения в Transact-SQL

Синтаксис

Create a non-clustered columnstore index.
CREATE [ NONCLUSTERED ] COLUMNSTORE INDEX index_name 
    ON [database_name. [schema_name ] . | schema_name . ] table_name  
        ( column  [ ,...n ] )
    [ WITH ( <columnstore_index_option> [ ,...n ] ) ]
    [ ON {
        partition_scheme_name ( column_name ) 
        | filegroup_name 
        | "default" 
        } 
    ]
[ ; ]

<columnstore_index_option> ::=
{
      DROP_EXISTING = { ON | OFF }
    | MAXDOP = max_degree_of_parallelism
 }

Аргументы

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

Некластеризованный индекс columnstore
( column [ ,...n ] )
Задает столбцы для хранения. Некластеризованный индекс columnstore может включать не более 1024 столбцов.

Каждый столбец должен иметь поддерживаемый тип данных для индексов columnstore. Список поддерживаемых типов данных см. в разделе Ограничения.
ON [database_name. [schema_name ] . | schema_name . ] table_name
Указывает одно-, двух- или трехкомпонентное имя таблицы, которая будет содержать индекс.
ON
Эти параметры указывают файловые группы, для которых создается индекс.
- partition_scheme_name ( column_name )
  Задает схему секционирования, которая определяет файловые группы соответствующие секциям секционированного индекса. Схема секционирования должна быть создана в базе данных путем выполнения инструкции CREATE PARTITION SCHEME. column_name указывает столбец, по которому будет секционирован индекс. Столбец должен соответствовать по типу данных, длине и точности аргументу функции секционирования, используемой аргументом partition_scheme_name. column_name в схеме не обязательно должно соответствовать столбцам из определения индекса. При секционировании индекса columnstore компонент Компонент Database Engine добавляет столбец секционирования как столбец индекса, если этого столбца еще нет в списке.
  
  Если аргумент partition_scheme_name или filegroup не задан и таблица секционирована, индекс помещается в ту же схему секционирования и с тем же столбцом секционирования, что и для базовой таблицы.
  
  Индекс columnstore для секционированной таблицы должен быть выровнен по секциям.
  
  Дополнительные сведения о секционировании индексов см. в разделе Секционированные таблицы и индексы.
- filegroup_name
  Указывает имя файловой группы, в которой создается индекс. Если имя filegroup_name не указано и таблица не секционирована, то индекс использует ту же файловую группу, что и базовая таблица. Файловая группа должна существовать.
- «по умолчанию»
  Создает заданный индекс в файловой группе, используемой по умолчанию.
  
  Слово «default» в этом контексте не является ключевым. Это идентификатор установленной по умолчанию файловой группы, который должен иметь разделители, как в выражениях ON "default" или ON [default]. Если указано значение "default", то параметру QUOTED_IDENTIFIER для текущего сеанса должно быть присвоено значение ON. Значение параметра по умолчанию. Дополнительные сведения см. в разделе SET QUOTED_IDENTIFIER (Transact-SQL).
- DROP_EXISTING
  Указывает, что именованный существующий индекс удален и перестраивается. Значение по умолчанию — OFF.
  - ON
    Существующий индекс удаляется и перестраивается. Указанное имя индекса должно совпадать с уже существующим индексом, но определение индекса может быть изменено. Например, можно указать другие столбцы или параметры индекса.
  - OFF
    Выдается ошибка, если индекс с указанным именем уже существует. Тип индекса не может быть изменен с помощью аргумента DROP_EXISTING. Для обратной совместимости синтаксиса аргумент WITH DROP_EXISTING эквивалентен аргументу WITH DROP_EXISTING = ON.
- MAXDOP = max_degree_of_parallelism
  Переопределяет параметр конфигурации Настройка параметра конфигурации сервера max degree of parallelism только на время выполнения операции с индексами. Параметр MAXDOP можно использовать для ограничения числа процессоров, используемых при параллельном выполнении планов. Максимальное число процессоров — 64.
  
  Параметр max_degree_of_parallelism может принимать следующие значения:
  - 1 — подавляет создание параллельных планов.
  - >1 — ограничивает максимальное число процессоров, используемых в параллельных операциях с индексами, заданным или меньшим числом в зависимости от текущей рабочей нагрузки системы. Например, если MAXDOP = 4, то число используемых процессоров будет равно 4 или меньше.
  - 0 (по умолчанию) — в зависимости от текущей рабочей нагрузки системы использует реальное или меньшее число процессоров.
  Дополнительные сведения см. в разделе Настройка параллельных операций с индексами.
  
  Примечание
  
  Параллельные операции с индексами доступны не во всех выпусках Microsoft SQL Server.Перечень функций, поддерживаемых в разных выпусках SQL Server, см. в разделе Функции, поддерживаемые различными выпусками SQL Server 2014.

Разрешения

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

Общие замечания

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

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

Ограничения

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

Не более 1024 столбцов.
Таблица с некластеризованным индексом columnstore может иметь ограничения уникальности, ограничения первичного ключа или ограничения внешнего ключа, но эти ограничения не могут быть включены в некластеризованный индекс columnstore.
Не может быть создан для представления или индексированного представления.
Не может содержать разреженный столбец.
Не может быть изменено с использованием инструкции ALTER INDEX. Чтобы изменить некластеризованный индекс, следует удалить и повторно создать индекс columnstore. Инструкция ALTER INDEX позволяет отключить и перестроить индекс columnstore.
Не может быть создано с использованием ключевого слова INCLUDE.
Нельзя включать ключевые слова ASC и DESC для сортировки индексов. Индексы columnstore упорядочены в соответствии с алгоритмами сжатия. В результате сортировки можно потерять многие преимущества в производительности.

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

datetimeoffset [ ( n ) ]
datetime2 [ ( n ) ]
datetime
smalldatetime
date
time [ ( n ) ]
float [ ( n ) ]
real [ ( n ) ]
decimal [ ( precision [ , scale ] ) ]
money
smallmoney
bigint
int
smallint
tinyint
bit
nvarchar [ ( n ) ], за исключением nvarchar (max), не поддерживается
nchar [ ( n ) ]
varchar [ ( n ) ]
char [ ( n ) ]
varbinary [ ( n ) ], за исключением varbinary (max), не поддерживается
binary [ ( n ) ]

Применимо для следующих объектов: SQL Server (начиная с SQL Server 2014 до текущей версии).

uniqueidentifier

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

ntext, text, и image
varchar(max) и nvarchar(max)
rowversion (и timestamp)
sql_variant
Типы CLR (hierarchyid и пространственные типы)
XML

Применимо для следующих объектов: SQL Server 2012.

uniqueidentifier

Индексы columnstore нельзя использовать вместе со следующими функциями:

Сжатие страниц и строк, а также формат хранения vardecimal (индекс columnstore уже сжат в другом формате).
Репликация
Отслеживание изменений
Система отслеживания измененных данных
Файловый поток

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

Метаданные

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

[ В НАЧАЛО ]

Примеры

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

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

CREATE TABLE SimpleTable
(ProductKey [int] NOT NULL, 
OrderDateKey [int] NOT NULL, 
DueDateKey [int] NOT NULL, 
ShipDateKey [int] NOT NULL);
GO
CREATE CLUSTERED INDEX cl_simple ON SimpleTable (ProductKey);
GO
CREATE NONCLUSTERED COLUMNSTORE INDEX csindx_simple
ON SimpleTable
(OrderDateKey, DueDateKey, ShipDateKey);
GO

Б.Создание простого некластеризованного индекса с использованием всех параметров

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

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

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

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

После создания некластеризованного индекса columnstore в таблице нельзя непосредственно изменять данные в этой таблице. Запрос с инструкциями INSERT, UPDATE, DELETE или MERGE завершится сбоем и вернет сообщение об ошибке. Для добавления или изменения данных в таблице можно воспользоваться одним из следующих способов.

Отключить или удалить индекс columnstore. Затем можно обновлять данные в таблице. Если отключить индекс columnstore, то можно перестроить его после окончания обновления данных. Например:
```
ALTER INDEX mycolumnstoreindex ON mytable DISABLE;
-- update mytable --
ALTER INDEX mycolumnstoreindex on mytable REBUILD
```
Загрузка данных в промежуточную таблицу, не имеющую индекса columnstore. Создание индекса columnstore в промежуточной таблице. Переключение промежуточной таблицы в пустую секцию главной таблицы.
Переключение секции из таблицы с индексом columnstore в пустую промежуточную таблицу. Если в промежуточной таблице имеется индекс columnstore, отключите индекс columnstore. Выполните все обновления. Постройте (или перестройте) индекс columnstore. Переключитесь с промежуточной таблицы обратно на (теперь пустую) секцию главной таблицы.