CREATE COLUMNSTORE INDEX (Transact-SQL)CREATE COLUMNSTORE INDEX (Transact-SQL)

ESTE TEMA SE APLICA A:síSQL Server (a partir de 2012)síAzure SQL DatabasesíAzure SQL Data Warehouse síAlmacenamiento de datos paralelos THIS TOPIC APPLIES TO:yesSQL Server (starting with 2012)yesAzure SQL DatabaseyesAzure SQL Data Warehouse yesParallel Data Warehouse

Convertir una tabla de almacén de filas en un índice de almacén de columnas agrupado o crear un índice no clúster de almacén de columnas.Convert a rowstore table to a clustered columnstore index or create a nonclustered columnstore index. Usar un índice de almacén para ejecutar de forma eficaz los análisis operativos en tiempo real en una carga de trabajo OLTP o para mejorar el rendimiento de la compresión y la consulta de datos para las cargas de trabajo de almacenamiento de datos.Use a columnstore index to efficiently run real-time operational analytics on an OLTP workload or to improve data compression and query performance for data warehousing workloads.

Nota

A partir de SQL Server 2016SQL Server 2016, puede crear la tabla como un índice de almacén de columnas agrupado.Starting with SQL Server 2016SQL Server 2016, you can create the table as a clustered columnstore index. Ya no es necesario crear primero una tabla de almacén de filas y, a continuación, convertirlo en un índice de almacén de columnas agrupado.It is no longer necessary to first create a rowstore table and then convert it to a clustered columnstore index.

Sugerencia

Para obtener información sobre las directrices para diseñar índices, consulte la Guía de diseño de índices de SQL Server.For information on index design guidelines, refer to the SQL Server Index Design Guide.

Vaya a ejemplos:Skip to examples:

Vaya a escenarios:Go to scenarios:

Aprende más:Learn more:

Icono de vínculo de tema Convenciones de sintaxis de Transact-SQLTopic link icon Transact-SQL Syntax Conventions

SintaxisSyntax

-- Syntax for SQL Server and Azure SQL Database  

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

