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

Se aplica a:Applies to: síSQL ServerSQL Server (todas las versiones admitidas) yesSQL ServerSQL Server (all supported versions) SíAzure SQL DatabaseAzure SQL DatabaseYesAzure SQL DatabaseAzure SQL Database SíInstancia administrada de Azure SQLAzure SQL Managed InstanceYesInstancia administrada de Azure SQLAzure SQL Managed Instance síAzure Synapse AnalyticsAzure Synapse AnalyticsyesAzure Synapse AnalyticsAzure Synapse Analytics síAlmacenamiento de datos paralelosParallel Data WarehouseyesAlmacenamiento de datos paralelosParallel Data WarehouseSe aplica a:Applies to: síSQL ServerSQL Server (todas las versiones admitidas) yesSQL ServerSQL Server (all supported versions) SíAzure SQL DatabaseAzure SQL DatabaseYesAzure SQL DatabaseAzure SQL Database SíInstancia administrada de Azure SQLAzure SQL Managed InstanceYesInstancia administrada de Azure SQLAzure SQL Managed Instance síAzure Synapse AnalyticsAzure Synapse AnalyticsyesAzure Synapse AnalyticsAzure Synapse Analytics síAlmacenamiento de datos paralelosParallel Data WarehouseyesAlmacenamiento de datos paralelosParallel Data Warehouse

Convierta una tabla de almacén de filas en un índice de almacén de columnas o cree 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. 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.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 2016 (13.x)SQL Server 2016 (13.x), puede crear la tabla como un índice clúster de almacén de columnas.Starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x), you can create the table as a clustered columnstore index. Ya no es necesario crear una tabla de almacén de filas y luego convertirla en un índice clúster de almacén de columnas.It is no longer necessary to first create a rowstore table and then convert it to a clustered columnstore index.

Sugerencia

Para obtener más información sobre las directrices de diseño de índices, vea 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.

Ejemplos:Skip to examples:

Escenarios:Go to scenarios:

