CREATE COLUMNSTORE INDEX (Transact-SQL)

  • Artículo

Se aplica a:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)

Convierta una tabla de almacén de filas en un índice de almacén de columnas agrupado o cree un índice de almacén de columnas no agrupado. Use un índice de almacén de columnas para ejecutar de forma eficaz los análisis operativos en tiempo real en una carga de trabajo OLTP, o para mejorar la compresión de los datos y el rendimiento de las consultas de las cargas de trabajo de almacenamiento de datos.

Siga las Novedades de los índices de almacén de columnas para conocer las últimas mejoras de esta característica.

  • Los índices de almacén de columnas agrupados ordenados se introdujeron en SQL Server 2022 (16.x). Para obtener más información, consulte CREATE COLUMNSTORE INDEX.

  • A partir de SQL Server 2016 (13.x), puede crear la tabla como un índice clúster de almacén de columnas. Ya no es necesario crear una tabla de almacén de filas y luego convertirla en un índice de almacén de columnas agrupado.

  • Para obtener información sobre las instrucciones de diseño de índices de almacén de columnas, consulte Guía de diseño de índices de almacén de columnas.

Convenciones de sintaxis de Transact-SQL

Sintaxis

Sintaxis de SQL Server y Azure SQL Database:

-- 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 ] )
    [ 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 )

Sintaxis de Azure Synapse Analytics, Almacenamiento de datos paralelos, SQL Server 2022 (16.x) y versiones posteriores:

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
[;]

Nota:

Para ver la sintaxis de Transact-SQL para SQL Server 2014 (12.x) y versiones anteriores, consulte Versiones anteriores de la documentación.

Argumentos

Algunas de las opciones no están disponibles en todas las versiones del motor de base de datos. En la siguiente tabla se muestran las versiones cuando las opciones se introducen en los índices CLUSTERED COLUMNSTORE y NONCLUSTERED COLUMNSTORE:

Opción CLUSTERED NONCLUSTERED
COMPRESSION_DELAY SQL Server 2016 (13.x) SQL Server 2016 (13.x)
DATA_COMPRESSION SQL Server 2016 (13.x) SQL Server 2016 (13.x)
ONLINE SQL Server 2019 (15.x) SQL Server 2017 (14.x)
WHERE, cláusula N/D SQL Server 2016 (13.x)

Todas las opciones están disponibles en Azure SQL Database.

CREATE CLUSTERED COLUMNSTORE INDEX

Crea un índice clúster de almacén de columnas en el que todos los datos están comprimidos y almacenados por columna. El índice incluye todas las columnas de la tabla y almacena toda la tabla. Si la tabla existente es un montón o un índice agrupado, se convierte en un índice de almacén de columnas agrupado. Si la tabla ya está almacenada como un índice agrupado de almacén de columnas, el índice existente se quita y se vuelve a compilar.

index_name

Especifica el nombre del nuevo índice.

Si la tabla ya tiene un índice clúster de almacén de columnas, puede especificar el mismo nombre que el índice existente o puede usar la opción DROP EXISTING para especificar uno nuevo.

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

Especifica el nombre de una, dos o tres partes de la tabla que se almacenará como un índice clúster de almacén de columnas. Si la tabla es un montón o tiene un índice agrupado, pasa de ser un almacén de filas a un almacén de columnas. Si la tabla ya es un almacén de columnas, esta instrucción vuelve a compilar el índice clúster de almacén de columnas.

ORDER

Se aplica a Azure Synapse Analytics, Analytics Platform System (PDW) y SQL Server 2022 (16.x) y versiones posteriores

Use la column_store_order_ordinal columna en sys.index_columns para determinar el orden de las columnas de un índice de almacén de columnas agrupado. Esto ayuda en la eliminación de segmentos, especialmente con datos de cadena. Para obtener más información, consulte Optimización del rendimiento con el índice de almacén de columnas agrupado ordenado y los índices de almacén de columnas: guía de diseño.

Para convertir en un índice de almacén de columnas agrupado ordenado, el índice existente debe ser un índice de almacén de columnas agrupado. Use la opción DROP_EXISTING.

Los tipos de datos LOB (los tipos de datos de longitud máxima) no pueden ser la clave de un índice de almacén de columnas agrupado ordenado.