--Create a non-clustered columnstore index on a disk-based table.  
CREATE [NONCLUSTERED]  COLUMNSTORE INDEX index_name   
    ON [database_name. [schema_name ] . | schema_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  
-- Syntax for Azure SQL Data Warehouse and Parallel Data Warehouse  

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

ArgumentosArguments

CREATE CLUSTERED COLUMNSTORE INDEXCREATE CLUSTERED COLUMNSTORE INDEX
Crear un índice de almacén de columnas agrupado en el que todos los datos se comprimen y almacenan por columna.Create a clustered columnstore index in which all of the data is compressed and stored by column. El índice incluye todas las columnas de la tabla y almacena toda la tabla.The index includes all of the columns in the table, and stores the entire table. Si la tabla existente es un índice agrupado o montón, la tabla se convierte en un índice de almacén de columnas agrupado.If the existing table is a heap or clustered index, the table is converted to a clustered columnstore index. Si la tabla ya se almacena como un índice de almacén de columnas agrupado, el índice existente se quita y vuelve a generar.If the table is already stored as a clustered columnstore index, the existing index is dropped and rebuilt.

index_nameindex_name
Especifica el nombre para el nuevo índice.Specifies the name for the new index.

Si la tabla ya tiene un índice de almacén de columnas agrupado, puede especificar el mismo nombre que el índice existente, o puede usar la opción DROP EXISTING para especificar un nuevo nombre.If the table already has a clustered columnstore index, you can specify the same name as the existing index, or you can use the DROP EXISTING option to specify a new name.

ON [database_name.ON [database_name. [schema_name ].[schema_name ] . | schema_name . | schema_name . ] table_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.Specifies the one-, two-, or three-part name of the table to be stored as a clustered columnstore index. Si la tabla es un montón o índice agrupado en la tabla se convierte de almacén de filas en un almacén de columnas.If the table is a heap or clustered index the table is converted from rowstore to a columnstore. Si la tabla ya es un almacén de columnas, esta instrucción vuelve a generar el índice de almacén de columnas agrupado.If the table is already a columnstore, this statement rebuilds the clustered columnstore index.

porWITH
DROP_EXISTING = [DESACTIVADO] | ONDROP_EXISTING = [OFF] | ON
DROP_EXISTING = ON especifica que se quite el índice de almacén de columnas clúster existente y crear un nuevo índice de almacén de columnas.DROP_EXISTING = ON specifies to drop the existing clustered columnstore index, and create a new columnstore index.

El valor predeterminado, DROP_EXISTING = OFF espera que el nombre del índice es el mismo que el nombre existente.The default, DROP_EXISTING = OFF expects the index name is the same as the existing name. Se produce un error es el nombre del índice especificado ya existe.An error occurs is the specified index name already exists.

MAXDOP = max_degree_of_parallelismMAXDOP = max_degree_of_parallelism
Reemplaza la configuración del servidor existente para el grado máximo de paralelismo mientras dura la operación de índice.Overrides the existing maximum degree of parallelism server configuration for the duration of the index operation. Utilice MAXDOP para establecer un límite para el número de procesadores utilizados en la ejecución de un plan paralelo.Use MAXDOP to limit the number of processors used in a parallel plan execution. El máximo es 64 procesadores.The maximum is 64 processors.

max_degree_of_parallelism los valores pueden ser:max_degree_of_parallelism values can be:

  • 1: suprime la generación de planes paralelos.1 - Suppress parallel plan generation.
  • >1: limitar el número máximo de procesadores utilizados en una operación de índice en paralelo al número especificado o menos, según la carga de trabajo del sistema actual.>1 - Restrict the maximum number of processors used in a parallel index operation to the specified number or fewer based on the current system workload. Por ejemplo, si MAXDOP = 4, el número de procesadores que desea usar es 4 o menos.For example, when MAXDOP = 4, the number of processors used is 4 or less.
  • 0 (predeterminado): usa el número real de procesadores o menos, según la carga de trabajo actual del sistema.0 (default) - Use the actual number of processors or fewer based on the current system workload.

    Para obtener más información, consulte configurar la max degree of parallelism Server Configuration Option, y configurar operaciones de índice paralelo.For more information, see Configure the max degree of parallelism Server Configuration Option, and Configure Parallel Index Operations.

COMPRESSION_DELAY = 0 | retraso [minutos]COMPRESSION_DELAY = 0 | delay [ Minutes ]
Se aplica a: SQL Server 2016SQL Server 2016 a través de SQL Server 2017SQL Server 2017.Applies to: SQL Server 2016SQL Server 2016 through SQL Server 2017SQL Server 2017.

Para una tabla basada en disco, retraso especifica el número mínimo de minutos que debe permanecer un grupo de filas delta en estado cerrado en el grupo de filas delta antes de que SQL Server puede comprimir en el grupo de filas comprimido.For a disk-based table, delay specifies the minimum number of minutes a delta rowgroup in the CLOSED state must remain in the delta rowgroup before SQL Server can compress it into the compressed rowgroup. Puesto que las tablas basadas en disco no realizar el seguimiento de insertar y actualizar horas en filas individuales, SQL Server aplica el retraso a grupos de filas delta en estado cerrado.Since disk-based tables don't track insert and update times on individual rows, SQL Server applies the delay to delta rowgroups in the CLOSED state.
El valor predeterminado es 0 minutos.The default is 0 minutes.
Para obtener recomendaciones sobre cuándo usar COMPRESSION_DELAY, vea Introducción al almacén de columnas para análisis operativos en tiempo real.For recommendations on when to use COMPRESSION_DELAY, see Get started with Columnstore for real time operational analytics.

DATA_COMPRESSION = COLUMNSTORE | COLUMNSTORE_ARCHIVEDATA_COMPRESSION = COLUMNSTORE | COLUMNSTORE_ARCHIVE
Se aplica a: SQL Server 2016SQL Server 2016 a través de SQL Server 2017SQL Server 2017.Applies to: SQL Server 2016SQL Server 2016 through SQL Server 2017SQL Server 2017. Especifica la opción de compresión de datos para la tabla, el número de partición o el intervalo de particiones especificados.Specifies the data compression option for the specified table, partition number, or range of partitions. Las opciones son las siguientes:The options are as follows:
COLUMNSTORECOLUMNSTORE
Almacén de columnas es el valor predeterminado y especifica que se debe comprimir con la mayor compresión de almacén de columnas de rendimiento.COLUMNSTORE is the default and specifies to compress with the most performant columnstore compression. Esta es la opción típica.This is the typical choice.

COLUMNSTORE_ARCHIVECOLUMNSTORE_ARCHIVE
COLUMNSTORE_ARCHIVE más comprime la tabla o partición para reducir su tamaño.COLUMNSTORE_ARCHIVE further compresses the table or partition to a smaller size. Utilice esta opción para situaciones como archivado que requieran un tamaño menor de almacenamiento y pueda permitirse más tiempo para el almacenamiento y recuperación.Use this option for situations such as archival that require a smaller storage size and can afford more time for storage and retrieval.

Para obtener más información acerca de la compresión, vea compresión de datos.For more information about compression, see Data Compression.

ONON
Con las opciones ON 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.With the ON options you can specify options for data storage, such as a partition scheme, a specific filegroup, or the default filegroup. Si no se especifica la opción ON, el índice usa la configuración de partición o grupo de archivos de configuración de la tabla existente.If the ON option is not specified, the index uses the settings partition or filegroup settings of the existing table.

partition_scheme_name ( column_name )partition_scheme_name ( column_name )
Especifica el esquema de partición de la tabla.Specifies the partition scheme for the table. El esquema de partición ya debe existir en la base de datos.The partition scheme must already exist in the database. Para crear el esquema de partición, vea CREATE PARTITION SCHEME.To create the partition scheme, see CREATE PARTITION SCHEME.

column_name especifica la columna en la que se particiona un índice con particiones.column_name specifies the column against which a partitioned index is partitioned. Esta columna debe coincidir con el tipo de datos, la longitud y la precisión del argumento de la partición de la función que partition_scheme_name está usando.This column must match the data type, length, and precision of the argument of the partition function that partition_scheme_name is using.

filegroup_namefilegroup_name
Especifica el grupo de archivos para almacenar el índice clúster de almacén de columnas.Specifies the filegroup for storing the clustered columnstore index. 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.If no location is specified and the table is not partitioned, the index uses the same filegroup as the underlying table or view. El grupo de archivos debe existir previamente.The filegroup must already exist.

"predeterminado""default"
Para crear el índice en el grupo de archivos predeterminado, use "default" o [default].To create the index on the default filegroup, use "default" or [ default ].

Si se especifica "default", la opción QUOTED_IDENTIFIER debe tener el valor ON para la sesión actual.If "default" is specified, the QUOTED_IDENTIFIER option must be ON for the current session. QUOTED_IDENTIFIER es ON de forma predeterminada.QUOTED_IDENTIFIER is ON by default. Para obtener más información, vea SET QUOTED_IDENTIFIER (Transact-SQL).For more information, see SET QUOTED_IDENTIFIER (Transact-SQL).

CREAR ÍNDICE DE ALMACÉN [NONCLUSTERED]CREATE [NONCLUSTERED] COLUMNSTORE INDEX
Crear un índice no clúster de almacén de columnas en memoria en una tabla de almacén de filas almacenada como un índice agrupado o montón.Create an in-memory nonclustered columnstore index on a rowstore table stored as a heap or clustered index. El índice puede tener una condición de filtrado y no necesita incluir todas las columnas de la tabla subyacente.The index can have a filtered condition and does not need to include all of the columns of the underlying table. El índice de almacén de columnas requiere suficiente espacio para almacenar una copia de los datos.The columnstore index requires enough space to store a copy of the data. Es actualizable y se actualiza cuando se cambia la tabla subyacente.It is updateable and is updated as the underlying table is changed. El índice de almacén de columnas en un índice agrupado permite análisis en tiempo real.The nonclustered columnstore index on a clustered index enables real-time analytics.

index_nameindex_name
Especifica el nombre del índice.Specifies the name of the index. index_name debe ser único dentro de la tabla, pero no tiene que ser único dentro de la base de datos.index_name must be unique within the table, but does not have to be unique within the database. Los nombres de índice deben seguir las reglas de identificadores.Index names must follow the rules of identifiers.

( columna [ ,... n ] )( column [ ,...n ] )
Especifica las columnas que se van a almacenar.Specifies the columns to store. Un índice de almacén de columnas no clúster está limitado a 1024 columnas.A nonclustered columnstore index is limited to 1024 columns.
Cada columna debe ser de un tipo de datos compatible con los índices de almacén de columnas.Each column must be of a supported data type for columnstore indexes. Vea limitaciones y restricciones para obtener una lista de los tipos de datos admitidos.See Limitations and Restrictions for a list of the supported data types.

ON [database_name.ON [database_name. [schema_name ].[schema_name ] . | schema_name . | schema_name . ] table_name] table_name
Especifica el uno, dos o tres partes de nombre de la tabla que contiene el índice.Specifies the one-, two-, or three-part name of the table that contains the index.

CON DROP_EXISTING = [DESACTIVADO] | ONWITH DROP_EXISTING = [OFF] | ON
DROP_EXISTING = ON, se quita y vuelve a generar el índice existente.DROP_EXISTING = ON The existing index is dropped and rebuilt. El nombre de índice especificado debe ser el mismo que el de un índice actualmente existente; sin embargo, la definición se puede modificar.The index name specified must be the same as a currently existing index; however, the index definition can be modified. Por ejemplo, puede especificar distintas columnas u opciones de índice.For example, you can specify different columns, or index options.

DROP_EXISTING = OFF, se muestra un error si ya existe el nombre del índice especificado.DROP_EXISTING = OFF An error is displayed if the specified index name already exists. El tipo de índice no puede cambiarse utilizando DROP_EXISTING.The index type cannot be changed by using DROP_EXISTING. En la sintaxis compatible con versiones anteriores, WITH DROP_EXISTING es equivalente a WITH DROP_EXISTING = ON.In backward compatible syntax, WITH DROP_EXISTING is equivalent to WITH DROP_EXISTING = ON.

MAXDOP = max_degree_of_parallelismMAXDOP = max_degree_of_parallelism
Invalida el configurar la max degree of parallelism Server Configuration Option opción de configuración para la duración de la operación de índice.Overrides the Configure the max degree of parallelism Server Configuration Option configuration option for the duration of the index operation. Utilice MAXDOP para establecer un límite para el número de procesadores utilizados en la ejecución de un plan paralelo.Use MAXDOP to limit the number of processors used in a parallel plan execution. El máximo es 64 procesadores.The maximum is 64 processors.

max_degree_of_parallelism los valores pueden ser:max_degree_of_parallelism values can be:

  • 1: suprime la generación de planes paralelos.1 - Suppress parallel plan generation.
  • >1: limitar el número máximo de procesadores utilizados en una operación de índice en paralelo al número especificado o menos, según la carga de trabajo del sistema actual.>1 - Restrict the maximum number of processors used in a parallel index operation to the specified number or fewer based on the current system workload. Por ejemplo, si MAXDOP = 4, el número de procesadores que desea usar es 4 o menos.For example, when MAXDOP = 4, the number of processors used is 4 or less.
  • 0 (predeterminado): usa el número real de procesadores o menos, según la carga de trabajo actual del sistema.0 (default) - Use the actual number of processors or fewer based on the current system workload.

    Para obtener más información, vea Configurar operaciones de índice en paralelo.For more information, see Configure Parallel Index Operations.

Nota

Operaciones de índice en paralelo no están disponibles en todas las ediciones de MicrosoftMicrosoft SQL ServerSQL Server.Parallel index operations are not available in every edition of MicrosoftMicrosoft SQL ServerSQL Server. Para obtener una lista de las características admitidas por las ediciones de SQL ServerSQL Server, vea Características compatibles con las ediciones de SQL Server 2016.For a list of features that are supported by the editions of SQL ServerSQL Server, see Editions and Supported Features for SQL Server 2016.

ONLINE = [ON | OFF]ONLINE = [ON | OFF]
Se aplica a: SQL Server 2017SQL Server 2017, en únicamente los índices no agrupados.Applies to: SQL Server 2017SQL Server 2017, in nonclustered columnstore indexes only. ON especifica que el índice de almacén de columnas no clúster permanece en línea y disponible cuando la nueva copia del índice se va a compilar.ON specifies that the nonclustered columnstore index remains online and available while the new copy of the index is being built.

DESACTIVAR especifica que el índice no está disponible para su uso durante la generación de la nueva copia.OFF specifies that the index is not available for use while the new copy is being built. Puesto que éste es un índice no agrupado, la tabla base se conserva disponibles, que solo el índice de almacén de columnas no se utiliza para satisfacer las consultas hasta que finalice el nuevo índice.As this is a nonclustered index only, the base table remains available, only the nonclustered columnstore index is not used to satisfy queries until the new index is complete.

COMPRESSION_DELAY = 0 | <retraso > [minutos]COMPRESSION_DELAY = 0 | <delay>[Minutes]
Se aplica a: SQL Server 2016SQL Server 2016 a través de SQL Server 2017SQL Server 2017.Applies to: SQL Server 2016SQL Server 2016 through SQL Server 2017SQL Server 2017.

Especifica un límite inferior en cuánto tiempo una fila debe permanecer en el grupo de filas delta antes de que se pueden migrar a un grupo de filas comprimido.Specifies a lower bound on how long a row should stay in delta rowgroup before it is eligible for migration to compressed rowgroup. Por ejemplo, un cliente puede decir que si una fila se ha modificado durante 120 minutos, que se pueda seleccionar para comprimir en formato de almacenamiento en columnas.For example, a customer can say that if a row is unchanged for 120 minutes, make it eligible for compressing into columnar storage format. Índice de almacén de columnas en tablas basadas en disco, no Mida el tiempo cuando una fila se inserta o actualiza, usamos el tiempo de filas cerrado delta como un proxy para la fila en su lugar.For columnstore index on disk-based tables, we don’t track the time when a row was inserted or updated, we use the delta rowgroup closed time as a proxy for the row instead. La duración predeterminada es 0 minutos.The default duration is 0 minutes. Una fila se migra al almacenamiento en columnas una vez que se ha acumulado en el grupo de filas delta 1 millón de filas y se ha marcado como cerrado.A row is migrated to columnar storage once 1 million rows have been accumulated in delta rowgroup and it has been marked closed.

DATA_COMPRESSIONDATA_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.Specifies the data compression option for the specified table, partition number, or range of partitions. Las opciones son las siguientes:The options are as follows:
COLUMNSTORECOLUMNSTORE
Se aplica a: SQL Server 2016SQL Server 2016 a través de SQL Server 2017SQL Server 2017.Applies to: SQL Server 2016SQL Server 2016 through SQL Server 2017SQL Server 2017. Solo se aplica a los índices de almacén de columnas, incluidos los clúster y no clúster.Applies only to columnstore indexes, including both nonclustered columnstore and clustered columnstore indexes. Almacén de columnas es el valor predeterminado y especifica que se debe comprimir con la mayor compresión de almacén de columnas de rendimiento.COLUMNSTORE is the default and specifies to compress with the most performant columnstore compression. Esta es la opción típica.This is the typical choice.

COLUMNSTORE_ARCHIVECOLUMNSTORE_ARCHIVE
Se aplica a: SQL Server 2016SQL Server 2016 a través de SQL Server 2017SQL Server 2017.Applies to: SQL Server 2016SQL Server 2016 through SQL Server 2017SQL Server 2017. Solo se aplica a los índices de almacén de columnas, incluidos los clúster y no clúster.Applies only to columnstore indexes, including both nonclustered columnstore and clustered columnstore indexes. COLUMNSTORE_ARCHIVE más comprime la tabla o partición para reducir su tamaño.COLUMNSTORE_ARCHIVE further compresses the table or partition to a smaller size. Esto se puede usar para el archivado o para otras situaciones que requieran un tamaño de almacenamiento mínimo y pueda permitirse más tiempo para el almacenamiento y recuperación.This can be used for archival, or for other situations that require a smaller storage size and can afford more time for storage and retrieval.

Para obtener más información acerca de la compresión, vea compresión de datos.For more information about compression, see Data Compression.

DONDE <filter_expression > [AND <filter_expression >] se aplica a: SQL Server 2016SQL Server 2016 a través de SQL Server 2017SQL Server 2017.WHERE <filter_expression> [ AND <filter_expression> ] Applies to: SQL Server 2016SQL Server 2016 through SQL Server 2017SQL Server 2017.

Llama a un predicado de filtro, esto especifica qué filas desea incluir en el índice.Called a filter predicate, this specifies which rows to include in the index. SQL ServerSQL Servercrea las estadísticas filtradas en las filas de datos en el índice filtrado. creates filtered statistics on the data rows in the filtered index.

El predicado de filtro utiliza la lógica de comparación simple.The filter predicate uses simple comparison logic. Las comparaciones que utilizan literales NULL no se admiten con los operadores de comparación.Comparisons using NULL literals are not allowed with the comparison operators. En su lugar, use los operadores IS NULL e IS NOT NULL.Use the IS NULL and IS NOT NULL operators instead.

A continuación, se muestran algunos ejemplos de predicados de filtro para la tabla Production.BillOfMaterials:Here are some examples of filter predicates for the Production.BillOfMaterials table:
WHERE StartDate > '20000101' AND EndDate <= '20000630'
WHERE ComponentID IN (533, 324, 753)
WHERE StartDate IN ('20000404', '20000905') AND EndDate IS NOT NULL

Para obtener instrucciones sobre índices filtrados, vea Create Filtered Indexes.For guidance on filtered indexes, see Create Filtered Indexes.

ONON
Estas opciones especifican los grupos de archivos en el que se crea el índice.These options specify the filegroups on which the index is created.

partition_scheme_name ( column_name )partition_scheme_name ( column_name )
Especifica el esquema de partición que define los grupos de archivos en el que se asigna las particiones de un índice con particiones.Specifies the partition scheme that defines the filegroups onto which the partitions of a partitioned index is mapped. El esquema de partición debe existir en la base de datos mediante la ejecución de CREATE PARTITION SCHEME.The partition scheme must exist within the database by executing CREATE PARTITION SCHEME. column_name especifica la columna en la que se particiona un índice con particiones.column_name specifies the column against which a partitioned index is partitioned. Esta columna debe coincidir con el tipo de datos, la longitud y la precisión del argumento de la partición de la función que partition_scheme_name está usando.This column must match the data type, length, and precision of the argument of the partition function that partition_scheme_name is using. column_name no está restringida a las columnas de la definición del índice.column_name is not restricted to the columns in the index definition. Al crear particiones en un índice de almacén de columnas, el Motor de base de datosDatabase Engine agrega la columna de partición como una columna del índice, si no se especificó todavía.When partitioning a columnstore index, Motor de base de datosDatabase Engine adds the partitioning column as a column of the index, if it is not already specified.
Si partition_scheme_name o archivos no se ha especificado y la tabla tiene particiones, el índice se sitúa en el mismo esquema de partición, con la misma columna de partición que la tabla subyacente.If partition_scheme_name or filegroup is not specified and the table is partitioned, the index is placed in the same partition scheme, using the same partitioning column, as the underlying table.
Un índice de almacén de columnas de una tabla con particiones debe estar alineado.A columnstore index on a partitioned table must be partition aligned.
Para obtener más información acerca de las particiones de índices, vea Partitioned Tables and Indexes.For more information about partitioning indexes, see Partitioned Tables and Indexes.

filegroup_namefilegroup_name
Especifica el nombre de un grupo de archivos en el que se va a crear el índice.Specifies a filegroup name on which to create the index. Si filegroup_name no se ha especificado y la tabla no tiene particiones, el índice usa el mismo grupo de archivos que la tabla subyacente.If filegroup_name is not specified and the table is not partitioned, the index uses the same filegroup as the underlying table. El grupo de archivos debe existir previamente.The filegroup must already exist.

"predeterminado""default"
Crea el índice especificado en el grupo de archivos predeterminado.Creates the specified index on the default filegroup.

El término predeterminado (default), en este contexto, no es una palabra clave.The term default, in this context, is not a keyword. Es un identificador para el grupo de archivos predeterminado y debe delimitarse, como en ON "predeterminado" u ON [predeterminado].It is an identifier for the default filegroup and must be delimited, as in ON "default" or ON [default]. Si se especifica "default", la opción QUOTED_IDENTIFIER debe tener el valor ON para la sesión actual.If "default" is specified, the QUOTED_IDENTIFIER option must be ON for the current session. Esta es la configuración predeterminada.This is the default setting. Para obtener más información, vea SET QUOTED_IDENTIFIER (Transact-SQL).For more information, see SET QUOTED_IDENTIFIER (Transact-SQL).

PermissionsPermissions

Requiere el permiso ALTER en la tabla.Requires ALTER permission on the table.

Notas generalesGeneral Remarks

Puede crearse un índice de almacén de columnas en una tabla temporal.A columnstore index can be created on a temporary table. Cuando se quita la tabla o finaliza la sesión, también se quita el índice.When the table is dropped or the session ends, the index is also dropped.

Índices filtradosFiltered Indexes

Un índice filtrado es un índice no clúster optimizado, adecuado para las consultas que seleccionan un porcentaje pequeño de las filas de una tabla.A filtered index is an optimized nonclustered index, suited for queries that select a small percentage of rows from a table. Utiliza un predicado de filtro para indizar una parte de los datos de la tabla.It uses a filter predicate to index a portion of the data in the table. Un índice filtrado bien diseñado puede mejorar el rendimiento de las consultas, reducir los costos de almacenamiento y de mantenimiento.A well-designed filtered index can improve query performance, reduce storage costs, and reduce maintenance costs.

Opciones SET requeridas para los índices filtradosRequired SET Options for Filtered Indexes

Las opciones SET de la columna de valor requerido son necesarias siempre que se dé alguna de las condiciones siguientes:The SET options in the Required Value column are required whenever any of the following conditions occur:

  • Se crea un índice filtrado.Create a filtered index.
  • La operación INSERT, UPDATE, DELETE o MERGE modifica los datos de un índice filtrado.INSERT, UPDATE, DELETE, or MERGE operation modifies the data in a filtered index.
  • Se utiliza el índice filtrado por el optimizador de consultas para generar el plan de consulta.The filtered index is used by the query optimizer to produce the query plan.

    Opciones de SetSET options Valor requeridoRequired value Valor de servidor predeterminadoDefault server value Valor predeterminadoDefault

    Valor de OLE DB y ODBCOLE DB and ODBC value
    Valor predeterminadoDefault

    predeterminadoDB-Library value
    ANSI_NULLSANSI_NULLS ONON ONON ONON OFFOFF
    ANSI_PADDINGANSI_PADDING ONON ONON ONON OFFOFF
    ANSI_WARNINGSANSI_WARNINGS ONON ONON ONON OFFOFF
    ARITHABORTARITHABORT ONON ONON OFFOFF OFFOFF
    CONCAT_NULL_YIELDS_NULLCONCAT_NULL_YIELDS_NULL ONON ONON ONON OFFOFF
    NUMERIC_ROUNDABORTNUMERIC_ROUNDABORT OFFOFF OFFOFF OFFOFF OFFOFF
    QUOTED_IDENTIFIERQUOTED_IDENTIFIER ONON ONON ONON OFFOFF

    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 superior.Setting ANSI_WARNINGS to ON implicitly sets ARITHABORT to ON when the database compatibility level is set to 90 or higher. Si el nivel de compatibilidad de la base de datos está establecido en 80 o en un nivel inferior, debe configurarse explícitamente la opción ARITHABORT en ON.If the database compatibility level is set to 80 or earlier, the ARITHABORT option must explicitly be set to ON.

    Si las opciones SET son incorrectas, se pueden producir las condiciones siguientes:If the SET options are incorrect, the following conditions can occur:

  • El índice filtrado no se crea.The filtered index is not created.

  • El Motor de base de datosDatabase Engine genera un error y revierte cualquier instrucción INSERT, UPDATE, DELETE o MERGE que cambia los datos del índice.The Motor de base de datosDatabase Engine generates an error and rolls back INSERT, UPDATE, DELETE, or MERGE statements that change data in the index.

  • El optimizador de consultas no tiene en cuenta el índice en el plan de ejecución de ninguna instrucción Transact-SQL.Query optimizer does not consider the index in the execution plan for any Transact-SQL statements.

    Para obtener más información acerca de los índices filtrados, vea Create Filtered Indexes.For more information about Filtered Indexes, see Create Filtered Indexes.

Limitaciones y restriccionesLimitations and Restrictions

Cada columna de un índice de almacén de columnas debe ser de uno de los tipos de datos empresariales comunes siguientes:Each column in a columnstore index must be of one of the following common business data types:

  • DateTimeOffset [( n )]datetimeoffset [ ( n ) ]
  • datetime2 [( n )]datetime2 [ ( n ) ]
  • DATETIMEdatetime
  • smalldatetimesmalldatetime
  • Datedate
  • tiempo [( n )]time [ ( n ) ]
  • float [( n )]float [ ( n ) ]
  • real [( n )]real [ ( n ) ]
  • decimal [( precisión [ , escala ] ) ]decimal [ ( precision [ , scale ] ) ]
  • numérico [( precisión [ , escala ] ) ]numeric [ ( precision [ , scale ] ) ]
  • moneymoney
  • SMALLMONEYsmallmoney
  • BIGINTbigint
  • INTint
  • smallintsmallint
  • TINYINTtinyint
  • bitbit
  • nvarchar [( n )]nvarchar [ ( n ) ]
  • nvarchar (max) (se aplica a SQL Server 2017SQL Server 2017 y base de datos de SQL de Azure en premium tarifa, en los índices de almacén de columnas agrupado solo)nvarchar(max) (Applies to SQL Server 2017SQL Server 2017 and Azure SQL Database at premium pricing tier, in clustered columnstore indexes only)
  • nchar [( n )]nchar [ ( n ) ]
  • varchar [( n )]varchar [ ( n ) ]
  • varchar (max) (se aplica a SQL Server 2017SQL Server 2017 y base de datos de SQL de Azure en premium tarifa, en los índices de almacén de columnas agrupado solo)varchar(max) (Applies to SQL Server 2017SQL Server 2017 and Azure SQL Database at premium pricing tier, in clustered columnstore indexes only)
  • Char [( n )]char [ ( n ) ]
  • varbinary [( n )]varbinary [ ( n ) ]
  • varbinary (max) (se aplica a SQL Server 2017SQL Server 2017 y base de datos de SQL de Azure en premium tarifa, en los índices de almacén de columnas agrupado solo)varbinary (max) (Applies to SQL Server 2017SQL Server 2017 and Azure SQL Database at premium pricing tier, in clustered columnstore indexes only)
  • binario [( n )]binary [ ( n ) ]
  • uniqueidentifier (se aplica a SQL Server 2014SQL Server 2014 y versiones posteriores)uniqueidentifier (Applies to SQL Server 2014SQL Server 2014 and later)

Si la tabla subyacente tiene una columna de un tipo de datos que no se admite para los índices de almacén de columnas, se debe omitir esa columna del índice no agrupado de almacén de columnas.If the underlying table has a column of a data type that is not supported for columnstore indexes, you must omit that column from the nonclustered columnstore index.

No se pueden incluir columnas que usan cualquiera de los siguientes tipos de datos en un índice de almacén de columnas:Columns that use any of the following data types cannot be included in a columnstore index:

  • ntext, text e imagentext, text, and image
  • nvarchar (max), varchar (max) y varbinary (max) (se aplica a SQL Server 2016SQL Server 2016 y las versiones anteriores y no agrupados)nvarchar(max), varchar(max), and varbinary(max) (Applies to SQL Server 2016SQL Server 2016 and prior versions, and nonclustered columnstore indexes)
  • rowversion (y timestamp)rowversion (and timestamp)
  • sql_variantsql_variant
  • Tipos CLR (hierarchyid y tipos espaciales)CLR types (hierarchyid and spatial types)
  • xmlxml
  • uniqueidentifier (se aplica a SQL Server 2012SQL Server 2012)uniqueidentifier (Applies to SQL Server 2012SQL Server 2012)

Índices no agrupados:Nonclustered columnstore indexes:

  • No puede tener más de 1024 columnas.Cannot have more than 1024 columns.
  • 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.A table with a nonclustered columnstore index can have unique constraints, primary key constraints, or foreign key constraints, but the constraints cannot be included in the nonclustered columnstore index.
  • No se puede crear en una vista o una vista indizada.Cannot be created on a view or indexed view.
  • No puede incluir ninguna columna dispersa.Cannot include a sparse column.
  • No se puede cambiar mediante el uso de la ALTER INDEX instrucción.Cannot be changed by using the ALTER INDEX statement. Para cambiar el índice no clúster, debe quitar y volver a crear el índice de almacén de columnas en su lugar.To change the nonclustered index, you must drop and re-create the columnstore index instead. Puede usar ALTER INDEX para deshabilitar y volver a generar un índice de almacén de columnas.You can use ALTER INDEX to disable and rebuild a columnstore index.
  • No se puede crear mediante el uso de la INCLUDE palabra clave.Cannot be created by using the INCLUDE keyword.
  • No puede incluir el ASC o DESC palabras clave para ordenar el índice.Cannot include the ASC or DESC keywords for sorting the index. Los índices de almacén de columnas se ordenan de acuerdo con los algoritmos de compresión.Columnstore indexes are ordered according to the compression algorithms. La ordenación eliminaría muchas mejoras de rendimiento.Sorting would eliminate many of the performance benefits.
  • No puede incluir columnas de objetos grandes (LOB) de tipo nvarchar (max), varchar (max) y varbinary (max) en índices de almacén de columnas no agrupado.Cannot include large object (LOB) columns of type nvarchar(max), varchar(max), and varbinary(max) in nonclustered column store indexes. Solo los índices de almacén de columnas agrupado admiten tipos de LOB, a partir de SQL Server 2017SQL Server 2017 versión y la base de datos de SQL de Azure configurada en tarifa premium.Only clustered columnstore indexes support LOB types, beginning in SQL Server 2017SQL Server 2017 version and Azure SQL Database configured at premium pricing tier. Tenga en cuenta que las versiones anteriores no admiten tipos de LOB en los índices de almacén de columnas agrupados y no agrupados.Note, prior versions do not support LOB types in clustered and nonclustered columnstore indexes.

Índices de almacén de columnas no se puede combinar con las siguientes características:Columnstore indexes cannot be combined with the following features:

  • Columnas calculadasComputed columns. A partir de SQL Server 2017, un índice de almacén de columnas agrupado puede contener una columna calculada no persistente.Starting with SQL Server 2017, a clustered columnstore index can contain a non-persisted computed column. Sin embargo, en SQL Server 2017, índices de almacén de columnas agrupado no pueden contener columnas calculadas persistentes y no se puede crear índices no agrupados en columnas calculadas.However, in SQL Server 2017, clustered columnstore indexes cannot contain persisted computed columns, and you cannot created nonclustered indexes on computed columns.
  • Compresión de página y fila, y vardecimal el formato de almacenamiento (un índice de almacén de columnas ya está comprimido en un formato diferente).Page and row compression, and vardecimal storage format (A columnstore index is already compressed in a different format.)
  • REPLICATIONReplication
  • Secuencia de archivosFilestream

No puede utilizar los cursores o desencadenadores en una tabla con un índice de almacén de columnas agrupado.You cannot use cursors or triggers on a table with a clustered columnstore index. Esta restricción no se aplica a los índices no agrupados; Puede utilizar los cursores y desencadenadores en una tabla con un índice no clúster de almacén de columnas.This restriction does not apply to nonclustered columnstore indexes; you can use cursors and triggers on a table with a nonclustered columnstore index.

Limitaciones específicas de SQL Server 2014SQL Server 2014 specific limitations
Estas limitaciones se aplican sólo a SQL Server 2014.These limitations apply only to SQL Server 2014. En esta versión, introdujimos índices de almacén de columnas agrupado actualizable.In this release, we introduced updateable clustered columnstore indexes. Los índices no agrupados estaban siendo de solo lectura.Nonclustered columnstore indexes were still read-only.

  • Seguimiento de cambios.Change tracking. No puede utilizar el seguimiento de cambios con índices de almacén de columnas no agrupado (NCCI) porque son de solo lectura.You cannot use change tracking with nonclustered columnstore indexes (NCCI) because they are read-only. Funciona para los índices de almacén de columnas agrupados (CCI).It does work for clustered columnstore indexes (CCI).
  • Captura de datos modificados.Change data capture. No puede utilizar el cambio de captura de datos para el índice de almacén de columnas no agrupado (NCCI) porque son de solo lectura.You cannot use change data capture for nonclustered columnstore index (NCCI) because they are read-only. Funciona para los índices de almacén de columnas agrupados (CCI).It does work for clustered columnstore indexes (CCI).
  • Base de datos secundaria legible.Readable secondary. No se puede tener acceso a un índice de almacén de columnas agrupado en clúster (CCI) de un elemento secundario legible de un grupo de disponibilidad siempre OnReadable.You cannot access a clustered clustered columnstore index (CCI) from a readable secondary of an Always OnReadable availability group. Puede tener acceso a un índice de almacén de columnas no agrupado (NCCI) de una base de datos secundaria legible.You can access a nonclustered columnstore index (NCCI) from a readable secondary.
  • Conjuntos de resultados activos múltiples (MARS).Multiple Active Result Sets (MARS). SQL Server 2014 utiliza MARS para las conexiones de solo lectura para tablas con un índice de almacén de columnas.SQL Server 2014 uses MARS for read-only connections to tables with a columnstore index. Sin embargo, SQL Server 2014 no es compatible con MARS para operaciones de DML (lenguaje) de manipulación de datos simultáneas en una tabla con un índice de almacén de columnas.However, SQL Server 2014 does not support MARS for concurrent data manipulation language (DML) operations on a table with a columnstore index. Cuando esto ocurre, SQL Server termina las conexiones y anula las transacciones.When this occurs, SQL Server terminates the connections and aborts the transactions.

    Para obtener información sobre las ventajas de rendimiento y las limitaciones de los índices de almacén de columnas, vea información general de los índices de almacén de columnas.For information about the performance benefits and limitations of columnstore indexes, see Columnstore Indexes Overview.

MetadatosMetadata

Todas las columnas de un índice de almacén de columnas se almacenan en los metadatos como columnas incluidas.All of the columns in a columnstore index are stored in the metadata as included columns. El índice de almacén de columnas no tiene columnas de clave.The columnstore index does not have key columns. Estas vistas del sistema proporcionan información sobre los índices de almacén de columnas.These system views provide information about columnstore indexes.

Ejemplos para convertir una tabla de almacén de filas en el almacén de columnasExamples for converting a rowstore table to columnstore

A.A. Convertir un montón en un índice clúster de almacén de columnasConvert a heap to a clustered columnstore index

En este ejemplo se crea una tabla como un montón y después se convierte en un índice clúster de almacén de columnas denominado cci_Simple.This example creates a table as a heap and then converts it to a clustered columnstore index named cci_Simple. Esto cambia el almacenamiento de la tabla de un almacén de filas a un almacén de columnas.This changes the storage for the entire table from rowstore to columnstore.

CREATE TABLE 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 SimpleTable;  
GO  

B.B. Convertir un índice clúster en un índice clúster de almacén de columnas con el mismo nombre.Convert a clustered index to a clustered columnstore index with the same name.

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.This example creates a table with clustered index, and then demonstrates the syntax of converting the clustered index to a clustered columnstore index. Esto cambia el almacenamiento de la tabla de un almacén de filas a un almacén de columnas.This changes the storage for the entire table from rowstore to 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 CLUSTERED COLUMNSTORE INDEX cl_simple ON SimpleTable  
WITH (DROP_EXISTING = ON);  
GO  

C.C. Administrar índices no clúster al convertir una tabla de almacén de filas en un índice de almacén de columnas.Handle nonclustered indexes when converting a rowstore table to a columnstore index.

Este ejemplo muestra cómo tratar los índices no clúster al convertir una tabla de almacén de filas en un índice de almacén de columnas.This example shows how to handle nonclustered indexes when converting a rowstore table to a columnstore index. En realidad, comenzando con SQL Server 2016SQL Server 2016 ninguna acción especial es necesaria; SQL ServerSQL Server define automáticamente y vuelve a generar los índices no clúster en el nuevo índice de almacén de columnas agrupado.Actually, beginning with SQL Server 2016SQL Server 2016 no special action is required; SQL ServerSQL Server automatically defines and rebuilds the nonclustered indexes on the new clustered columnstore index.

Si desea quitar los índices no clúster, utilice la instrucción DROP INDEX antes de crear el índice de almacén de columnas.If you want to drop the nonclustered indexes, use the DROP INDEX statement prior to creating the columnstore index. La opción DROP EXISTING solo quita el índice agrupado que se va a convertir.The DROP EXISTING option only drops the clustered index that is being converted. No quita los índices no clúster.It does not drop the nonclustered indexes.

En SQL Server 2012SQL Server 2012 y SQL Server 2014SQL Server 2014, no pudo crear un índice no agrupado en un índice de almacén de columnas.In SQL Server 2012SQL Server 2012 and SQL Server 2014SQL Server 2014, you could not create a nonclustered index on a columnstore index. Este ejemplo muestra cómo en versiones anteriores es necesario quitar los índices no clúster antes de crear el índice de almacén de columnas.This example shows how in previous releases you need to drop the nonclustered indexes before creating the columnstore index.


--Create the table for use with this example.  
CREATE TABLE 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 SimpleTable (OrderDateKey);  
CREATE INDEX nc2_simple ON SimpleTable (DueDateKey);   
GO  

--SQL Server 2012 and SQL Server 2014: you need to drop the nonclustered indexes  
--in order to create the columnstore index.   

DROP INDEX SimpleTable.nc1_simple;  
DROP INDEX SimpleTable.nc2_simple;  

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

D.D. Convertir una tabla de hechos grande de almacén de filas en almacén de columnasConvert a large fact table from rowstore to columnstore

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.This example explains how to convert a large fact table from a rowstore table to a columnstore table.

Para convertir una tabla de almacén de filas en una tabla de almacén de columnas.To convert a rowstore table to a columnstore table.

  1. En primer lugar, cree una tabla pequeña para usar en este ejemplo.First, create a small table to use in this example.

    --Create a rowstore table with a clustered index and a non-clustered index.  
    CREATE TABLE MyFactTable (  
        ProductKey [int] NOT NULL,  
        OrderDateKey [int] NOT NULL,  
         DueDateKey [int] NOT NULL,  
         ShipDateKey [int] NOT NULL )  
    )  
    WITH (  
        CLUSTERED INDEX ( ProductKey )  
    );  
    
    --Add a non-clustered index.  
    CREATE INDEX my_index ON MyFactTable ( ProductKey, OrderDateKey );  
    
  2. Quite todos los índices no clúster de la tabla de almacén de filas.Drop all non-clustered indexes from the rowstore table.

    --Drop all non-clustered indexes  
    DROP INDEX my_index ON MyFactTable;  
    
  3. Quitar el índice clúster.Drop the clustered index.

    • Haga esto solo si desea especificar un nuevo nombre para el índice cuando se convierta en un índice clúster de almacén de columnas.Do this only if you want to specify a new name for the index when it is converted to a clustered columnstore index. Si no se quita el índice agrupado, el nuevo índice de almacén de columnas agrupado tiene el mismo nombre.If you do not drop the clustered index, the new clustered columnstore index has the same name.

      Nota

      El nombre del índice será más fácil de recordar si usa su propio nombre.The name of the index might be easier to remember if you use your own name. Todos los índices de almacén de filas agrupado utilizan el nombre predeterminado que es ' ClusteredIndex_<GUID >'.All rowstore clustered indexes use the default name which is 'ClusteredIndex_<GUID>'.

    --Process for dropping a clustered index.  
    --First, look up the name of the clustered rowstore index.  
    --Clustered rowstore indexes always use the DEFAULT name ‘ClusteredIndex_<GUID>’.  
    SELECT i.name   
    FROM sys.indexes i   
    JOIN sys.tables t  
    ON ( i.type_desc = 'CLUSTERED' ) WHERE t.name = 'MyFactTable';  
    
    --Drop the clustered rowstore index.  
    DROP INDEX ClusteredIndex_d473567f7ea04d7aafcac5364c241e09 ON MyDimTable;  
    
  4. 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.Convert the rowstore table to a columnstore table with a clustered columnstore index.

    --Option 1: Convert to columnstore and name the new clustered columnstore index MyCCI.  
    CREATE CLUSTERED COLUMNSTORE INDEX MyCCI ON MyFactTable;  
    
    --Option 2: Convert to columnstore and use the rowstore clustered   
    --index name for the columnstore clustered index name.  
    --First, look up the name of the clustered rowstore index.  
    SELECT i.name   
    FROM sys.indexes i  
    JOIN sys.tables t   
    ON ( i.type_desc = 'CLUSTERED' )  
    WHERE t.name = 'MyFactTable';  
    
    --Second, create the clustered columnstore index and   
    --Replace ClusteredIndex_d473567f7ea04d7aafcac5364c241e09  
    --with the name of your clustered index.  
    CREATE CLUSTERED COLUMNSTORE INDEX   
    ClusteredIndex_d473567f7ea04d7aafcac5364c241e09  
     ON MyFactTable  
    WITH DROP_EXISTING = ON;  
    

E.E. Convertir una tabla de almacén de columnas en una tabla de almacén de filas con un índice clústerConvert a columnstore table to a rowstore table with a clustered index

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.To convert a columnstore table to a rowstore table with a clustered index, use the CREATE INDEX statement with the DROP_EXISTING option.

CREATE CLUSTERED INDEX ci_MyTable   
ON MyFactTable  
WITH ( DROP EXISTING = ON );  

F.F. Convertir una tabla de almacén de columnas en un montón de almacenes de filasConvert a columnstore table to a rowstore heap

Para convertir una tabla de almacén de columnas en un montón de almacenes de filas, simplemente quite el índice clúster de almacén de columnas.To convert a columnstore table to a rowstore heap, simply drop the clustered columnstore index.

DROP INDEX MyCCI   
ON MyFactTable;  

G.G. Desfragmentar al volver a generar el índice agrupado de almacén de columnas completoDefragment by rebuilding the entire clustered columnstore index

Se aplica a: SQL Server 2014Applies to: SQL Server 2014

Hay dos maneras de volver a generar todo el índice clúster de almacén de columnas.There are two ways to rebuild the full clustered columnstore index. Puede usar CREATE CLUSTERED COLUMNSTORE INDEX, o ALTER INDEX ( Transact-SQL ) y la opción REBUILD.You can use CREATE CLUSTERED COLUMNSTORE INDEX, or ALTER INDEX (Transact-SQL) and the REBUILD option. Con ambos métodos se obtienen los mismos resultados.Both methods achieve the same results.

Nota

A partir de SQL Server 2016, utilice ALTER INDEX REORGANIZE en lugar de volver a generar con los métodos descritos en este ejemplo.Beginning with SQL Server 2016, use ALTER INDEX REORGANIZE instead of rebuilding with the methods described in this example.

--Determine the Clustered Columnstore Index name of MyDimTable.  
SELECT i.object_id, i.name, t.object_id, t.name   
FROM sys.indexes i   
JOIN sys.tables t  
ON (i.type_desc = 'CLUSTERED COLUMNSTORE')  
WHERE t.name = 'RowstoreDimTable';  

--Rebuild the entire index by using CREATE CLUSTERED INDEX.  
CREATE CLUSTERED COLUMNSTORE INDEX my_CCI   
ON MyFactTable  
WITH ( DROP_EXISTING = ON );  

--Rebuild the entire index by using ALTER INDEX and the REBUILD option.  
ALTER INDEX my_CCI  
ON MyFactTable  
REBUILD PARTITION = ALL  
WITH ( DROP_EXISTING = ON );  

Ejemplos para los índices no agrupadosExamples for nonclustered columnstore indexes

A.A. Crear un índice de almacén como un índice secundario en una tabla de almacén de filasCreate a columnstore index as a secondary index on a rowstore table

En este ejemplo se crea un índice no clúster de almacén de columnas en una tabla de almacén de filas.This example creates a nonclustered columnstore index on a rowstore table. Índice de almacén de columnas solo puede crearse en esta situación.Only one columnstore index can be created in this situation. El índice de almacén de columnas necesita almacenamiento adicional porque contiene una copia de los datos en la tabla de almacén de filas.The columnstore index requires extra storage since it contains a copy of the data in the rowstore table. Este ejemplo crea una tabla simple y un índice clúster y, a continuación, muestra la sintaxis para crear un índice no clúster de almacén de columnas.This example creates a simple table and a clustered index, and then demonstrates the syntax of creating a nonclustered columnstore index.

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.B. Crea un índice de almacén de columnas no clúster sencillo utilizando todas las opcionesCreate a simple nonclustered columnstore index using all options

En el ejemplo siguiente se muestra la sintaxis para crear un índice no clúster de almacén de columnas usando todas las opciones.The following example demonstrates the syntax of creating a nonclustered columnstore index by using all options.

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 con tablas con particiones, vea información general de los índices de almacén de columnas.For a more complex example using partitioned tables, see Columnstore Indexes Overview.

C.C. Crear un índice no clúster de almacén de columnas con un predicado filtradoCreate a nonclustered columnstore index with a filtered predicate

En el ejemplo siguiente se crea un índice de almacén de columnas no agrupado filtrado en la tabla Production.BillOfMaterials de la AdventureWorks2012AdventureWorks2012 base de datos.The following example creates a filtered nonclustered columnstore index on the Production.BillOfMaterials table in the AdventureWorks2012AdventureWorks2012 database. El predicado de filtro puede incluir columnas que no son columnas de clave en el índice filtrado.The filter predicate can include columns that are not key columns in the filtered index. El predicado de este ejemplo selecciona solo las filas en que EndDate no es NULL.The predicate in this example selects only the rows where EndDate is non-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.D. Cambiar los datos de un índice no clúster de almacén de columnasChange the data in a nonclustered columnstore index

Se aplica a: SQL Server 2012SQL Server 2012 a través de SQL Server 2014SQL Server 2014.Applies to: SQL Server 2012SQL Server 2012 through SQL Server 2014SQL Server 2014.

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.Once you create a nonclustered columnstore index on a table, you cannot directly modify the data in that table. Una consulta con INSERT, UPDATE, DELETE o MERGE, se produce un error y devuelve un mensaje de error.A query with INSERT, UPDATE, DELETE, or MERGE fails and returns an error message. Para agregar o modificar los datos de la tabla, puede hacer lo siguiente:To add or modify the data in the table, you can do one of the following:

  • Deshabilitar o quitar el índice de almacén de columnas.Disable or drop the columnstore index. Después puede actualizar los datos de la tabla.You can then update the data in the table. Si deshabilita el índice de almacén de columnas, puede regenerar el índice de almacén de columnas cuando termine de actualizar los datos.If you disable the columnstore index, you can rebuild the columnstore index when you finish updating the data. Por ejemplo,For example,

    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.Load data into a staging table that does not have a columnstore index. Genere un índice de almacén de columnas en la tabla de ensayo.Build a columnstore index on the staging table. Cambie la tabla de ensayo a una partición vacía de la tabla principal.Switch the staging table into an empty partition of the main table.

  • Cambiar una partición de la tabla con el índice de almacén de columnas a una tabla de ensayo vacía.Switch a partition from the table with the columnstore index into an empty staging table. Si hay un índice de almacén de columnas en la tabla de ensayo, deshabilítelo.If there is a columnstore index on the staging table, disable the columnstore index. Realice las actualizaciones que desee.Perform any updates. Genere (o regenere) el índice de almacén de columnas.Build (or rebuild) the columnstore index. Vuelva a cambiar la tabla de ensayo a la partición (ahora vacía) de la tabla principal.Switch the staging table back into the (now empty) partition of the main table.

Ejemplos: Almacenamiento de datos SQL de AzureAzure SQL Data Warehouse y Almacenamiento de datos paralelosParallel Data WarehouseExamples: Almacenamiento de datos SQL de AzureAzure SQL Data Warehouse and Almacenamiento de datos paralelosParallel Data Warehouse

A.A. Cambiar un índice agrupado a un índice de almacén de columnas agrupadoChange a clustered index to a clustered columnstore index

Mediante la instrucción CREATE CLUSTERED COLUMNSTORE INDEX con DROP_EXISTING = ON, puede:By using the CREATE CLUSTERED COLUMNSTORE INDEX statement with DROP_EXISTING = ON, you can:

  • Cambiar un índice clúster en un índice de almacén de columnas agrupado.Change a clustered index into a clustered columnstore index.

  • Volver a generar un índice de almacén de columnas agrupado.Rebuild a clustered columnstore index.

    Este ejemplo crea la tabla xDimProduct como una tabla de almacén de filas con un índice agrupado y, a continuación, usa CREATE CLUSTERED COLUMNSTORE INDEX para convertir la tabla de una tabla de almacén de filas en una tabla de almacén de columnas.This example creates the xDimProduct table as a rowstore table with a clustered index, and then uses CREATE CLUSTERED COLUMNSTORE INDEX to change the table from a rowstore table to a columnstore table.

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

--Change the existing clustered index   
--to a clustered columnstore index with the same name.  
--Look up the name of the index before running this statement.  
CREATE CLUSTERED COLUMNSTORE INDEX <index_name>   
ON xdimProduct   
WITH ( DROP_EXISTING = ON );  

B.B. Volver a generar un índice de almacén de columnas agrupadoRebuild a clustered columnstore index

Basándose en el ejemplo anterior, en este ejemplo se utiliza CREATE CLUSTERED COLUMNSTORE INDEX para volver a generar el índice de almacén de columnas agrupado existente denominado cci_xDimProduct.Building on the previous example, this example uses CREATE CLUSTERED COLUMNSTORE INDEX to rebuild the existing clustered columnstore index called cci_xDimProduct.

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

C.C. Cambiar el nombre de un índice de almacén de columnas agrupadoChange the name of a clustered columnstore index

Para cambiar el nombre de un índice de almacén de columnas agrupado, quite el índice de almacén de columnas clúster existente y, a continuación, vuelva a crear el índice con un nuevo nombre.To change the name of a clustered columnstore index, drop the existing clustered columnstore index, and then recreate the index with a new name.

Se recomienda sólo realizando esta operación con una tabla pequeña o una tabla vacía.We recommend only doing this operation with a small table or an empty table. Tarda mucho tiempo para quitar un índice de almacén de columnas agrupado grandes y volver a generar con un nombre diferente.It takes a long time to drop a large clustered columnstore index and rebuild with a different name.

Con el índice de almacén de columnas agrupado cci_xDimProduct del ejemplo anterior, en este ejemplo se quita el índice de almacén de columnas agrupado cci_xDimProduct y, a continuación, vuelve a crear el índice de almacén de columnas agrupado con el nombre mycci_xDimProduct.Using the cci_xDimProduct clustered columnstore index from the previous example, this example drops the cci_xDimProduct clustered columnstore index and then recreates the clustered columnstore index with the name 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.D. Convertir una tabla de almacén de columnas en una tabla de almacén de filas con un índice clústerConvert a columnstore table to a rowstore table with a clustered index

Puede haber una situación que desea quitar un índice de almacén de columnas agrupado y crear un índice agrupado.There might be a situation for which you want to drop a clustered columnstore index and create a clustered index. La tabla se almacena en formato de almacén de filas.This stores the table in rowstore format. Este ejemplo 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.This example converts a columnstore table to a rowstore table with a clustered index with the same name. Ninguno de los datos se pierde.None of the data is lost. Todos los datos que se va a la tabla de almacén de filas y las columnas enumeradas se convierte en las columnas de clave en el índice agrupado.All data goes to the rowstore table and the columns listed becomes the key columns in the clustered index.

--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.E. Convertir una tabla de almacén de columnas en un montón de almacén de filasConvert a columnstore table back to a rowstore heap

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.Use DROP INDEX (SQL Server PDW) to drop the clustered columnstore index and convert the table to a rowstore heap. En este ejemplo se convierte en la tabla cci_xDimProduct en un montón de almacén de filas.This example converts the cci_xDimProduct table to a rowstore heap. La tabla sigue distribuirse, pero se almacena como un montón.The table continues to be distributed, but is stored as a heap.

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