Más información: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.table_name | schema_name.table_name | table_name }  
    [ 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  
  
-- Syntax for Azure Synapse Analytics and Parallel Data Warehouse  
  
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 y versiones anteriores, consulte Versiones anteriores de la documentación.To view Transact-SQL syntax for SQL Server 2014 and earlier, see Previous versions documentation.

ArgumentosArguments

Algunas de las opciones no están disponibles en todas las versiones del motor de base de datos.Some of the options are not available in all database engine versions. En la siguiente tabla se muestran las versiones cuando las opciones se introducen en los índices CLUSTERED COLUMNSTORE y NONCLUSTERED COLUMNSTORE:The following table shows the versions when the options are introduced in CLUSTERED COLUMNSTORE and NONCLUSTERED COLUMNSTORE indexes:

OpciónOption CLUSTEREDCLUSTERED NONCLUSTEREDNONCLUSTERED
COMPRESSION_DELAYCOMPRESSION_DELAY SQL Server 2016 (13.x)SQL Server 2016 (13.x) SQL Server 2016 (13.x)SQL Server 2016 (13.x)
DATA_COMPRESSIONDATA_COMPRESSION SQL Server 2016 (13.x)SQL Server 2016 (13.x) SQL Server 2016 (13.x)SQL Server 2016 (13.x)
ONLINEONLINE SQL Server 2019 (15.x)SQL Server 2019 (15.x) SQL Server 2017 (14.x)SQL Server 2017 (14.x)
WHERE, cláusulaWHERE clause N/DN/A SQL Server 2016 (13.x)SQL Server 2016 (13.x)

Todas las opciones están disponibles en Azure SQL Database.All options are available in Azure SQL Database.

CREATE CLUSTERED COLUMNSTORE INDEXCREATE 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.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 montón o un índice clúster, la tabla se convierte en un índice clúster de almacén de columnas.If the existing table is a heap or clustered index, the table is converted to a clustered columnstore index. Si la tabla ya está almacenada como un índice clúster de almacén de columnas, el índice existente se quita y se vuelve a compilar.If the table is already stored as a clustered columnstore index, the existing index is dropped and rebuilt.

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

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.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 un índice clúster, 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 compilar el índice clúster de almacén de columnas.If the table is already a columnstore, this statement rebuilds the clustered columnstore index. Para convertir a un índice ordenado de almacén de columnas agrupado, el índice existente debe ser un índice de almacén de columnas agrupado.To convert to an ordered clustered column store index the existing index must be a clustered columnstore index.

Opciones de WITHWITH options

DROP_EXISTING = [OFF] | ONDROP_EXISTING = [OFF] | ON

DROP_EXISTING = ON especifica que se quite el índice existente y se cree un nuevo índice de almacén de columnas.DROP_EXISTING = ON specifies to drop the existing index, and create a new columnstore index.

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.The default, DROP_EXISTING = OFF expects the index name is the same as the existing name. Se muestra un error si ya existe el nombre de índice especificado.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.

Los valores de max_degree_of_parallelism pueden ser:max_degree_of_parallelism values can be:

  • 1: suprime la generación de planes paralelos.1 - Suppress parallel plan generation.
  • >1: restringe 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.>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 usados es de 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.
CREATE CLUSTERED COLUMNSTORE INDEX cci ON Sales.OrderLines
       WITH (MAXDOP = 2);

Para obtener más información, vea Establecer la opción de configuración del servidor Grado máximo de paralelismo y Configurar operaciones de índice en paralelo.For more information, see Configure the max degree of parallelism Server Configuration Option, and Configure Parallel Index Operations.

COMPRESSION_DELAY = 0 | delay [ Minutes ]COMPRESSION_DELAY = 0 | delay [ Minutes ]

En una tabla basada en disco, delay especifica el número mínimo de minutos que debe permanecer un grupo de filas delta en estado CLOSED en el grupo de filas delta antes de que SQL Server pueda comprimirlo 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 realizan el seguimiento de los tiempos de inserción y actualización de filas individuales, SQL Server aplica el retraso a los grupos de filas delta en el estado CLOSED.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.

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

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:

  • COLUMNSTORE es el valor predeterminado y especifica que se comprima con la compresión de almacén de columnas que ofrezca el mejor rendimiento.COLUMNSTORE is the default and specifies to compress with the most performant columnstore compression. Es la elección habitual.This is the typical choice.
  • COLUMNSTORE_ARCHIVE comprime la tabla o la partición a un tamaño menor.COLUMNSTORE_ARCHIVE further compresses the table or partition to a smaller size. Use esta opción para situaciones como un archivo que requiera un tamaño menor de almacenamiento y pueda permitirse usar más tiempo para el almacenamiento y la 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.
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.For more information about compression, see Data Compression.

ONLINE = [ON | OFF]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.ON specifies that the columnstore index remains online and available while the new copy of the index is being built.
  • OFF especifica que el índice no está disponible mientras se compila la nueva copia.OFF specifies that the index is not available for use while the new copy is being built.
CREATE CLUSTERED COLUMNSTORE INDEX cci ON Sales.OrderLines
       WITH ( ONLINE = ON );

Opciones de ONON options

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 de grupo de archivos 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 van a crear las particiones de 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 función de partición que partition_scheme_name emplea.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.

" default "" 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).

CREATE [NONCLUSTERED] COLUMNSTORE INDEXCREATE [NONCLUSTERED] COLUMNSTORE INDEX

Crea un índice no clúster de almacén de columnas en memoria en una tabla de almacén de filas almacenada como un montón o un índice clúster.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. Se puede actualizar y se actualiza a medida que se modifica la tabla subyacente.It is updateable and is updated as the underlying table is changed. El índice no clúster de almacén de columnas de un índice clúster permite el 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 en la tabla, pero no es necesario que sea único en 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 los identificadores.Index names must follow the rules of identifiers.

( column [ , ... n ] )( column [ , ... n ] )
Especifica las columnas que se van a almacenar.Specifies the columns to store. Un índice no clúster de almacén de columnas 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 nombre de una, dos o tres partes de la tabla que contiene el índice.Specifies the one-, two-, or three-part name of the table that contains the index.

Opciones de WITHWITH options

DROP_EXISTING = [OFF] | ONDROP_EXISTING = [OFF] | ON

DROP_EXISTING = ON El índice existente se quita y se vuelve a compilar.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 de í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

Reemplaza la opción Establecer la opción de configuración del servidor Grado máximo de paralelismo durante 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.

Los valores de max_degree_of_parallelism pueden ser:max_degree_of_parallelism values can be:

  • 1: suprime la generación de planes paralelos.1 - Suppress parallel plan generation.
  • >1: restringe 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.>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 usados es de 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

Las operaciones de índices en paralelo no están disponibles en todas las ediciones de MicrosoftMicrosoftSQL ServerSQL Server.Parallel index operations are not available in every edition of MicrosoftMicrosoftSQL 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]
  • ON especifica que el índice de almacén de columnas permanece en línea y disponible mientras se compila la nueva copia del índice.ON specifies that the columnstore index remains online and available while the new copy of the index is being built.
  • OFF especifica que el índice no está disponible mientras se compila la nueva copia.OFF specifies that the index is not available for use while the new copy is being built. En índices no agrupados, la tabla base permanece disponible, solo el índice de almacén de columnas no agrupado no se usa para responder a consultas hasta que finaliza el nuevo índice.In nonclustered index, the base table remains available, only the nonclustered columnstore index is not used to satisfy queries until the new index is complete.