Al crear un índice de almacén de columnas agrupado ordenado, use OPTION(MAXDOP = 1) para la ordenación de mayor calidad con la instrucción CREATE INDEX, a cambio de una duración significativamente más larga de la instrucción CREATE INDEX. Para crear el índice con la mayor rapidez posible, no limite MAXDOP y use todos los subprocesos paralelos que puede proporcionar el servidor. La máxima calidad de la compresión y la ordenación puede resultar de ayuda en las consultas en el índice de almacén de columnas.

Cuando se crea un índice de almacén de columnas agrupado ordenado, las columnas de clave se indican mediante la columna column_store_order_ordinal de sys.index_columns.

Opciones de WITH

DROP_EXISTING = [OFF] | ON

DROP_EXISTING = ON especifica que se quite el índice existente y se cree un nuevo índice de almacén de columnas.

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

El valor predeterminado, DROP_EXISTING = OFF, espera que el nombre del índice sea el mismo que el nombre existente. Se produce un error si ya existe el nombre de índice especificado.

MAXDOP = max_degree_of_parallelism

Esta opción puede invalidar la configuración del servidor existente para el grado máximo de paralelismo mientras dura la operación de í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.

Los valores de max_degree_of_parallelism pueden ser:

  • 1: lo que significa suprimir la generación de planes paralelos.
  • >1: lo que significa restringir el número máximo de procesadores usados 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, el número de procesadores usados es de 4 o menos.
  • 0 (predeterminado): usa el número real de procesadores, o menos, según la carga de trabajo actual del sistema.
CREATE CLUSTERED COLUMNSTORE INDEX cci ON Sales.OrderLines
WITH (MAXDOP = 2);

Para obtener más información, vea Configurar el grado máximo de paralelismo (opción de configuración del servidor) y Configurar operaciones de índice paralelo.

COMPRESSION_DELAY = 0 | delay [ MINUTES ]

Para las tablas basadas en disco, el delay especifica el número mínimo de minutos que debe permanecer un grupo de filas delta en estado cerrado en dicho grupo. SQL Server puede entones comprimirlo en el grupo de filas comprimido. Dado que las tablas basadas en disco no realizan un seguimiento de los tiempos de inserción y actualización de las filas individuales, SQL Server aplica el retraso a los grupos de filas delta en estado cerrado.

El valor predeterminado es 0 minutos.

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

Para obtener recomendaciones sobre cuándo usar COMPRESSION_DELAY, vea Introducción al almacén de columnas para los análisis operativos en tiempo real.

DATA_COMPRESSION = COLUMNSTORE | COLUMNSTORE_ARCHIVE

Especifica la opción de compresión de datos para la tabla, el número de partición o el intervalo de particiones especificados. Las opciones son las siguientes:

  • COLUMNSTORE es el valor predeterminado y especifica que se comprima con la compresión de almacén de columnas de mayor rendimiento. Esta opción es la elección habitual.
  • COLUMNSTORE_ARCHIVE comprime la tabla o la partición a un tamaño menor. Use esta opción para situaciones como el archivado que requiere un tamaño de almacenamiento menor y puede permitirse más tiempo para el almacenamiento y la recuperación.
CREATE CLUSTERED COLUMNSTORE INDEX cci ON Sales.OrderLines
WITH ( DATA_COMPRESSION = COLUMNSTORE_ARCHIVE );

Para más información sobre la compresión, vea Compresión de datos.

ONLINE = [ON | OFF]
  • ON especifica que el índice de almacén de columnas permanece en línea y disponible, mientras se compila la nueva copia del índice.
  • OFF especifica que el índice no está disponible mientras se compila la nueva copia.
CREATE CLUSTERED COLUMNSTORE INDEX cci ON Sales.OrderLines
WITH ( ONLINE = ON );

Opciones de ON

Con estas opciones, puede especificar opciones para el almacenamiento de datos, como un esquema de partición, un grupo de archivos específico o el grupo de archivos predeterminado. Si no se especifica la opción ON, el índice usa la configuración de partición o de grupo de archivos de la tabla existente.

partition_scheme_name ( column_name ) especifica el esquema de partición de la tabla. El esquema de partición ya debe existir en la base de datos. Para crear el esquema de partición, vea 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.

