CREATE COLUMNSTORE INDEX (Transact-SQL)

Artículo
06/18/2013

Crea un índice de almacén de columnas en una tabla especificada. Un índice de almacén de columnas optimizado de memoria xVelocity es un tipo de índice no clúster comprimido. Hay un límite de un índice de almacén de columnas por tabla. Se puede crear un índice antes de que la tabla posea datos. Una tabla con un índice de almacén de columnas no se puede actualizar. Para obtener información sobre cómo utilizar los índices de almacén de columnas, vea Índices de almacén de columnas.

[!NOTA]

Para obtener más información acerca de cómo crear un índice relacional, vea CREATE INDEX (Transact-SQL). Para obtener información acerca de cómo crear un índice XML, vea CREATE XML INDEX (Transact-SQL). Para obtener información acerca de cómo crear un índice espacial, vea CREATE SPATIAL INDEX (Transact-SQL).

Icono de vínculo a temas Convenciones de sintaxis de Transact-SQL

Sintaxis

CREATE [ NONCLUSTERED ] COLUMNSTORE INDEX index_name 
    ON <object> ( column  [ ,...n ] )
    [ WITH ( <column_index_option> [ ,...n ] ) ]
    [ ON {
           { partition_scheme_name ( column_name ) } 
           | filegroup_name 
           | "default" 
         }
    ]
[ ; ]