CREATE COLUMNSTORE INDEX ncci ON Sales.OrderLines (StockItemID, Quantity, UnitPrice, TaxRate) WITH ( ONLINE = ON );
COMPRESSION_DELAY = 0 | <delay>[Minutos]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 se pueda 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 indicar que si una fila no se ha modificado durante 120 minutos, se pueda 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. En el índice de almacén de columnas de las tablas basadas en disco, no se realiza el seguimiento del tiempo de inserción o actualización de una fila; en su lugar, se usa el tiempo de cierre del grupo de filas de diferencial como elemento intermedio de la fila.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 en el grupo de filas delta se han acumulado 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. 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. Las opciones son las siguientes:The options are as follows:

  • COLUMNSTORE es el valor predeterminado y especifica que se comprima con la compresión de almacén de columnas que ofrezca el mejor rendimiento.COLUMNSTORE - the default and specifies to compress with the most performant columnstore compression. Es la elección habitual.This is the typical choice.
  • COLUMNSTORE_ARCHIVE comprime la tabla o la partición a un tamaño menor.COLUMNSTORE_ARCHIVE - 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 más información sobre la compresión, vea Compresión de datos.For more information about compression, see Data Compression.

WHERE <filter_expression> [ AND <filter_expression> ]WHERE <filter_expression> [ AND <filter_expression> ]

Si se ha llamado a un predicado de filtro, especifica qué filas se va a incluir en el índice.Called a filter predicate, this specifies which rows to include in the index. SQL ServerSQL Server crea estadísticas filtradas sobre las filas de datos en el índice filtrado.creates filtered statistics on the data rows in the filtered index.

El predicado de filtro usa 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 orientación sobre índices filtrados, vea Crear índices filtrados.For guidance on filtered indexes, see Create Filtered Indexes.

Opciones de ONON options

Estas opciones especifican los grupos de archivos en los 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 a los que se asignan 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 dentro de 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 van a crear las particiones de 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 función de partición que partition_scheme_name emplea.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á limitado a las columnas de la definición de í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 no se especificó partition_scheme_name o filegroup y se crearon particiones en la tabla, el índice se coloca en el mismo esquema de partición y usa la misma columna de partición que en 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 sobre los índices con particiones, vea Partitioned Tables and Indexes (Tablas e índices con particiones).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 no se especifica filegroup_name 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.

" default "" 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 " default " u ON [ default ] .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).

PermisosPermissions

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

Notas generalesGeneral Remarks

Se puede crear 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.