filegroup_name especifica el grupo de archivos para almacenar el índice de almacén de columnas agrupado. Si no se especifica ninguna ubicación y la tabla no tiene particiones, el índice usa el mismo grupo de archivos que la tabla o la vista subyacente. El grupo de archivos debe existir previamente.

Para crear el índice en el grupo de archivos predeterminado, use "default" o [default]. Si especifica "default", la QUOTED_IDENTIFIER opción debe ser ON para la sesión actual. QUOTED_IDENTIFIER es ON de forma predeterminada. Para obtener más información, vea SET QUOTED_IDENTIFIER (Transact-SQL).

CREATE [NONCLUSTERED] COLUMNSTORE INDEX

Crear un índice de almacén de columnas no agrupado en una tabla de almacén de filas almacenada como un índice de montón o agrupado. El índice puede tener una condición de filtrado y no necesitar incluir todas las columnas de la tabla subyacente. El índice de almacén de columnas requiere suficiente espacio para almacenar una copia de los datos. El índice se puede actualizar, y se actualiza a medida que cambia la tabla subyacente. El índice no clúster de almacén de columnas de un índice clúster permite el análisis en tiempo real.

index_name

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

(column [ ,...n ] )

Especifica las columnas que se van a almacenar. Un índice de almacén de columnas no agrupado 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 restricciones para obtener una lista de los tipos de datos admitidos.

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

Especifica el nombre de una, dos o tres partes de la tabla que contiene el índice.

Opciones de WITH

DROP_EXISTING = [OFF] | ON

DROP_EXISTING = ON El índice existente se quita y se vuelve a compilar. El nombre de índice especificado debe ser el mismo que el de un índice actualmente existente; sin embargo, la definición se puede modificar. Por ejemplo, puede especificar distintas columnas u opciones de índice.

DROP_EXISTING = OFF
Se produce un error si ya existe el nombre de índice especificado. El tipo de índice no puede cambiarse mediante 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 Configurar el grado máximo de paralelismo (opción de configuración del servidor) durante la operación de í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.

Los valores de max_degree_of_parallelism pueden ser:

  • 1: lo que significa suprimir la generación de planes paralelos.
  • >1: lo que significa restringir el número máximo de procesadores usados 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, el número de procesadores usados es de 4 o menos.
  • 0 (predeterminado): usa el número real de procesadores o menos en función de 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 las características admitidas por las ediciones de SQL Server, consulte Ediciones y características admitidas de SQL Server 2022.

ONLINE = [ON | OFF]
  • ON especifica que el índice de almacén de columnas permanece en línea y disponible, mientras se compila la nueva copia del índice.
  • OFF especifica que el índice no está disponible mientras se compila la nueva copia. En un índice no agrupado, la tabla base permanece disponible. Solo el índice de almacén de columnas no agrupado no se usa para satisfacer las consultas hasta que el nuevo índice esté completo.
CREATE COLUMNSTORE INDEX ncci ON Sales.OrderLines (StockItemID, Quantity, UnitPrice, TaxRate)
WITH ( ONLINE = ON );
COMPRESSION_DELAY = 0 | delay [ MINUTES ]

Especifica un límite inferior para el tiempo que una fila debe permanecer en el grupo de filas delta, antes de que sea elegible para la migración a un grupo de filas comprimido. Por ejemplo, puede indicar que si una fila no se ha modificado durante 120 minutos, se pueda comprimir en formato de almacenamiento en columnas.

En el caso de un índice de almacén de columnas en tablas basadas en disco, no se realiza un seguimiento de la hora en que se insertó o actualizó una fila. En su lugar, el tiempo de cierre del grupo de filas delta se usa como un proxy para la fila. La duración predeterminada es 0 minutos. Una fila se migra al almacenamiento en columnas después de que se acumulan 1 millón de filas en el grupo de filas delta y se marca como cerrada.

DATA_COMPRESSION

Especifica la opción de compresión de datos para la tabla, el número de partición o el intervalo de particiones especificados. Solo se aplica a los índices de almacén de columnas, incluidos los agrupados y no agrupados. Las opciones son las siguientes:

  • COLUMNSTORE es el valor predeterminado y especifica que se comprima con la compresión de almacén de columnas de mayor rendimiento. Esta opción es la elección habitual.
  • COLUMNSTORE_ARCHIVE comprime la tabla o la partición a un tamaño menor. Esta opción se puede usar para el archivado o para otras situaciones que requieran un tamaño de almacenamiento menor y pueda permitirse más tiempo para el almacenamiento y recuperación.

