CREATE COLUMNSTORE INDEX (Transact-SQL)

Crea un índice no clúster de almacén de columnas en memoria en una tabla de SQL Server. Use un índice no clúster de almacén de columnas para aprovechar la compresión de almacén de columnas y así mejorar considerablemente el tiempo de ejecución de consultas en datos de solo lectura.

Para crear un índice clúster de almacén de columnas, vea CREATE CLUSTERED COLUMNSTORE INDEX (Transact-SQL).

Para obtener más información, vea estos temas:

• Descripción de los índices de almacén de columnas

• Usar índices no clúster de almacén de columnas

• Usar índices clúster de almacén de columnas

Se aplica a: SQL Server (desde SQL Server 2012 hasta la versión actual. )

Convenciones de sintaxis de Transact-SQL

Sintaxis

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
 }

Argumentos

• index_name
  Especifica el nombre del índice. index_name debe ser únicos dentro de la tabla, pero no es necesario que sea único dentro de la base de datos. Los nombres de índice deben seguir las reglas de los identificadores.

  Para un índice no clúster de almacén de columnas,

• ( column [ ,...n ] )
  Especifica las columnas que se van a almacenar. Un índice de almacén de columnas no clúster está limitado a 1024 columnas.

  Cada columna debe ser de un tipo de datos compatible con los índices de almacén de columnas. Vea Limitaciones y restriccionespara obtener una lista de los tipos de datos compatibles.

• ON [database_name. [schema_name ] . | schema_name . ] table_name
  Especifica el nombre de una, dos o tres partes de la tabla que contendrá el índice.

• ON
  Estas opciones especifican los grupos de archivos en el que el índice se creará.

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

    Un índice de almacén de columnas de una tabla con particiones debe estar alineado.

    Para obtener más información acerca de los índices de partición, vea Tablas e índices con particiones.

  • filegroup_name
    Especifica el nombre de un grupo de archivos en el que se va a crear el índice. Si no se especifica filegroup_name y la tabla no tiene particiones, el índice usa el mismo grupo de archivos que la tabla subyacente. El grupo de archivos debe existir previamente.

  • "predeterminado"
    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).

  • 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 en el número de procesadores utilizados en la ejecución de un plan paralelo. El máximo es 64 procesadores.

    Los valores de max_degree_of_parallelism pueden 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. Por ejemplo, si MAXDOP = 4, número de procesadores utilizados será 4 o menos.

    • 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 2014.

Permisos

Requiere el permiso ALTER en la tabla.

Comentarios generales

Se puede crear un índice de almacén de columnas en una tabla temporal. Cuando se quita la tabla o finaliza la sesión, también se quita el índice.

Si tiene una columna con un tipo de datos no admitido para los índices de almacén de columnas, debe omitir esa columna del índice de almacén de columnas.

Limitaciones y restricciones

Índices no clúster de almacén de columnas:

• No puede tener más de 1024 columnas.

• Una tabla con un índice de almacén de columnas no clúster puede tener restricciones UNIQUE, restricciones de clave principal o restricciones de clave externa, pero las restricciones no se pueden incluir en el índice de almacén de columnas no clúster.

• No se puede crear en una vista o una vista indizada.

• No puede incluir ninguna columna dispersa.

• No se puede cambiar mediante la instrucción ALTER INDEX. Para cambiar el índice no clúster, debe quitar y volver a crear el índice de almacén de columnas en su lugar. Puede usar ALTER INDEX para deshabilitar y recompilar un índice de almacén de columnas.

• No se puede crear mediante 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.

Cada columna de un índice de almacén de columnas debe ser de uno de los tipos de datos empresariales comunes siguientes.

• 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 ) ] excepto nvarchar (max) no se admite

• nchar [ ( n ) ]

• varchar [ ( n ) ]

• char [ ( n ) ]

• varbinary [ ( n ) ] excepto varbinary (max) no se admite

• binary [ ( n ) ]

Se aplica a: SQL Server (desde SQL Server 2014 hasta la versión actual).

• uniqueidentifier

Las columnas que usan alguno de los siguientes tipos de datos no pueden incluirse en un índice de almacén de columnas.

• ntext, text e image

• varchar(max) y nvarchar(max)

• rowversion (y timestamp)

• sql_variant

• Tipos CLR (hierarchyid y tipos espaciales)

• xml

Se aplica a: SQL Server 2012.

• uniqueidentifier

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 Descripción de los índices de almacén de columnas.

Metadatos

Todas las columnas de un índice de almacén de columnas se almacenan en los metadatos como columnas incluidas. El índice de almacén de columnas no tiene columnas de clave. Estas vistas del sistema proporcionan información sobre los índices de almacén de columnas.

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

[ ARRIBA ]

Ejemplos

A.Crear un índice no clúster de almacén de columnas simple

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 no clúster.

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 simple usando todas las opciones

En el ejemplo siguiente se muestra la sintaxis para crear un índice no clúster de almacén de columnas usando todas las opciones.

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 que usa tablas con particiones, vea Descripción de los índices de almacén de columnas.

Cargar los datos en un índice no clúster de almacén de columnas

Una vez creado un índice no clúster de almacén de columnas en una tabla, no puede modificar directamente los datos de esa tabla. Una consulta con INSERT, UPDATE, DELETE o MERGE generará un error y devolverá un mensaje de error. Para agregar o modificar los datos de la tabla, puede hacer lo siguiente:

• Deshabilitar o quitar el índice de almacén de columnas. Después puede actualizar los datos de la tabla. Si deshabilita el índice de almacén de columnas, puede regenerar el índice de almacén de columnas cuando termine de actualizar los datos. Por ejemplo,

  ALTER INDEX mycolumnstoreindex ON mytable DISABLE;
  -- update mytable --
  ALTER INDEX mycolumnstoreindex on mytable REBUILD
  

• Cargar datos en una tabla de ensayo que no tenga un índice de almacén de columnas. Genere un índice de almacén de columnas en la tabla de ensayo. Cambie la tabla de ensayo a una partición vacía de la tabla principal.

• Cambiar una partición de la tabla con el índice de almacén de columnas a una tabla de ensayo vacía. Si hay un índice de almacén de columnas en la tabla de ensayo, deshabilítelo. Realice las actualizaciones que desee. Genere (o regenere) el índice de almacén de columnas. Vuelva a cambiar la tabla de ensayo a la partición (ahora vacía) de la tabla principal.

[ ARRIBA ]