Se puede crear un índice de almacén de columnas agrupadas y ordenado en columnas de cualquier tipo de datos que se admita en Azure Synapse Analytics (SQL Data Warehouse)Azure Synapse Analytics (SQL Data Warehouse), excepto en columnas de cadena.An ordered clustered columnstore index can be created on columns of any data types supported in Azure Synapse Analytics (SQL Data Warehouse)Azure Synapse Analytics (SQL Data Warehouse) except for string columns.

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

  • El optimizador de consultas usa el índice filtrado para crear 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 ACTIVARON ACTIVARON ACTIVARON ApagadoOFF
    ANSI_PADDINGANSI_PADDING ACTIVARON ACTIVARON ACTIVARON ApagadoOFF
    ANSI_WARNINGS*ANSI_WARNINGS* ACTIVARON ACTIVARON ACTIVARON ApagadoOFF
    ARITHABORTARITHABORT ACTIVARON ACTIVARON ApagadoOFF ApagadoOFF
    CONCAT_NULL_YIELDS_NULLCONCAT_NULL_YIELDS_NULL ACTIVARON ACTIVARON ACTIVARON ApagadoOFF
    NUMERIC_ROUNDABORTNUMERIC_ROUNDABORT ApagadoOFF ApagadoOFF ApagadoOFF ApagadoOFF
    QUOTED_IDENTIFIERQUOTED_IDENTIFIER ACTIVARON ACTIVARON ACTIVARON ApagadoOFF

    *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 sobre los índices filtrados, vea Crear índices filtrados.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
  • time [ ( n ) ]time [ ( n ) ]
  • float [ ( n ) ]float [ ( n ) ]
  • real [ ( n ) ]real [ ( n ) ]
  • decimal [ ( precision [ , scale ] ) ]decimal [ ( precision [ , scale ] ) ]
  • numeric [ ( precision [ , scale ] ) ]numeric [ ( precision [ , scale ] ) ]
  • moneymoney
  • SMALLMONEYsmallmoney
  • bigintbigint
  • intint
  • SMALLINTsmallint
  • TINYINTtinyint
  • bitbit
  • nvarchar [ ( n ) ]nvarchar [ ( n ) ]
  • nvarchar (max) [se aplica a SQL Server 2017 (14.x)SQL Server 2017 (14.x) y al nivel Premium, al nivel estándar (S3 y versiones posteriores) y a todos los niveles de ofertas de núcleo virtual, solo en los índices de almacén de columnas agrupado]nvarchar(max) (Applies to SQL Server 2017 (14.x)SQL Server 2017 (14.x) and Premium tier, Standard tier (S3 and above), and all VCore offerings tiers, in clustered columnstore indexes only)
  • nchar [ ( n ) ]nchar [ ( n ) ]
  • varchar [ ( n ) ]varchar [ ( n ) ]
  • varchar (max) [se aplica a SQL Server 2017 (14.x)SQL Server 2017 (14.x) y al nivel Premium, al nivel estándar (S3 y versiones posteriores) y a todos los niveles de ofertas de núcleo virtual, solo en los índices de almacén de columnas agrupado]varchar(max) (Applies to SQL Server 2017 (14.x)SQL Server 2017 (14.x) and Premium tier, Standard tier (S3 and above), and all VCore offerings tiers, in clustered columnstore indexes only)
  • char [ ( n ) ]char [ ( n ) ]
  • varbinary [ ( n ) ]varbinary [ ( n ) ]
  • varbinary (max) [se aplica a SQL Server 2017 (14.x)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 agrupado]varbinary (max) (Applies to SQL Server 2017 (14.x)SQL Server 2017 (14.x) and Azure SQL Database at Premium tier, Standard tier (S3 and above), and all VCore offerings tiers, in clustered columnstore indexes only)
  • binary [ ( n ) ]binary [ ( n ) ]
  • uniqueidentifier (Se aplica a SQL Server 2014 (12.x)SQL Server 2014 (12.x) y versiones posteriores)uniqueidentifier (Applies to SQL Server 2014 (12.x)SQL Server 2014 (12.x) and later)

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