Para más información sobre la compresión, vea Compresión de datos.

WHERE <filter_expression> [ AND <filter_expression> ]

Si se ha llamado a un predicado de filtro, esta opción especifica qué filas se va a incluir en el índice. SQL Server crea estadísticas filtradas sobre las filas de datos en el índice filtrado.

El predicado de filtro usa lógica de comparación simple. Las comparaciones que usan NULL literales no se permiten con los operadores de comparación. Use en su lugar los operadores IS NULL y IS NOT NULL.

A continuación, se muestran algunos ejemplos de predicados de filtro para la tabla Production.BillOfMaterials:

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

Para obtener una guía sobre índices filtrados, vea Crear índices filtrados.

Opciones de ON

Las siguientes opciones especifican los grupos de archivos en los que se crea el índice.

partition_scheme_name ( column_name )

Especifica el esquema de partición que define los grupos de archivos a los que se asignan 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 de í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 está ya especificada.

Si la tabla tiene particiones y no se especifica partition_scheme_name ni filegroup, el índice se coloca en el mismo esquema de partición y usa la misma columna de partición que la tabla subyacente.

Un índice de almacén de columnas de una tabla con particiones debe estar alineado. Para obtener más información sobre los índices con particiones, 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, que es el ajuste predeterminado. Para obtener más información, vea SET QUOTED_IDENTIFIER (Transact-SQL).

Permisos

Requiere el permiso ALTER en la tabla.

Comentarios

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.

Índices filtrados

Un índice filtrado es un índice no agrupado optimizado, adecuado para las consultas que seleccionan un porcentaje pequeño de las filas de una tabla. Utiliza un predicado de filtro para indizar una parte de los datos de la tabla. Un índice filtrado bien diseñado puede mejorar el rendimiento de las consultas, reducir los costos de almacenamiento y de mantenimiento.

Opciones SET requeridas para los índices filtrados

Las opciones SET en la columna de valor requerido son necesarias siempre que se dé alguna de las siguientes condiciones:

  • Cree un índice filtrado.
  • La operación INSERT, UPDATE, DELETE o MERGE modifica los datos de un índice filtrado.
  • El optimizador de consultas usa el índice filtrado para generar el plan de consulta.
Opciones de Set Valor requerido Valor de servidor predeterminado Valor de OLE DB y ODBC Valor de BD-Library (Biblioteca de código) predeterminado
ANSI_NULLS ACTIVAR ACTIVAR ACTIVAR Apagado
ANSI_PADDING ACTIVAR ACTIVAR ACTIVAR Apagado
ANSI_WARNINGS 1 ACTIVAR ACTIVAR ACTIVAR Apagado
ARITHABORT ACTIVAR ACTIVAR Apagado Apagado
CONCAT_NULL_YIELDS_NULL ACTIVAR ACTIVAR ACTIVAR Apagado
NUMERIC_ROUNDABORT Apagado Apagado Apagado Apagado
QUOTED_IDENTIFIER ACTIVAR ACTIVAR ACTIVAR Apagado

1 Al establecer ANSI_WARNINGS en ON, ARITHABORT se establece de forma implícita en ON cuando el nivel de compatibilidad de base de datos está establecido en 90 o un valor posterior. Si el nivel de compatibilidad de la base de datos está establecido en 80 o en un nivel inferior, debe configurar explícitamente la opción ARITHABORT en ON.

Si las opciones SET son incorrectas, se pueden producir las condiciones siguientes:

  • El índice filtrado no se crea.

  • El Motor de base de datos genera un error y revierte cualquier instrucción INSERT, UPDATE, DELETE o MERGE que cambia los datos del índice.

  • El optimizador de consultas no tiene en cuenta el índice en el plan de ejecución de ninguna instrucción Transact-SQL.

Para obtener más información sobre los índices filtrados, vea Crear índices filtrados.

Limitaciones y restricciones

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 ] ) ]
  • numeric [ ( precision [ , scale ] ) ]
  • money
  • smallmoney
  • bigint
  • int
  • smallint
  • tinyint
  • bit
  • nvarchar [ ( n ) ]
  • nvarchar(max)1
  • nchar [ ( n ) ]
  • varchar [ ( n ) ]
  • varchar(max)1
  • char [ ( n ) ]
  • varbinary [ ( n ) ]
  • varbinary(max)1
  • binary [ ( n ) ]
  • uniqueidentifier2