<object> ::=
{
    [database_name. [schema_name ] . | schema_name . ]
     table_name
{

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

Argumentos

NONCLUSTERED
Crea un índice de almacén de columnas que especifica la ordenación lógica de una tabla. Los índices clúster de almacén de columnas no se admiten.
COLUMNSTORE
Indica que el índice será un índice de almacén de columnas.
index_name
Es el nombre del índice. Los nombres de índice deben ser únicos en una tabla o vista, pero no es necesario que sean únicos en una base de datos. Los nombres de índice deben seguir las reglas de los identificadores.
column
Es la columna o columnas en las que se basa el índice. Un índice de almacén de columnas está limitado a 1024 columnas.
ON partition_scheme_name**(column_name)**
Especifica el esquema de partición que define los grupos de archivos a los que se asignarán las particiones de un índice con particiones. El esquema de partición debe existir dentro de la base de datos mediante la ejecución de CREATE PARTITION SCHEME. column_name especifica la columna en la que se van a crear las particiones de un índice con particiones. Esta columna debe coincidir con el tipo de datos, la longitud y la precisión del argumento de la función de partición que partition_scheme_name emplea. column_name no está limitado a las columnas de la definición del índice. Al crear particiones en un índice de almacén de columnas, el Motor de base de datos agrega la columna de partición como una columna del índice, si no se especificó todavía.

Si no se especificó partition_scheme_name o filegroup y se han creado particiones en la tabla, el índice se sitúa en el mismo esquema de partición y se utiliza la misma columna de partición para la tabla subyacente.

Para obtener más información acerca de los índices de partición, vea Tablas e índices con particiones.
ON filegroup_name
Crea el índice especificado en el grupo de archivos especificado. Si no se ha especificado una ubicación y la tabla o vista no tiene particiones, el índice utiliza el mismo grupo de archivos que la tabla o vista subyacente. El grupo de archivos debe existir previamente.
ON "default"
Crea el índice especificado en el grupo de archivos predeterminado.

El término predeterminado (default), en este contexto, no es una palabra clave. Es un identificador para el grupo de archivos predeterminado y debe delimitarse, como en ON "default" o en ON [default]. Si se especifica "default", la opción QUOTED_IDENTIFIER debe tener el valor ON para la sesión actual. Esta es la configuración predeterminada. Para obtener más información, vea SET QUOTED_IDENTIFIER (Transact-SQL).

<object>::=

Es el objeto completo o no que se indizará.

database_name
Es el nombre de la base de datos.
schema_name
Es el nombre del esquema al que pertenece la tabla.
table_name
Es el nombre de la tabla que va a indizarse.

<column_index_option>::=

Especifica las opciones que se van a usar en la creación del índice de almacén de columnas.

DROP_EXISTING
Especifica que se quite y se recompile el índice con nombre preexistente. El valor predeterminado es OFF.
- ON
  El índice existente se quita y se vuelve a generar. El nombre de índice especificado debe ser el mismo que el de un índice actualmente existente; sin embargo, es posible modificar la definición de índice. Por ejemplo, puede especificar distintas columnas u opciones de índice.
- OFF
  Se muestra un error si ya existe el nombre de índice especificado. El tipo de índice no puede cambiarse utilizando DROP_EXISTING. En la sintaxis compatible con versiones anteriores, WITH DROP_EXISTING es equivalente a WITH DROP_EXISTING = ON.
MAXDOP = max_degree_of_parallelism
Invalida la opción de configuración Establecer la opción de configuración del servidor Grado máximo de paralelismo mientras se prolongue la operación del índice. Utilice MAXDOP para establecer un límite para el número de procesadores utilizados en la ejecución de un plan paralelo. El máximo es 64 procesadores.

max_degree_of_parallelism puede ser:
- 1
  Suprime la generación de planes paralelos.
- >1
  Restringe el número máximo de procesadores utilizados en una operación de índice paralelo al número especificado o a un número inferior, en función de la actual carga de trabajo del sistema.
- 0 (predeterminado)
  Usa el número real de procesadores o menos, según la carga de trabajo actual del sistema.
Para obtener más información, vea Configurar operaciones de índice en paralelo.

[!NOTA]

Las operaciones de índices en paralelo no están disponibles en todas las ediciones de Microsoft SQL Server. Para obtener una lista de características admitidas por las ediciones de SQL Server, vea Características compatibles con las ediciones de SQL Server 2012.

Comentarios

Los índices se pueden crear en una tabla temporal. Cuando se quita la tabla o finaliza la sesión, se quitan los índices.

Los tipos de datos empresariales comunes pueden incluirse en un índice de almacén de columnas. Los siguientes tipos de datos pueden incluirse en un índice de almacén de columnas.

char y varchar
nchar y nvarchar (excepto varchar(max) y nvarchar(max))
decimal (y numeric) (excepto con una precisión mayor que 18 dígitos).
int, bigint, smallint y tinyint.
float (y real)
bit
money y smallmoney
Todos los tipos de datos de fecha y hora (excepto datetimeoffset con escala mayor que 2)

Los siguientes tipos de datos no pueden incluirse en un índice de almacén de columnas.

binary y varbinary
ntext, text e image
varchar(max) y nvarchar(max)
uniqueidentifier
rowversion (y timestamp)
sql_variant
decimal (y numeric) con una precisión superior a 18 dígitos
datetimeoffset con escala mayor que 2
Tipos CLR (hierarchyid y tipos espaciales)
xml

Restricciones básicas

Un índice de almacén de columnas:

No puede tener más de 1024 columnas.
No puede ser clúster. Solo están disponibles los índices no clúster de almacén de columnas.
No puede ser un índice único.
No se puede crear en una vista o una vista indizada.
No puede incluir ninguna columna dispersa.
No puede actuar como clave principal ni clave externa.
No se puede cambiar utilizando la instrucción ALTER INDEX. En su lugar se debe quitar y volver a crear el índice de almacén de columnas. (Puede usar ALTER INDEX para deshabilitar y recompilar un índice de almacén de columnas).
No se puede crear junto con la palabra clave INCLUDE.
No puede incluir las palabras clave ASC ni DESC para ordenar el índice. Los índices de almacén de columnas se ordenan de acuerdo con los algoritmos de compresión. La ordenación eliminaría muchas mejoras de rendimiento.

Los índices de almacén de columnas no se pueden combinar con las siguientes características:

Compresión de página y fila, y formato de almacenamiento vardecimal (un índice de almacén de columnas ya está comprimido en un formato diferente).
Replicación
Seguimiento de cambios
Captura de datos modificados
Secuencia de archivos

Para obtener información acerca de las mejoras de rendimiento y las limitaciones de los índices de almacén de columnas, vea Índices de almacén de columnas.

Permisos

Requiere el permiso ALTER en la tabla.

Ejemplos

A.Crear un índice no clúster sencillo

En el ejemplo siguiente se crea una tabla y un índice clúster sencillos, y después se muestra la sintaxis para crear un índice de almacén de columnas.

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

B.Crear un índice no clúster sencillo utilizando todas las opciones

En el ejemplo siguiente se crea una tabla y un índice clúster sencillos, y después se muestra la sintaxis para crear un índice de almacén de columnas.

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

Para obtener un ejemplo más complejo usando tablas con particiones, vea Índices de almacén de columnas.