Las columnas que usan alguno de los siguientes tipos de datos no pueden incluirse 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 2016 (13.x)SQL Server 2016 (13.x) y versiones anteriores e índices no clúster de almacén de columnas)nvarchar(max), varchar(max), and varbinary(max) (Applies to SQL Server 2016 (13.x)SQL Server 2016 (13.x) 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 2012 (11.x)SQL Server 2012 (11.x))uniqueidentifier (Applies to SQL Server 2012 (11.x)SQL Server 2012 (11.x))

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

  • No puede tener más de 1024 columnas.Cannot have more than 1024 columns.
  • No se pueden crear como índice basado en restricciones.Cannot be created as a constraint-based index. 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.It is possible to have unique constraints, primary key constraints, and foreign key constraints on a table with a columnstore index. Las restricciones se aplican siempre con un índice de almacén de filas.Constraints are always enforced with a row-store index. Las restricciones no se pueden aplicar con un índice de almacén de columnas (agrupado o no agrupado).Constraints cannot be enforced with a columnstore (clustered or nonclustered) index.
  • No puede incluir ninguna columna dispersa.Cannot include a sparse column.
  • No se pueden modificar mediante la instrucción ALTER INDEX .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 compilar un índice de almacén de columnas.You can use ALTER INDEX to disable and rebuild a columnstore index.
  • No se pueden crear mediante la palabra clave INCLUDE .Cannot be created by using the INCLUDE keyword.
  • No pueden incluir las palabras clave ASC ni DESC 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 se pueden incluir columnas de objetos grandes (LOB) de tipo nvarchar(max), varchar(max) y varbinary(max) en índices no clúster de almacén de columnas.Cannot include large object (LOB) columns of type nvarchar(max), varchar(max), and varbinary(max) in nonclustered column store indexes. Solo los índices clúster de almacén de columnas admiten tipos LOB, a partir de la versión SQL Server 2017 (14.x)SQL Server 2017 (14.x) y Azure SQL Database configurados en el nivel Premium, nivel estándar (S3 y posteriores) y en todos los niveles de ofertas de núcleo virtual.Only clustered columnstore indexes support LOB types, beginning in SQL Server 2017 (14.x)SQL Server 2017 (14.x) version and Azure SQL Database configured at Premium tier, Standard tier (S3 and above), and all VCore offerings tiers tier. Tenga en cuenta que las versiones anteriores no admiten tipos LOB en los índices clúster y no clúster de almacén de columnas.Note, prior versions do not support LOB types in clustered and nonclustered columnstore indexes.

Nota

A partir de SQL Server 2016 (13.x)SQL Server 2016 (13.x), se puede crear un índice de almacén de columnas no agrupado en una vista indexada.Starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x), you can create a nonclustered columnstore index on an indexed view.

Los índices de almacén de columnas no se pueden 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 clúster de almacén de columnas puede contener una columna calculada no persistente.Starting with SQL Server 2017, a clustered columnstore index can contain a non-persisted computed column. Pero en SQL Server 2017, los índices clúster de almacén de columnas no pueden contener columnas calculadas persistentes y no se pueden crear índices no clúster 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 formato de almacenamiento vardecimal (un índice de almacén de columnas ya está comprimido en otro formato).Page and row compression, and vardecimal storage format (A columnstore index is already compressed in a different format.)
  • ReplicaciónReplication
  • Secuencia de archivosFilestream