1 Se aplica a SQL Server 2017 (14.x) y a Azure SQL Database en el nivel Premium, en el nivel Estándar (S3 y versiones posteriores) y en todos los niveles de ofertas de núcleo virtual, solo en los índices de almacén de columnas agrupados.

2 Se aplica a SQL Server 2014 (12.x) y versiones posteriores.

Si la tabla subyacente 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 no agrupado.

Los datos de objetos grandes (LOB) mayores de 8 kilobytes se almacenan en el almacenamiento LOB fuera de fila con un puntero hacia la ubicación física almacenada dentro del segmento de columna. El tamaño de los datos almacenados no se notifica en sys.column_store_segments, sys.column_store_dictionaries, o sys.dm_db_column_store_row_group_physical_stats.

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
  • nvarchar(max), varchar(max) y varbinary(max)1
  • rowversion (y timestamp)
  • sql_variant
  • Tipos CLR (hierarchyid y tipos espaciales)
  • xml
  • uniqueidentifier2

1 Se aplica a SQL Server 2016 (13.x) y versiones anteriores e índices de almacén de columnas no agrupados.

2 Se aplica a SQL Server 2012 (11.x).

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

  • No se pueden tener más de 1024 columnas.
  • No pueden crearse como un índice basado en restricciones. Se pueden tener restricciones únicas, restricciones de clave principal y restricciones de clave externa en una tabla con un índice de almacén de columnas. Las restricciones se aplican siempre con un índice de almacén de filas. Las restricciones no se pueden aplicar con un índice de almacén de columnas (agrupado o no agrupado).
  • No puede incluir ninguna columna dispersa.
  • No se pueden modificar 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 volver a compilar un índice de almacén de columnas.
  • No se pueden crear mediante la palabra clave INCLUDE.
  • No pueden 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. En Azure Synapse Analytics, y a partir de SQL Server 2022 (16.x), puede especificar un orden para las columnas de un índice de almacén de columnas. Para más información, consulte Optimización del rendimiento con el índice de almacén de columnas agrupado ordenado.
  • No se pueden incluir columnas LOB de tipo nvarchar(max), varchar(max)y varbinary(max) en índices de almacén de columnas no agrupados. Solo los índices de almacén de columnas agrupados admiten tipos loB, a partir de la versión de SQL Server 2017 (14.x), Azure SQL Database (configurado en el nivel Premium, nivel Estándar (S3 y versiones posteriores) y todos los niveles de ofertas de núcleo virtual. Las versiones anteriores no admiten tipos de LOB en índices de almacén de columnas agrupados y no agrupados.
  • A partir de SQL Server 2016 (13.x), se puede crear un índice de almacén de columnas no agrupado en una vista indexada.

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

  • Columnas calculadas A partir de SQL Server 2017 (14.x), un índice de almacén de columnas agrupado puede contener una columna calculada no persistente. Sin embargo, en SQL Server 2017 (14.x), los índices de almacén de columnas agrupados no pueden contener columnas calculadas persistentes y no se pueden crear índices no clúster en columnas calculadas.
  • Compresión de página y fila, y el formato de almacenamiento vardecimal . (Un índice de almacén de columnas ya está comprimido en un formato diferente).
  • Replicación.
  • Filestream.

No puede usar cursores ni desencadenadores en una tabla con un índice de almacén de columnas agrupado. Esta restricción no se aplica a los índices de almacén de columnas no agrupados. No puede usar cursores ni desencadenadores en una tabla con un índice de almacén de columnas no agrupado.

Limitaciones específicas de SQL Server 2014 (12.x):

Las siguientes limitaciones solo se aplican a SQL Server 2014 (12.x). En esta versión, puede usar índices de almacén de columnas agrupados actualizables. Los índices de almacén de columnas no agrupados siguen siendo de solo lectura.

  • Seguimiento de cambios. No se puede usar el seguimiento de cambios con índices de almacén de columnas.
  • Captura de datos modificados. Esta característica no se puede habilitar en tablas con un índice de almacén de columnas agrupado. A partir de SQL Server 2016 (13.x), la captura de datos modificados se puede habilitar en tablas con un índice de almacén de columnas no agrupado.
  • Secundario legible. No se puede acceder a un índice de almacén de columnas agrupado (CCI) desde una réplica secundaria legible de un grupo de disponibilidad Always legible. Puede acceder a un índice no clúster de almacén de columnas (NCCI) desde un secundario legible.
  • Conjuntos de resultados activos múltiples (MARS). SQL Server 2014 (12.x) usa esta característica para las conexiones de solo lectura a las tablas con un índice de almacén de columnas. Pero SQL Server 2014 (12.x) no es compatible con esta característica para operaciones simultáneas de lenguaje de manipulación de datos (DML) en una tabla con un índice de almacén de columnas. Si se intenta usar la característica para este fin, SQL Server finaliza las conexiones y cancela las transacciones.
  • Los índices de almacén de columnas no agrupados no se pueden crear en una vista o vista indexada.

Para obtener información sobre las ventajas y limitaciones de rendimiento de los índices de almacén de columnas, consulte Índices de almacén de columnas: Información general.

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. Las siguientes vistas del sistema proporcionan información sobre los índices de almacén de columnas:

Ejemplos: convertir la tabla de almacén de filas a almacén de columnas

A. Convertir un montón en un índice clúster de almacén de columnas

En este ejemplo se crea una tabla como un montón y después se convierte en un índice de almacén de columnas agrupado denominado cci_Simple. La creación del índice de almacén de columnas agrupado cambia el almacenamiento de toda la tabla de almacén de filas a almacén de columnas.

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

B. Convertir un índice agrupado en un índice de almacén de columnas agrupado con el mismo nombre

En este ejemplo se crea la tabla con un índice clúster y después se muestra la sintaxis para convertir el índice clúster en un índice clúster de almacén de columnas. La creación del índice de almacén de columnas agrupado cambia el almacenamiento de toda la tabla de almacén de filas a almacén de columnas.

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

C. Administrar índices no agrupado al convertir una tabla de almacén de filas en un índice de almacén de columnas

En este ejemplo se muestra cómo administrar índices no agrupados al convertir una tabla de almacén de filas en un índice de almacén de columnas. A partir de SQL Server 2016 (13.x), no se requiere ninguna acción especial. SQL Server define automáticamente y vuelve a compilar los índices no agrupado en el nuevo índice de almacén de columnas agrupado.

Si desea quitar los índices no clúster, use la instrucción DROP INDEX antes de crear el índice de almacén de columnas. La opción DROP EXISTING solo quita el índice clúster que se va a convertir. No quita los índices no agrupados.

En SQL Server 2012 (11.x) y SQL Server 2014 (12.x), no se podía crear un índice no agrupado en un índice de almacén de columnas.

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

Solo para SQL Server 2012 (11.x) y SQL Server 2014 (12.x), debe quitar los índices no agrupados para crear el índice de almacén de columnas.

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

D. Convertir una tabla de hechos grande de almacén de filas en almacén de columnas

En este ejemplo se explica cómo convertir una tabla de hechos grande desde una tabla de almacén de filas en una tabla de almacén de columnas.

  1. En primer lugar, cree una tabla pequeña para usar en este ejemplo.

    --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. Quite todos los índices no agrupados de la tabla de almacén de filas. Es posible que quiera crear scripts de los índices para volver a crearlos más adelante.

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

  3. Convierta la tabla de almacén de filas en una tabla de almacén de columnas con un índice clúster de almacén de columnas.

    En primer lugar, busque el nombre del índice de almacén de filas agrupado existente. En el paso 1, establecemos el nombre del índice en IDX_CL_MyFactTable. Si no se especificó el nombre del índice, se le ha dado un nombre de índice único generado automáticamente. Puede recuperar el nombre generado automáticamente con la siguiente consulta de ejemplo:

    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';

    Opción 1: Eliminar el índice agrupado existente IDX_CL_MyFactTable y convertirlo MyFactTableen un almacén de columnas. Cambiar el nombre del nuevo índice de almacén de columnas agrupado.

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

    Opción 2: Convertir a almacén de columnas y reutilizar el nombre del índice de almacén de filas agrupado existente.

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

E. Convertir una tabla de almacén de columnas en una tabla de almacén de filas con un índice clúster

Para convertir una tabla de almacén de columnas en una tabla de almacén de filas con un índice clúster, use la instrucción CREATE INDEX con la opción DROP_EXISTING.

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

F. Convertir una tabla de almacén de columnas en un montón de almacenes de filas

Para convertir una tabla de almacén de columnas en un montón de almacenes de filas, quite el índice clúster de almacén de columnas. Esto no se recomienda normalmente, pero puede tener algunos usos limitados. Para más información sobre los montones, vea Montones (tablas sin índices agrupados).

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

G. Desfragmentación mediante la reorganización del índice de almacén de columnas

Hay dos maneras de conservar todo el índice agrupado de almacén de columnas. A partir de SQL Server 2016 (13.x), use ALTER INDEX...REORGANIZE en lugar de REBUILD. Para obtener más información, consulte Grupo de filas de índice de almacén de columnas. En versiones anteriores de SQL Server, se puede usar CREATE CLUSTERED COLUMNSTORE INDEX con DROP_EXISTING=ON o ALTER INDEX (Transact-SQL) y la opción REBUILD. Con ambos métodos se obtenían los mismos resultados.

Empiece por determinar el nombre del índice agrupado de almacén de columnas en 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';

Quite la fragmentación mediante la función REORGANIZE en el índice de almacén de columnas.

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

Ejemplos de índices no clúster de almacén de columnas

A. Crear un índice de almacén de columnas en un índice secundario de una tabla de almacén de filas

En este ejemplo se crea un índice no clúster de almacén de columnas en una tabla de almacén de filas. En esta situación solo se puede crear un índice de almacén de columnas. El índice de almacén de columnas necesita almacenamiento adicional, ya que contiene una copia de los datos de la tabla de almacén de filas. En el ejemplo se crean una tabla simple y un índice agrupado de almacén de filas y luego se muestra la sintaxis para crear un índice no agrupado de almacén de columnas.

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

B. Crear un índice de almacén de columnas básico y no agrupado, usando todas las opciones

En el ejemplo siguiente se muestra la sintaxis de creación de un índice de almacén de columnas no agrupado en el grupo de archivos DEFAULT, especificando los grados máximos de paralelismo (MAXDOP) de 2.

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

C. Crear un índice no clúster de almacén de columnas con un predicado filtrado

En el ejemplo siguiente se crea un índice de almacén de columnas no agrupado filtrado en Production.BillOfMaterials la tabla AdventureWorks2022 de muestra de la base de datos. El predicado de filtro puede incluir columnas que no son columnas de clave en el índice filtrado. El predicado de este ejemplo selecciona solo las filas en que EndDate no es 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;

D. Modificar los datos de un índice no clúster de almacén de columnas

Se aplica a: SQL Server 2012 (11.x) a SQL Server 2014 (12.x).

En SQL Server 2014 (12.x) y versiones anteriores, después de crear un índice de almacén de columnas no agrupado en una tabla, no se pueden modificar directamente los datos de esa tabla. Una consulta con INSERT, UPDATE, DELETE o MERGE genera un error y devuelve un mensaje de error. Estas son las opciones que puede usar para agregar o modificar los datos de la tabla:

  • 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 dbo.mytable DISABLE;
-- update the data in mytable as necessary
ALTER INDEX mycolumnstoreindex on dbo.mytable REBUILD;

  • Cargar datos en una tabla de almacenamiento provisional 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.

Ejemplos: Azure Synapse Analytics y Sistema de la plataforma de análisis (PDW)

A. Convertir un índice clúster en un índice clúster de almacén de columnas

Mediante la instrucción CREATE CLUSTERED COLUMNSTORE INDEX con DROP_EXISTING = ON, puede:

  • Convertir un índice clúster en un índice clúster de almacén de columnas.

  • Volver a compilar un índice clúster de almacén de columnas.

En este ejemplo se crea la tabla xDimProduct como una tabla de almacén de filas con un índice agrupado. En este ejemplo se usa CREATE CLUSTERED COLUMNSTORE INDEX para cambiar una tabla de almacén de filas en una tabla de almacén de columnas.

-- 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;

Busque el nombre del índice agrupado creado automáticamente para la tabla nueva en los metadatos del sistema mediante sys.indexes. Por ejemplo:

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';

Ahora puede elegir:

  1. Anular el índice de almacén de columnas agrupado existente con un nombre creado automáticamente y, luego, crear un índice de almacén de columnas agrupado con un nombre definido por el usuario.
  2. Anular y reemplazar el índice existente por un índice de almacén de columnas agrupado y conservar el mismo nombre generado por el sistema, como ClusteredIndex_1bd8af8797f7453182903cc68df48541.

Por ejemplo:

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

B. Volver a compilar un índice clúster de almacén de columnas

A partir del ejemplo anterior, en este ejemplo se usa CREATE CLUSTERED COLUMNSTORE INDEX para recompilar el índice de almacén de columnas agrupado existente, denominado cci_xDimProduct.

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

C. Cambiar el nombre de un índice clúster de almacén de columnas

Para cambiar el nombre de un índice de almacén de columnas agrupado, quite el índice de almacén de columnas agrupado existente y luego vuelva a crear el índice con un nuevo nombre.

Se recomienda que limite esta operación a una tabla pequeña o vacía. Se tarda mucho en quitar un índice de almacén de columnas grande agrupado y recompilarlo con otro nombre.

En este ejemplo se hace referencia al cci_xDimProduct índice de almacén de columnas agrupado del ejemplo anterior. Este ejemplo anula el cci_xDimProduct índice de almacén de columnas agrupado y lo vuelve a crear con el nombre 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 );