No puede usar cursores ni desencadenadores en una tabla con un índice clúster de almacén de columnas.You cannot use cursors or triggers on a table with a clustered columnstore index. Esta restricción no se aplica a los índices no clúster de almacén de columnas; puede usar 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 2014 (12.x)SQL Server 2014 (12.x)SQL Server 2014 (12.x)SQL Server 2014 (12.x) specific limitations
Estas limitaciones solo se aplican a SQL Server 2014 (12.x)SQL Server 2014 (12.x).These limitations apply only to SQL Server 2014 (12.x)SQL Server 2014 (12.x). En esta versión se presentaron los índices clúster de almacén de columnas actualizables.In this release, we introduced updateable clustered columnstore indexes. Los índices no clúster de almacén de consultas seguían siendo de solo lectura.Nonclustered columnstore indexes were still read-only.

  • Seguimiento de cambios.Change tracking. No se puede usar el seguimiento de cambios con índices de almacén de columnas.You cannot use change tracking with columnstore indexes.
  • Captura de datos modificados.Change data capture. No se puede usar la captura de datos modificados con los índices no clúster de almacén de columnas (NCCI) porque son de solo lectura.You cannot use change data capture for nonclustered columnstore index (NCCI) because they are read-only. Sí funciona con los índices clúster de almacén de columnas (CCI).It does work for clustered columnstore indexes (CCI).
  • Secundario legible.Readable secondary. No se puede acceder a un índice clúster de almacén de columnas (CCI) desde un secundario legible de un grupo de disponibilidad Always On legible.You cannot access a clustered columnstore index (CCI) from a readable secondary of an Always OnReadable availability group. Puede acceder a un índice no clúster de almacén de columnas (NCCI) desde un secundario 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 (12.x)SQL Server 2014 (12.x) usa MARS para las conexiones de solo lectura a las tablas con un índice de almacén de columnas.uses MARS for read-only connections to tables with a columnstore index. Pero SQL Server 2014 (12.x)SQL Server 2014 (12.x) no es compatible con MARS para operaciones simultáneas de lenguaje de manipulación de datos (DML) en una tabla con un índice de almacén de columnas.However, SQL Server 2014 (12.x)SQL Server 2014 (12.x) does not support MARS for concurrent data manipulation language (DML) operations on a table with a columnstore index. Cuando ocurre esto, SQL ServerSQL Server termina las conexiones y anula las transacciones.When this occurs, SQL ServerSQL Server terminates the connections and aborts the transactions.
  • Los índices de almacén de columnas no agrupados no se pueden crear en una vista o vista indexada.Nonclustered columnstore indexes cannot be created on a view or indexed view.

Para obtener más información sobre las ventajas de rendimiento y las limitaciones de los índices de almacén de columnas, vea Introducción a 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 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.

En este ejemplo se muestra cómo administrar í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, a partir de SQL Server 2016 (13.x)SQL Server 2016 (13.x), no es necesaria ninguna acción especial; SQL ServerSQL Server define automáticamente y vuelve a compilar los índices no clúster en el nuevo índice clúster de almacén de columnas.Actually, beginning with SQL Server 2016 (13.x)SQL Server 2016 (13.x) no special action is required; SQL ServerSQL Server automatically defines and rebuilds the nonclustered indexes on the new clustered columnstore index.

Si quiere quitar los índices no clúster, use 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 clúster 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 2012 (11.x)SQL Server 2012 (11.x) y SQL Server 2014 (12.x)SQL Server 2014 (12.x), no se podía crear un índice no clúster en un índice de almacén de columnas.In SQL Server 2012 (11.x)SQL Server 2012 (11.x) and SQL Server 2014 (12.x)SQL Server 2014 (12.x), 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 nonclustered 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 nonclustered index.  
    CREATE INDEX my_index ON MyFactTable ( ProductKey, OrderDateKey );  
    
  2. Quite todos los índices no agrupados de la tabla de almacén de filas.Drop all nonclustered indexes from the rowstore table.

    --Drop all nonclustered 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 quita el índice clúster, el nuevo índice clúster de almacén de columnas 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 agrupados de almacén de filas usan el nombre predeterminado, "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 mediante la recompilación de todo el índice clúster de almacén de columnasDefragment by rebuilding the entire clustered columnstore index

Se aplica a: SQL Server 2014 (12.x)SQL Server 2014 (12.x)Applies to: SQL Server 2014 (12.x)SQL Server 2014 (12.x)

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 (13.x)SQL Server 2016 (13.x), use ALTER INDEX...REORGANIZE en lugar de recompilar con los métodos que se describen en este ejemplo.Starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x), 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 de índices no clúster de almacén de columnasExamples for nonclustered columnstore indexes

A.A. Crear un índice de almacén de columnas en un índice secundario de 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. En esta situación solo se puede crear un índice de almacén de columnas.Only one columnstore index can be created in this situation. 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.The columnstore index requires extra storage since it contains a copy of the data in the rowstore table. En el ejemplo se crean una tabla simple y un índice clúster y luego se 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. Crear un índice no clúster de almacén de columnas simple con 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 Introducción a 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 no clúster de almacén de columnas filtrado en la tabla Production.BillOfMaterials de la base de datos AdventureWorks2012AdventureWorks2012.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. Modificar 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 2012 (11.x)SQL Server 2012 (11.x) a SQL Server 2014 (12.x)SQL Server 2014 (12.x).Applies to: SQL Server 2012 (11.x)SQL Server 2012 (11.x) through SQL Server 2014 (12.x)SQL Server 2014 (12.x).

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 genera 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: Azure Synapse Analytics (SQL Data Warehouse)Azure Synapse Analytics (SQL Data Warehouse) y Almacenamiento de datos paralelosParallel Data WarehouseExamples: Azure Synapse Analytics (SQL Data Warehouse)Azure Synapse Analytics (SQL Data Warehouse) and Almacenamiento de datos paralelosParallel Data Warehouse

A.A. Convertir un índice clúster en un índice clúster de almacén de columnasChange 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:

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

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

En este ejemplo se crea la tabla xDimProduct como una tabla de almacén de filas con un índice clúster y luego se usa CREATE CLUSTERED COLUMNSTORE INDEX para convertir la 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 compilar un índice clúster de almacén de columnasRebuild a clustered columnstore index

A partir del ejemplo anterior, en este ejemplo se usa CREATE CLUSTERED COLUMNSTORE INDEX para volver a compilar el índice clúster de almacén de columnas 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 clúster de almacén de columnasChange the name of a clustered columnstore index

Para cambiar el nombre de un índice clúster de almacén de columnas, quite el índice clúster de almacén de columnas existente y luego 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 realizar esta operación solo con una tabla pequeña o una tabla vacía.We recommend only doing this operation with a small table or an empty table. Se tarda mucho en quitar un índice clúster de almacén de columnas grande y en volver a compilarlo con otro nombre.It takes a long time to drop a large clustered columnstore index and rebuild with a different name.

Con el índice clúster de almacén de columnas cci_xDimProduct del ejemplo anterior, en este ejemplo se quita el índice clúster de almacén de columnas cci_xDimProduct y luego se vuelve a crear el índice clúster de almacén de columnas 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 en la que quiera quitar un índice clúster de almacén de columnas y crear un índice clúster.There might be a situation for which you want to drop a clustered columnstore index and create a clustered index. Así se almacena la tabla en formato de almacén de filas.This stores the table in rowstore format. 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.This example converts a columnstore table to a rowstore table with a clustered index with the same name. No se pierde ningún dato.None of the data is lost. Todos los datos van a la tabla de almacén de filas y las columnas enumeradas se convierten en las columnas de clave del índice clúster.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. Volver a 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 clúster de almacén de columnas 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 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 se sigue distribuyendo, 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;  

F.F. Crear un índice de almacén de columnas agrupado ordenado en una tabla sin índiceCreate an ordered clustered columnstore index on a table with no index

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

G.G. Convertir un índice de almacén de columnas agrupado en un índice de almacén de columnas agrupado ordenadoConvert a clustered columnstore index to an ordered clustered columnstore index

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

H.H. Agregar una columna a la ordenación de un índice de almacén de columnas agrupado ordenadoAdd a column to the ordering of an ordered clustered columnstore index

-- The original ordered clustered columnstore index was ordered on SHIPDATE column only.  Add PRODUCTKEY column to the ordering.
CREATE CLUSTERED COLUMNSTORE INDEX cci ON Sales.OrderLines
ORDER ( SHIPDATE, PRODUCTKEY );
WITH (DROP_EXISTING = ON)

I.I. Cambiar el ordinal de las columnas ordenadasChange the ordinal of ordered columns

-- The original ordered clustered columnstore index was ordered on SHIPDATE, PRODUCTKEY.  Change the ordering to PRODUCTKEY, SHIPDATE.  
CREATE CLUSTERED COLUMNSTORE INDEX cci ON Sales.OrderLines
ORDER ( PRODUCTKEY,SHIPDATE );
WITH (DROP_EXISTING = ON)