D. Convertir una tabla de almacén de columnas en una tabla de almacén de filas con un índice clúster

Puede haber una situación en la que quiera quitar un índice de almacén de columnas agrupado y crear un índice agrupado. Al quitar el índice de almacén de columnas agrupado, la tabla se cambiará al formato de almacén de filas. En este ejemplo se convierte una tabla de almacén de columnas en una tabla de almacén de filas con un índice clúster con el mismo nombre. No se pierde ningún dato. Todos los datos van a la tabla de almacén de filas y las columnas enumeradas se convierten en las columnas clave del índice agrupado.

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

E. Volver a convertir una tabla de almacén de columnas en un montón de almacén de filas

Use DROP INDEX (SQL Server PDW) para quitar el índice de almacén de columnas agrupado y convertir la tabla en un montón de almacén de filas. En este ejemplo se convierte la tabla cci_xDimProduct en un montón de almacén de filas. La tabla se sigue distribuyendo, pero se almacena como un montón.

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

F. Crear un índice de almacén de columnas agrupado ordenado en una tabla sin índice

Un índice de almacén de columnas sin ordenar cubre todas las columnas de manera predeterminada, sin necesidad de especificar una lista de columnas. Un índice de almacén de columnas ordenado permite especificar el orden de las columnas. En la lista no hace falta incluir todas las columnas.

Los índices de almacén de columnas ordenados están disponibles en Azure Synapse Analytics, Sistema de plataforma de análisis (PDW) y SQL Server 2022 (16.x). Para más información, consulte Optimización del rendimiento con el índice de almacén de columnas agrupado ordenado.

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

G. Convertir un índice de almacén de columnas agrupado en un índice de almacén de columnas agrupado ordenado

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

H. Agregar una columna a la ordenación de un índice de almacén de columnas agrupado ordenado

En Azure Synapse Analytics, el Sistema de plataforma de análisis (PDW) y a partir de SQL Server 2022 (16.x), puede especificar un orden para las columnas de un índice de almacén de columnas. El índice agrupado y ordenado de almacén de columnas original solo estaba ordenado en la columna SHIPDATE. En el ejemplo siguiente se agrega la columna PRODUCTKEY a la ordenación.

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

I. Cambiar el ordinal de las columnas ordenadas

El índice agrupado y ordenado de almacén de columnas original se ordenó en SHIPDATE, PRODUCTKEY. En el ejemplo siguiente se cambia la ordenación a PRODUCTKEY, SHIPDATE.

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

J. Creación de un índice ordenado de almacén de columnas agrupado

Se aplica a: Azure Synapse Analytics y SQL Server 2022 (16.x).

Puede crear un índice de almacén de columnas agrupado con claves de ordenación. Al crear un índice de almacén de columnas agrupado ordenado, debe aplicar la sugerencia MAXDOP = 1 de consulta para obtener la máxima calidad de ordenación y duración más corta.

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

Contenido relacionado