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

SE APLICA A: síSQL Server síAzure SQL Database síAzure SQL Data Warehouse síAlmacenamiento de datos paralelos APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure SQL Data Warehouse yesParallel Data Warehouse

Crea un índice relacional en una tabla o una vista.Creates a relational index on a table or view. También se denomina índice de almacén de filas porque es un índice de árbol B agrupado o no agrupado.Also called a rowstore index because it is either a clustered or nonclustered B-tree index. Puede crear un índice de almacén de filas antes de que haya datos en la tabla.You can create a rowstore index before there is data in the table. Utilice un índice de almacén de filas para mejorar el rendimiento de las consultas, especialmente cuando las consultas hacen la selección en columnas específicas o requieren que los valores se ordenen en un orden concreto.Use a rowstore index to improve query performance, especially when the queries select from specific columns or require values to be sorted in a particular order.

Nota

En estos momentos, Almacenamiento de datos SQLSQL Data Warehouse y Almacenamiento de datos paralelosParallel Data Warehouse no admiten las restricciones Unique.Almacenamiento de datos SQLSQL Data Warehouse and Almacenamiento de datos paralelosParallel Data Warehouse currently do not support Unique constraints. Los ejemplos que hacen referencia a las restricciones Unique solo son aplicables a SQL ServerSQL Server y SQL DatabaseSQL Database.Any examples referencing Unique Constraints are only applicable to SQL ServerSQL Server and SQL DatabaseSQL Database.

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 sencillos:Simple examples:

-- Create a nonclustered index on a table or view
CREATE INDEX i1 ON t1 (col1);
--Create a clustered index on a table and use a 3-part name for the table
CREATE CLUSTERED INDEX i1 ON d1.s1.t1 (col1);
-- Syntax for SQL Server and Azure SQL Database
-- Create a nonclustered index with a unique constraint
-- on 3 columns and specify the sort order for each column
CREATE UNIQUE INDEX i1 ON t1 (col1 DESC, col2 ASC, col3 DESC);

Escenario clave:Key scenario:

A partir de SQL Server 2016 (13.x)SQL Server 2016 (13.x) y SQL DatabaseSQL Database, utilice un índice no agrupado en un índice de almacén de columnas para mejorar el rendimiento de las consultas de almacenamiento de datos.Starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x) and SQL DatabaseSQL Database, use a nonclustered index on a columnstore index to improve data warehousing query performance. Para obtener más información, vea Almacenamiento de datos de índices de almacén de columnas.For more information, see Columnstore Indexes - Data Warehouse.

Para los tipos adicionales de índices, consulte:For additional types of indexes, see:

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

SintaxisSyntax

Sintaxis de SQL Server y Azure SQL DatabaseSyntax for SQL Server and Azure SQL Database

CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name
    ON <object> ( column [ ASC | DESC ] [ ,...n ] )
    [ INCLUDE ( column_name [ ,...n ] ) ]
    [ WHERE <filter_predicate> ]
    [ WITH ( <relational_index_option> [ ,...n ] ) ]
    [ ON { partition_scheme_name ( column_name )
         | filegroup_name
         | default
         }
    ]
    [ FILESTREAM_ON { filestream_filegroup_name | partition_scheme_name | "NULL" } ]
  
[ ; ]
  
<object> ::=
{ database_name.schema_name.table_or_view_name | schema_name.table_or_view_name | table_or_view_name }

<relational_index_option> ::=
{
    PAD_INDEX = { ON | OFF }
  | FILLFACTOR = fillfactor
  | SORT_IN_TEMPDB = { ON | OFF }
  | IGNORE_DUP_KEY = { ON | OFF }
  | STATISTICS_NORECOMPUTE = { ON | OFF }
  | STATISTICS_INCREMENTAL = { ON | OFF }
  | DROP_EXISTING = { ON | OFF }
  | ONLINE = { ON | OFF }
  | RESUMABLE = {ON | OF }
  | MAX_DURATION = <time> [MINUTES]
  | ALLOW_ROW_LOCKS = { ON | OFF }
  | ALLOW_PAGE_LOCKS = { ON | OFF }
  | OPTIMIZE_FOR_SEQUENTIAL_KEY = { ON | OFF}
  | MAXDOP = max_degree_of_parallelism
  | DATA_COMPRESSION = { NONE | ROW | PAGE}
     [ ON PARTITIONS ( { <partition_number_expression> | <range> }
     [ , ...n ] ) ]
}

<filter_predicate> ::=
    <conjunct> [ AND <conjunct> ]

<conjunct> ::=
    <disjunct> | <comparison>

<disjunct> ::=
        column_name IN (constant ,...n)

<comparison> ::=
        column_name <comparison_op> constant

<comparison_op> ::=
    { IS | IS NOT | = | <> | != | > | >= | !> | < | <= | !< }

<range> ::=
<partition_number_expression> TO <partition_number_expression>

Índice relacional compatible con versiones anterioresBackward Compatible Relational Index

Importante

La estructura de sintaxis de índice relacional compatible con versiones anteriores se quitará en una versión futura de SQL ServerSQL Server.The backward compatible relational index syntax structure will be removed in a future version of SQL ServerSQL Server. Evite usar esta estructura de sintaxis en los nuevos trabajos de desarrollo y piense en modificar las aplicaciones que la usan actualmente.Avoid using this syntax structure in new development work, and plan to modify applications that currently use the feature. En su lugar, use la estructura de sintaxis especificada en <relational_index_option>.Use the syntax structure specified in <relational_index_option> instead.

CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name
    ON <object> ( column_name [ ASC | DESC ] [ ,...n ] )
    [ WITH <backward_compatible_index_option> [ ,...n ] ]
    [ ON { filegroup_name | "default" } ]

<object> ::=
{
    [ database_name. [ owner_name ] . | owner_name. ]
    table_or_view_name
}

<backward_compatible_index_option> ::=
{
    PAD_INDEX
  | FILLFACTOR = fillfactor
  | SORT_IN_TEMPDB
  | IGNORE_DUP_KEY
  | STATISTICS_NORECOMPUTE
  | DROP_EXISTING
}

Sintaxis para Azure SQL Data Warehouse y Almacenamiento de datos paralelosSyntax for Azure SQL Data Warehouse and Parallel Data Warehouse

CREATE [ CLUSTERED | NONCLUSTERED ] INDEX index_name
    ON [ database_name . [ schema ] . | schema . ] table_name
        ( { column [ ASC | DESC ] } [ ,...n ] )
    WITH ( DROP_EXISTING = { ON | OFF } )
[;]

ArgumentosArguments

UNIQUEUNIQUE
Crea un índice único en una tabla o una vista.Creates a unique index on a table or view. Un índice único es aquel en el que no se permite que dos filas tengan el mismo valor de clave del índice.A unique index is one in which no two rows are permitted to have the same index key value. El índice clúster de una vista debe ser único.A clustered index on a view must be unique.

Motor de base de datosDatabase Engine no admite la creación de un índice único sobre columnas que ya contengan valores duplicados, independientemente de si se ha establecido o no IGNORE_DUP_KEY en ON.The Motor de base de datosDatabase Engine does not allow creating a unique index on columns that already include duplicate values, whether or not IGNORE_DUP_KEY is set to ON. Si se intenta, Motor de base de datosDatabase Engine muestra un mensaje de error.If this is tried, the Motor de base de datosDatabase Engine displays an error message. Se deben quitar los valores duplicados para poder crear un índice único en la columna o columnas.Duplicate values must be removed before a unique index can be created on the column or columns. Las columnas que se utilizan en un índice único se deben establecer en NOT NULL, dado que varios valores NULL se consideran duplicados cuando se crea un índice único.Columns that are used in a unique index should be set to NOT NULL, because multiple null values are considered duplicates when a unique index is created.

CLUSTEREDCLUSTERED
Crea un índice en el que el orden lógico de los valores de clave determina el orden físico de las filas correspondientes de la tabla.Creates an index in which the logical order of the key values determines the physical order of the corresponding rows in a table. El nivel inferior, u hoja, de un índice clúster contiene las filas de datos reales de la tabla.The bottom, or leaf, level of the clustered index contains the actual data rows of the table. Una tabla o vista permite un índice clúster al mismo tiempo.A table or view is allowed one clustered index at a time.

Una vista con un índice clúster único se denomina vista indizada.A view with a unique clustered index is called an indexed view. La creación de un índice clúster único en una vista materializa físicamente la vista.Creating a unique clustered index on a view physically materializes the view. Es necesario crear un índice clúster único en una vista para poder definir otros índices en la misma vista.A unique clustered index must be created on a view before any other indexes can be defined on the same view. Para obtener más información, vea Crear vistas indexadas.For more information, see Create Indexed Views.

Cree el índice clúster antes de crear los índices no clúster.Create the clustered index before creating any nonclustered indexes. Los índices no clúster existentes en las tablas se vuelven a generar al crear un índice clúster.Existing nonclustered indexes on tables are rebuilt when a clustered index is created.

Si no se especifica CLUSTERED, se crea un índice no agrupado.If CLUSTERED is not specified, a nonclustered index is created.

Nota

Debido a que el nivel hoja de un índice clúster y sus páginas de datos son, por definición, lo mismo, la creación de un índice clúster y la utilización de la cláusula ON partition_scheme_name u ON filegroup_name mueven una tabla desde el grupo de archivos en el que se creó la tabla al nuevo grupo de archivos o esquema de partición.Because the leaf level of a clustered index and the data pages are the same by definition, creating a clustered index and using the ON partition_scheme_name or ON filegroup_name clause effectively moves a table from the filegroup on which the table was created to the new partition scheme or filegroup. Antes de crear tablas o índices en grupos de archivos específicos, compruebe cuáles están disponibles y que esos grupos de archivos tengan suficiente espacio disponible para el índice.Before creating tables or indexes on specific filegroups, verify which filegroups are available and that they have enough empty space for the index.

En algunos casos, al crear un índice clúster se pueden habilitar previamente los índices deshabilitados.In some cases creating a clustered index can enable previously disabled indexes. Para obtener más información, consulte Habilitar índices y restricciones y Deshabilitar índices y restricciones.For more information, see Enable Indexes and Constraints and Disable Indexes and Constraints.

NONCLUSTEREDNONCLUSTERED
Crea un índice que especifica la ordenación lógica de una tabla.Creates an index that specifies the logical ordering of a table. Con un índice no clúster, el orden físico de las filas de datos es independiente del orden indizado.With a nonclustered index, the physical order of the data rows is independent of their indexed order.

Cada tabla puede tener hasta 999 índices no clúster, independientemente de cómo se crean: de forma implícita con las restricciones PRIMARY KEY y UNIQUE, o explícita con CREATE INDEX.Each table can have up to 999 nonclustered indexes, regardless of how the indexes are created: either implicitly with PRIMARY KEY and UNIQUE constraints, or explicitly with CREATE INDEX.

Para las vistas indizadas, solo se pueden crear índices no clúster en una vista que ya tenga definido un índice clúster único.For indexed views, nonclustered indexes can be created only on a view that has a unique clustered index already defined.

Si no se especifica, el tipo de índice predeterminado es NONCLUSTERED.If not otherwise specified, the default index type is NONCLUSTERED.

index_name index_name
Es el nombre del índice.Is the name of the index. Los nombres de índice deben ser únicos en una tabla o vista, pero no es necesario que sean únicos en una base de datos.Index names must be unique within a table or view, but do not have to be unique within a database. Los nombres de índice deben seguir las reglas de los identificadores.Index names must follow the rules of identifiers.

column column
Es la columna o columnas en las que se basa el índice.Is the column or columns on which the index is based. Especifique dos o más nombres de columna para crear un índice compuesto sobre los valores combinados de las columnas especificadas.Specify two or more column names to create a composite index on the combined values in the specified columns. Enumere las columnas que desee incluir en el índice compuesto (en orden de prioridad) entre paréntesis después de table_or_view_name.List the columns to be included in the composite index, in sort-priority order, inside the parentheses after table_or_view_name.

Se pueden combinar hasta 32 columnas en la clave de un único índice compuesto.Up to 32 columns can be combined into a single composite index key. Todas las columnas de una clave del índice compuesto deben encontrarse en la misma tabla o vista.All the columns in a composite index key must be in the same table or view. El tamaño máximo permitido de los valores de índice combinados es de 900 bytes para un índice agrupado o de 1700 para un índice no agrupado.The maximum allowable size of the combined index values is 900 bytes for a clustered index, or 1,700 for a nonclustered index. Los límites son 16 columnas y 900 bytes para las versiones anteriores a SQL DatabaseSQL Database y SQL Server 2016 (13.x)SQL Server 2016 (13.x).The limits are 16 columns and 900 bytes for versions before SQL DatabaseSQL Database and SQL Server 2016 (13.x)SQL Server 2016 (13.x).

Las columnas de los tipos de datos de objetos grandes (LOB) ntext, text, varchar(max) , nvarchar(max) , varbinary(max) , xml o image no pueden especificarse como columnas clave para un índice.Columns that are of the large object (LOB) data types ntext, text, varchar(max), nvarchar(max), varbinary(max), xml, or image cannot be specified as key columns for an index. Además, una definición de vista no puede incluir columnas ntext, text ni image, aunque no se haga referencia a ellas en la instrucción CREATE INDEX.Also, a view definition cannot include ntext, text, or image columns, even if they are not referenced in the CREATE INDEX statement.

Puede crear índices en columnas de tipo definido por el usuario CLR si el tipo admite el orden binario.You can create indexes on CLR user-defined type columns if the type supports binary ordering. También puede crear índices en columnas calculadas que están definidas como invocaciones de método de una columna de tipo definido por el usuario, siempre que los métodos estén marcados como deterministas y no realicen operaciones de acceso a datos.You can also create indexes on computed columns that are defined as method invocations off a user-defined type column, as long as the methods are marked deterministic and do not perform data access operations. Para obtener más información sobre la indización de columnas de tipo definido por el usuario CLR, vea Tipos definidos por el usuario de CLR.For more information about indexing CLR user-defined type columns, see CLR User-defined Types.

[ ASC | DESC ][ ASC | DESC ]
Determina la dirección ascendente o descendente del orden de la columna de índice determinada.Determines the ascending or descending sort direction for the particular index column. El valor predeterminado es ASC.The default is ASC.

INCLUDE ( column [ , ... n ] ) INCLUDE (column [ ,... n ] )
Especifica las columnas que no son de clave que se agregarán en el nivel hoja del índice no clúster.Specifies the non-key columns to be added to the leaf level of the nonclustered index. El índice no clúster puede ser único o no único.The nonclustered index can be unique or non-unique.

Los nombres de columna no se pueden repetir en la lista INCLUDE y no se pueden utilizar simultáneamente como columnas de clave y que no son de clave.Column names cannot be repeated in the INCLUDE list and cannot be used simultaneously as both key and non-key columns. Los índices no clúster siempre contienen las columnas de índice clúster si se define un índice clúster en la tabla.Nonclustered indexes always contain the clustered index columns if a clustered index is defined on the table. Para más información, consulte Create Indexes with Included Columns.For more information, see Create Indexes with Included Columns.

Se admiten todos los tipos de datos, a excepción de text, ntexte image.All data types are allowed except text, ntext, and image. El índice se debe crear o regenerar sin conexión (ONLINE = OFF) si el tipo de datos de alguna de las columnas que no son de clave especificadas es varchar(max) , nvarchar(max) o varbinary(max) .The index must be created or rebuilt offline (ONLINE = OFF) if any one of the specified non-key columns are varchar(max), nvarchar(max), or varbinary(max) data types.

Las columnas calculadas que son deterministas, y precisas o imprecisas, pueden ser columnas incluidas.Computed columns that are deterministic and either precise or imprecise can be included columns. Las columnas calculadas derivadas de los tipos de datos image, ntext, text, varchar(max) , nvarchar(max) , varbinary(max) y xml se pueden incluir en columnas que no son clave, siempre que el tipo de datos de la columna calculada esté disponible como una columna incluida.Computed columns derived from image, ntext, text, varchar(max), nvarchar(max), varbinary(max), and xml data types can be included in non-key columns as long as the computed column data types is allowable as an included column. Para obtener más información, vea Indexes on Computed Columns.For more information, see Indexes on Computed Columns.

Para obtener información sobre cómo crear un índice XML, vea CREATE XML INDEX.For information on creating an XML index, see CREATE XML INDEX.

WHERE <filter_predicate>WHERE <filter_predicate>
Crea un índice filtrado especificando qué filas se van a incluir en el índice.Creates a filtered index by specifying which rows to include in the index. El índice filtrado debe ser un índice no clúster en una tabla.The filtered index must be a nonclustered index on a table. Crea las estadísticas filtradas para las filas de datos en el índice filtrado.Creates filtered statistics for the data rows in the filtered index.

El predicado de filtro utiliza la lógica de comparación simple y no puede hacer referencia a una columna calculada, a una columna UDT, a una columna de tipo de datos espacial o a una columna de tipo de datos hierarchyID.The filter predicate uses simple comparison logic and cannot reference a computed column, a UDT column, a spatial data type column, or a hierarchyID data type column. 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

Los índices filtrados no se aplican a los índices XML ni a los índices de texto completo.Filtered indexes do not apply to XML indexes and full-text indexes. Para los índices UNIQUE, solo las filas seleccionadas deben tener valores de índice únicos.For UNIQUE indexes, only the selected rows must have unique index values. Los índices filtrados no admiten la opción IGNORE_DUP_KEY.Filtered indexes do not allow the IGNORE_DUP_KEY option.

ON partition_scheme_name ( column_name ) ON partition_scheme_name ( column_name )
Se aplica a: SQL ServerSQL Server (desde SQL Server 2008SQL Server 2008 hasta SQL Server 2017SQL Server 2017) y Base de datos SQL de AzureAzure SQL Database.Applies to: SQL ServerSQL Server (SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017) and Base de datos SQL de AzureAzure SQL Database

Especifica el esquema de partición que define los grupos de archivos a los que se asignarán las particiones de un índice con particiones.Specifies the partition scheme that defines the filegroups onto which the partitions of a partitioned index will be mapped. El esquema de partición debe existir dentro de la base de datos mediante la ejecución de CREATE PARTITION SCHEME o ALTER PARTITION SCHEME.The partition scheme must exist within the database by executing either CREATE PARTITION SCHEME or ALTER 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 will be 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. Se puede especificar cualquier columna de la tabla base, excepto en el caso de partición de un índice UNIQUE, en el que se debe elegir column_name entre las columnas usadas como clave única.Any column in the base table can be specified, except when partitioning a UNIQUE index, column_name must be chosen from among those used as the unique key. Esta restricción permite que Motor de base de datosDatabase Engine compruebe la unicidad de los valores de clave en una única partición solamente.This restriction allows the Motor de base de datosDatabase Engine to verify uniqueness of key values within a single partition only.

Nota

Cuando se crean particiones en un índice clúster no único, Motor de base de datosDatabase Engine agrega de forma predeterminada la columna de partición a la lista de claves del índice clúster, en caso de que aún no se hubiera especificado.When you partition a non-unique, clustered index, the Motor de base de datosDatabase Engine by default adds the partitioning column to the list of clustered index keys, if it is not already specified. Cuando se crean particiones en un índice no clúster que tampoco es único, Motor de base de datosDatabase Engine agrega la columna de partición como una columna sin clave (incluida) del índice, si aún no se especificó.When partitioning a non-unique, nonclustered index, the Motor de base de datosDatabase Engine adds the partitioning column as a non-key (included) 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.

Nota

No se puede especificar un esquema de partición en un índice XML.You cannot specify a partitioning scheme on an XML index. Si se crean particiones en la tabla base, el índice XML usa el mismo esquema de partición que la tabla.If the base table is partitioned, the XML index uses the same partition scheme as the table.

Para más información sobre los índices con particiones, vea Tablas e índices con particiones.For more information about partitioning indexes, Partitioned Tables and Indexes.

ON filegroup_name ON filegroup_name
Se aplica a: SQL ServerSQL Server (de SQL Server 2008SQL Server 2008 a SQL Server 2017SQL Server 2017)Applies to: SQL ServerSQL Server (SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017)

Crea el índice especificado en el grupo de archivos especificado.Creates the specified index on the specified filegroup. Si no se ha especificado una ubicación y la tabla o vista no tiene particiones, el índice utiliza el mismo grupo de archivos que la tabla o vista subyacente.If no location is specified and the table or view 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.

ON " default " ON " default "
Se aplica a: SQL ServerSQL Server (desde SQL Server 2008SQL Server 2008 hasta SQL Server 2017SQL Server 2017) y Base de datos SQL de AzureAzure SQL Database.Applies to: SQL ServerSQL Server (SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017) and Base de datos SQL de AzureAzure SQL Database

Crea el índice especificado en el mismo grupo de archivos o esquema de partición que la tabla o la vista.Creates the specified index on the same filegroup or partition scheme as the table or view.

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 más información, consulte SET QUOTED_IDENTIFIER.For more information, see SET QUOTED_IDENTIFIER.

Nota

"default" no indica el grupo de archivos de base de datos predeterminado en el contexto de CREATE INDEX."default" does not indicate the database default filegroup in the context of CREATE INDEX. Esto difiere de CREATE TABLE, donde "default" busca la tabla en el grupo de archivos predeterminado de la base de datos.This differs from CREATE TABLE, where "default" locates the table on the database default filegroup.

[ FILESTREAM_ON { filestream_filegroup_name | partition_scheme_name | "NULL" } ][ FILESTREAM_ON { filestream_filegroup_name | partition_scheme_name | "NULL" } ]
Se aplica a: SQL ServerSQL Server (de SQL Server 2008SQL Server 2008 a SQL Server 2017SQL Server 2017)Applies to: SQL ServerSQL Server (SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017)

Especifica la posición de datos FILESTREAM para la tabla cuando se crea un índice clúster.Specifies the placement of FILESTREAM data for the table when a clustered index is created. La cláusula FILESTREAM_ON permite mover los datos FILESTREAM a otro esquema de partición o a otro grupo de archivos FILESTREAM.The FILESTREAM_ON clause allows FILESTREAM data to be moved to a different FILESTREAM filegroup or partition scheme.

filestream_filegroup_name es el nombre de un grupo de archivos FILESTREAM.filestream_filegroup_name is the name of a FILESTREAM filegroup. El grupo de archivos debe tener un archivo definido para el grupo de archivos, usando para ello las instrucciones CREATE DATABASE o ALTER DATABASE; en caso contrario, se produce un error.The filegroup must have one file defined for the filegroup by using a CREATE DATABASE or ALTER DATABASE statement; otherwise, an error is raised.

Si se crean particiones de la tabla, la cláusula FILESTREAM_ON deberá incluirse y especificar un esquema de partición de grupos de archivos FILESTREAM que utilice la misma función de partición y columnas de partición que el esquema de partición para la tabla.If the table is partitioned, the FILESTREAM_ON clause must be included and must specify a partition scheme of FILESTREAM filegroups that uses the same partition function and partition columns as the partition scheme for the table. En caso contrario, se produce un error.Otherwise, an error is raised.

Si la tabla no tiene particiones, no se pueden crear particiones en la columna FILESTREAM.If the table is not partitioned, the FILESTREAM column cannot be partitioned. Los datos FILESTREAM para la tabla deben estar almacenados en un grupo de archivos único que se especifica en la cláusula FILESTREAM_ON.FILESTREAM data for the table must be stored in a single filegroup that is specified in the FILESTREAM_ON clause.

FILESTREAM_ON NULL se puede especificar en una instrucción CREATE INDEX si se va a crear un índice clúster y la tabla no contiene una columna FILESTREAM.FILESTREAM_ON NULL can be specified in a CREATE INDEX statement if a clustered index is being created and the table does not contain a FILESTREAM column.

Para obtener más información, vea FILESTREAM (SQL Server).For more information, see FILESTREAM (SQL Server).

<object>::=<object>::=

Es el objeto completo o no que se indizará.Is the fully qualified or nonfully qualified object to be indexed.

database_name database_name
Es el nombre de la base de datos.Is the name of the database.

schema_name schema_name
Es el nombre del esquema al que pertenece la tabla o la vista.Is the name of the schema to which the table or view belongs.

table_or_view_name table_or_view_name
Es el nombre de la tabla o la vista que se va a indizar.Is the name of the table or view to be indexed.

La vista debe definirse con SCHEMABINDING para crear un índice en ella.The view must be defined with SCHEMABINDING to create an index on it. Es necesario crear un índice clúster único en una vista antes de crear los índices no clúster.A unique clustered index must be created on a view before any nonclustered index is created. Para obtener más información acerca de las vistas indizadas, vea la sección Comentarios.For more information about indexed views, see the Remarks section.

A partir de SQL Server 2016 (13.x)SQL Server 2016 (13.x), el objeto puede ser una tabla almacenada con un índice de almacén de columnas agrupado.Starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x), the object can be a table stored with a clustered columnstore index.

Base de datos SQL de AzureAzure SQL Database admite el formato de nombre de tres partes database_name.[schema_name].object_name cuando database_name es la base de datos actual o database_name es tempdb y object_name comienza por #.supports the three-part name format database_name.[schema_name].object_name when the database_name is the current database or the database_name is tempdb and the object_name starts with #.

<relational_index_option>::= <relational_index_option>::=
Especifica las opciones que se van a utilizar en la creación del índice.Specifies the options to use when you create the index.

PAD_INDEX = { ON | OFF }PAD_INDEX = { ON | OFF }
Se aplica a: SQL ServerSQL Server (desde SQL Server 2008SQL Server 2008 hasta SQL Server 2017SQL Server 2017) y Base de datos SQL de AzureAzure SQL Database.Applies to: SQL ServerSQL Server (SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017) and Base de datos SQL de AzureAzure SQL Database

Especifica el relleno del índice.Specifies index padding. El valor predeterminado es OFF.The default is OFF.

ONON
El porcentaje de espacio disponible especificado por fillfactor se aplica a páginas de nivel intermedio del índice.The percentage of free space that is specified by fillfactor is applied to the intermediate-level pages of the index.

No se especifica OFF ni fillfactor.OFF or fillfactor is not specified
Las páginas de nivel intermedio se llenan casi al máximo de su capacidad y dejan espacio suficiente para al menos una fila del tamaño máximo que puede tener el índice, considerando el conjunto de claves incluidas en las páginas de nivel intermedio.The intermediate-level pages are filled to near capacity, leaving sufficient space for at least one row of the maximum size the index can have, considering the set of keys on the intermediate pages.

La opción PAD_INDEX solamente resulta útil si también se especifica FILLFACTOR, porque PAD_INDEX utiliza el mismo porcentaje especificado por FILLFACTOR.The PAD_INDEX option is useful only when FILLFACTOR is specified, because PAD_INDEX uses the percentage specified by FILLFACTOR. Si el porcentaje especificado para FILLFACTOR no es lo suficientemente grande como para admitir una fila, Motor de base de datosDatabase Engine invalida internamente el porcentaje para permitir el valor mínimo.If the percentage specified for FILLFACTOR is not large enough to allow for one row, the Motor de base de datosDatabase Engine internally overrides the percentage to allow for the minimum. El número de filas de una página de nivel intermedio del índice no es nunca inferior a dos, independientemente de lo bajo que sea el valor de fillfactor.The number of rows on an intermediate index page is never less than two, regardless of how low the value of fillfactor.

En la sintaxis compatible con versiones anteriores, WITH PAD_INDEX es equivalente a WITH PAD_INDEX = ON.In backward compatible syntax, WITH PAD_INDEX is equivalent to WITH PAD_INDEX = ON.

FILLFACTOR = fillfactor FILLFACTOR =fillfactor
Se aplica a: SQL ServerSQL Server (desde SQL Server 2008SQL Server 2008 hasta SQL Server 2017SQL Server 2017) y Base de datos SQL de AzureAzure SQL Database.Applies to: SQL ServerSQL Server (SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017) and Base de datos SQL de AzureAzure SQL Database

Especifica un porcentaje que indica cuánto debe llenar el Motor de base de datosDatabase Engine el nivel hoja de cada página de índice durante la creación o nueva generación de los índices.Specifies a percentage that indicates how full the Motor de base de datosDatabase Engine should make the leaf level of each index page during index creation or rebuild. fillfactor debe ser un valor entero comprendido entre 1 y 100.fillfactor must be an integer value from 1 to 100. Si fillfactor es 100, el Motor de base de datosDatabase Engine crea índices con las páginas hoja llenas al máximo de su capacidad.If fillfactor is 100, the Motor de base de datosDatabase Engine creates indexes with leaf pages filled to capacity.

La configuración de FILLFACTOR solo se aplica cuando se crea o se vuelve a generar el índice.The FILLFACTOR setting applies only when the index is created or rebuilt. Motor de base de datosDatabase Engine no mantiene dinámicamente el porcentaje especificado de espacio disponible de las páginas.The Motor de base de datosDatabase Engine does not dynamically keep the specified percentage of empty space in the pages. Para ver la configuración del factor de relleno, use la vista de catálogo sys.indexes.To view the fill factor setting, use the sys.indexes catalog view.

Importante

La creación de un índice clúster con un valor de FILLFACTOR menor que 100 afecta a la cantidad de espacio de almacenamiento que ocupan los datos, porque Motor de base de datosDatabase Engine vuelve a distribuir los datos cuando crea el índice clúster.Creating a clustered index with a FILLFACTOR less than 100 affects the amount of storage space the data occupies because the Motor de base de datosDatabase Engine redistributes the data when it creates the clustered index.

Para obtener más información, vea Especificar el factor de relleno para un índice.For more information, see Specify Fill Factor for an Index.

SORT_IN_TEMPDB = { ON | OFF }SORT_IN_TEMPDB = { ON | OFF }
Se aplica a: SQL ServerSQL Server (desde SQL Server 2008SQL Server 2008 hasta SQL Server 2017SQL Server 2017) y Base de datos SQL de AzureAzure SQL Database.Applies to: SQL ServerSQL Server (SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017) and Base de datos SQL de AzureAzure SQL Database

Indica si deben almacenarse resultados temporales de orden en tempdb.Specifies whether to store temporary sort results in tempdb. El valor predeterminado es OFF.The default is OFF.

ONON
Los resultados de ordenación intermedios utilizados para generar el índice se almacenan en tempdb.The intermediate sort results that are used to build the index are stored in tempdb. Esto puede reducir el tiempo necesario para crear un índice si tempdb y la base de datos de usuarios están en conjuntos de discos distintos.This may reduce the time required to create an index if tempdb is on a different set of disks than the user database. Sin embargo, esto aumenta la cantidad de espacio en disco utilizado durante la generación del índice.However, this increases the amount of disk space that is used during the index build.

OFFOFF
Los resultados de orden intermedios se almacenan en la misma base de datos que el índice.The intermediate sort results are stored in the same database as the index.

Además del espacio necesario en la base de datos del usuario para crear el índice, tempdb debe tener la misma cantidad de espacio adicional para almacenar los resultados de orden intermedio.In addition to the space required in the user database to create the index, tempdb must have about the same amount of additional space to hold the intermediate sort results. Para más información, vea Opción SORT_IN_TEMPDB para índices.For more information, see SORT_IN_TEMPDB Option For Indexes.

En la sintaxis compatible con versiones anteriores, WITH SORT_IN_TEMPDB es equivalente a WITH SORT_IN_TEMPDB = ON.In backward compatible syntax, WITH SORT_IN_TEMPDB is equivalent to WITH SORT_IN_TEMPDB = ON.

IGNORE_DUP_KEY = { ON | OFF }IGNORE_DUP_KEY = { ON | OFF }
Especifica la respuesta de error cuando una operación de inserción intenta insertar valores de clave duplicados en un índice único.Specifies the error response when an insert operation attempts to insert duplicate key values into a unique index. La opción IGNORE_DUP_KEY se aplica solamente a operaciones de inserción realizadas tras crear o volver a generar el índice.The IGNORE_DUP_KEY option applies only to insert operations after the index is created or rebuilt. La opción no tiene efecto cuando se ejecutan CREATE INDEX, ALTER INDEX o UPDATE.The option has no effect when executing CREATE INDEX, ALTER INDEX, or UPDATE. El valor predeterminado es OFF.The default is OFF.

ONON
Se producirá un mensaje de advertencia cuando se inserten valores de clave duplicados en un índice único.A warning message will occur when duplicate key values are inserted into a unique index. Solo las filas que infrinjan la restricción de unicidad darán error.Only the rows violating the uniqueness constraint will fail.

OFFOFF
Se producirá un mensaje de error cuando se inserten valores de clave duplicados en un índice único.An error message will occur when duplicate key values are inserted into a unique index. Toda la operación INSERT se revertirá.The entire INSERT operation will be rolled back.

IGNORE_DUP_KEY no se puede establecer en ON para los índices creados en una vista, los índices que no sean únicos, los índices XML, los índices espaciales y los índices filtrados.IGNORE_DUP_KEY cannot be set to ON for indexes created on a view, non-unique indexes, XML indexes, spatial indexes, and filtered indexes.

Para ver IGNORE_DUP_KEY, use sys.indexes.To view IGNORE_DUP_KEY, use sys.indexes.

En la sintaxis compatible con versiones anteriores, WITH IGNORE_DUP_KEY es equivalente a WITH IGNORE_DUP_KEY = ON.In backward compatible syntax, WITH IGNORE_DUP_KEY is equivalent to WITH IGNORE_DUP_KEY = ON.

STATISTICS_NORECOMPUTE = { ON | OFF}STATISTICS_NORECOMPUTE = { ON | OFF}
Especifica si se vuelven a calcular las estadísticas de distribución.Specifies whether distribution statistics are recomputed. El valor predeterminado es OFF.The default is OFF.

ONON
Las estadísticas obsoletas no se vuelven a calcular automáticamente.Out-of-date statistics are not automatically recomputed.

OFFOFF
Se habilita la actualización automática de las estadísticas.Automatic statistics updating are enabled.

Para restaurar la actualización automática de estadísticas, establezca STATISTICS_NORECOMPUTE en OFF o ejecute UPDATE STATISTICS sin la cláusula NORECOMPUTE.To restore automatic statistics updating, set the STATISTICS_NORECOMPUTE to OFF, or execute UPDATE STATISTICS without the NORECOMPUTE clause.

Importante

Deshabilitar el cálculo automático de estadísticas de distribución puede impedir que el optimizador de consultas elija los planes de ejecución óptimos de las consultas relativas a la tabla.Disabling automatic recomputation of distribution statistics may prevent the query optimizer from picking optimal execution plans for queries involving the table.

En la sintaxis compatible con versiones anteriores, WITH STATISTICS_NORECOMPUTE es equivalente a WITH STATISTICS_NORECOMPUTE = ON.In backward compatible syntax, WITH STATISTICS_NORECOMPUTE is equivalent to WITH STATISTICS_NORECOMPUTE = ON.

STATISTICS_INCREMENTAL = { ON | OFF }STATISTICS_INCREMENTAL = { ON | OFF }
Cuando se establece en ON, se crean estadísticas por cada partición.When ON, the statistics created are per partition statistics. Cuando se establece en OFF, se quita el árbol de estadísticas y SQL ServerSQL Server recalcula las estadísticas.When OFF, the statistics tree is dropped and SQL ServerSQL Server re-computes the statistics. El valor predeterminado es OFF.The default is OFF.

Si no se admiten las estadísticas por partición, la opción se omite y se genera una advertencia.If per partition statistics are not supported the option is ignored and a warning is generated. Las estadísticas incrementales no se admiten para los siguientes tipos de estadísticas:Incremental stats are not supported for following statistics types:

  • Estadísticas creadas con índices que no están alineados por partición con la tabla base.Statistics created with indexes that are not partition-aligned with the base table.
  • Estadísticas creadas sobre bases de datos secundarias legibles AlwaysOn.Statistics created on Always On readable secondary databases.
  • Estadísticas creadas sobre bases de datos de solo lectura.Statistics created on read-only databases.
  • Estadísticas creadas sobre índices filtrados.Statistics created on filtered indexes.
  • Estadísticas creadas sobre vistas.Statistics created on views.
  • Estadísticas creadas sobre tablas internas.Statistics created on internal tables.
  • Estadísticas creadas con índices espaciales o índices XML.Statistics created with spatial indexes or XML indexes.

DROP_EXISTING = { ON | OFF }DROP_EXISTING = { ON | OFF }
Es una opción para quitar y volver a generar el índice agrupado o no agrupado existente con las especificaciones de la columna modificada y mantener el mismo nombre para el índice.Is an option to drop and rebuild the existing clustered or nonclustered index with modified column specifications, and keep the same name for the index. El valor predeterminado es OFF.The default is OFF.

ONON
Especifica que se debe quitar y volver a generar el índice existente, que debe tener el mismo nombre que el parámetro index_name.Specifies to drop and rebuild the existing index, which must have the same name as the parameter index_name.

OFFOFF
Especifica que no se debe quitar y volver a generar el índice existente.Specifies not to drop and rebuild the existing index. SQL Server muestra un error si ya existe el nombre de índice especificado.SQL Server displays an error if the specified index name already exists.

Con DROP_EXISTING, puede cambiar:With DROP_EXISTING, you can change:

  • Un índice no agrupado de almacén de filas por un índice agrupado de almacén de filas.A nonclustered rowstore index to a clustered rowstore index.

Con DROP_EXISTING, no puede cambiar:With DROP_EXISTING, you cannot change:

  • Un índice agrupado de almacén de filas por un índice no agrupado de almacén de filas.A clustered rowstore index to a nonclustered rowstore index.
  • Un índice de almacén de columnas agrupado por cualquier tipo de índice de almacén de filas.A clustered columnstore index to any type of rowstore index.

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.

ONLINE = { ON | OFF }ONLINE = { ON | OFF }
Especifica si las tablas subyacentes y los índices asociados están disponibles para realizar consultas y modificar datos durante la operación de índice.Specifies whether underlying tables and associated indexes are available for queries and data modification during the index operation. El valor predeterminado es OFF.The default is OFF.

Nota

Las operaciones de índices en línea no están disponibles en todas las ediciones de MicrosoftMicrosoftSQL ServerSQL Server.Online 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.

ONON
Los bloqueos de tabla de larga duración no se mantienen durante la operación de indización.Long-term table locks are not held for the duration of the index operation. Durante la fase principal de la operación de índice, solo se mantiene un bloqueo preventivo en la tabla de origen.During the main phase of the index operation, only an Intent Share (IS) lock is held on the source table. Esto habilita las consultas o actualizaciones en la tabla subyacente y en los índices.This enables queries or updates to the underlying table and indexes to proceed. Al inicio de la operación, se mantiene un bloqueo compartido (S) en el objeto de origen durante un período de tiempo muy corto.At the start of the operation, a Shared (S) lock is held on the source object for a very short period of time. Al final de la operación, se adquiere un bloqueo S (compartido) sobre el origen durante un corto período, si se está creando un índice no clúster; o bien, se adquiere un bloqueo SCH-M (modificación del esquema) cuando se crea o se quita un índice clúster en línea, y cuando se vuelve a crear un índice clúster o no clúster.At the end of the operation, for a short period of time, an S (Shared) lock is acquired on the source if a nonclustered index is being created; or an SCH-M (Schema Modification) lock is acquired when a clustered index is created or dropped online and when a clustered or nonclustered index is being rebuilt. ONLINE no se puede establecer en ON cuando se crea un índice en una tabla temporal local.ONLINE cannot be set to ON when an index is being created on a local temporary table.

OFFOFF
Los bloqueos de tabla se aplican durante la operación de índice.Table locks are applied for the duration of the index operation. Una operación de índice sin conexión para crear, volver a crear o quitar un índice clúster, o para volver a crear o quitar un índice no clúster, adquiere un bloqueo de modificación del esquema (Sch-M) de la tabla.An offline index operation that creates, rebuilds, or drops a clustered index, or rebuilds or drops a nonclustered index, acquires a Schema modification (Sch-M) lock on the table. Esto evita que todos los usuarios tengan acceso a la tabla subyacente durante la operación.This prevents all user access to the underlying table for the duration of the operation. Una operación de índice sin conexión que crea un índice no clúster adquiere un bloqueo compartido (S) en la tabla.An offline index operation that creates a nonclustered index acquires a Shared (S) lock on the table. Esto evita que se realicen actualizaciones en la tabla subyacente, pero permite la realización de operaciones de lectura, como instrucciones SELECT.This prevents updates to the underlying table but allows read operations, such as SELECT statements.

Para más información, vea Cómo funcionan las operaciones de índice en línea.For more information, see How Online Index Operations Work.

RESUMABLE = { ON | OFF}RESUMABLE = { ON | OFF}

Se aplica a: SQL ServerSQL Server (A partir de SQL Server 2019SQL Server 2019) y Base de datos SQL de AzureAzure SQL Database (versión preliminar pública)Applies to: SQL ServerSQL Server (Starting with SQL Server 2019SQL Server 2019) and Base de datos SQL de AzureAzure SQL Database (public preview)

Especifica si una operación de índice en línea se puede reanudar.Specifies whether an online index operation is resumable.

ONON
la operación de índice se puede reanudar.Index operation is resumable.

OFFOFF
la operación de índice no se puede reanudar.Index operation is not resumable.

MAX_DURATION = time [MINUTES] se usa con RESUMABLE = ON (requiere ONLINE = ON)MAX_DURATION = time [MINUTES] used with RESUMABLE = ON (requires ONLINE = ON)

Se aplica a: SQL ServerSQL Server (A partir de SQL Server 2019SQL Server 2019) y Base de datos SQL de AzureAzure SQL Database (versión preliminar pública)Applies to: SQL ServerSQL Server (Starting with SQL Server 2019SQL Server 2019) and Base de datos SQL de AzureAzure SQL Database (public preview)

Indica el tiempo (valor entero especificado en minutos) durante el cual se ejecuta una operación de índice en línea reanudable antes de ponerse en pausa.Indicates time (an integer value specified in minutes) that a resumable online index operation is executed before being paused.

Advertencia

Para saber más sobre las operaciones de índices que pueden realizarse en línea, vea Directrices para operaciones de índices en línea.For more detailed information about index operations that can be performed online, see Guidelines for Online Index Operations.

Los índices, incluidos los índices de las tablas temp globales, se pueden crear en línea, con las excepciones siguientes:Indexes, including indexes on global temp tables, can be created online with the following exceptions:

  • Índice XMLXML index
  • Índice en una tabla temp localIndex on a local temp table.
  • Índice clúster único inicial en una vista.Initial unique clustered index on a view.
  • Índices clúster deshabilitados.Disabled clustered indexes.
  • Índice clúster si la tabla subyacente contiene tipos de datos LOB: image, ntext, text y tipos espaciales.Clustered index if the underlying table contains LOB data types: image, ntext, text, and spatial types.
  • Las columnas varchar(max) y varbinary(max) no pueden formar parte de un índice.varchar(max) and varbinary(max) columns cannot be part of an index. En SQL ServerSQL Server (a partir de SQL Server 2012 (11.x)SQL Server 2012 (11.x)) y en SQL DatabaseSQL Database, cuando una tabla contiene columnas varchar(max) o varbinary(max) de un índice agrupado que contiene otras columnas, pueden crearse o volver a crearse con la opción ONLINE.In SQL ServerSQL Server (beginning with SQL Server 2012 (11.x)SQL Server 2012 (11.x)) and in SQL DatabaseSQL Database, when a table contains varchar(max) or varbinary(max) columns, a clustered index containing other columns, can be built or rebuilt using the ONLINE option. SQL DatabaseSQL Database no permite la opción ONLINE cuando la tabla contiene columnas varchar(max) o varbinary(max) .does not permit the ONLINE option when the base table contains varchar(max) or varbinary(max) columns.

Para más información, consulte Perform Index Operations Online.For more information, see Perform Index Operations Online.

ALLOW_ROW_LOCKS = { ON | OFF }ALLOW_ROW_LOCKS = { ON | OFF }
Se aplica a: SQL ServerSQL Server (desde SQL Server 2008SQL Server 2008 hasta SQL Server 2017SQL Server 2017) y Base de datos SQL de AzureAzure SQL Database.Applies to: SQL ServerSQL Server (SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017) and Base de datos SQL de AzureAzure SQL Database

Especifica si se permiten los bloqueos de fila.Specifies whether row locks are allowed. El valor predeterminado es ON.The default is ON.

ONON
Los bloqueos de fila se admiten al obtener acceso al índice.Row locks are allowed when accessing the index. El Motor de base de datosDatabase Engine determina cuándo se usan los bloqueos de fila.The Motor de base de datosDatabase Engine determines when row locks are used.

OFFOFF
No se usan los bloqueos de fila.Row locks are not used.

ALLOW_PAGE_LOCKS = { ON | OFF }ALLOW_PAGE_LOCKS = { ON | OFF }
Se aplica a: SQL ServerSQL Server (desde SQL Server 2008SQL Server 2008 hasta SQL Server 2017SQL Server 2017) y Base de datos SQL de AzureAzure SQL Database.Applies to: SQL ServerSQL Server (SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017) and Base de datos SQL de AzureAzure SQL Database

Especifica si se permiten bloqueos de página.Specifies whether page locks are allowed. El valor predeterminado es ON.The default is ON.

ONON
Los bloqueos de página se permiten al obtener acceso al índice.Page locks are allowed when accessing the index. Motor de base de datosDatabase Engine determina el momento en que se usan los bloqueos de página.The Motor de base de datosDatabase Engine determines when page locks are used.

OFFOFF
No se utilizan bloqueos de página.Page locks are not used.

OPTIMIZE_FOR_SEQUENTIAL_KEY = { ON | OFF }OPTIMIZE_FOR_SEQUENTIAL_KEY = { ON | OFF }
Se aplica a: SQL ServerSQL Server (a partir de SQL Server 2019SQL Server 2019)Applies to: SQL ServerSQL Server (Starting with SQL Server 2019SQL Server 2019)

Especifica si se deben optimizar la contención de inserción de la última página.Specifies whether or not to optimize for last-page insert contention. El valor predeterminado es OFF.The default is OFF. Consulte la sección Claves secuenciales para obtener más información.See the Sequential Keys section for more information.

MAXDOP = max_degree_of_parallelism MAXDOP = max_degree_of_parallelism
Se aplica a: SQL ServerSQL Server (desde SQL Server 2008SQL Server 2008 hasta SQL Server 2017SQL Server 2017) y Base de datos SQL de AzureAzure SQL Database.Applies to: SQL ServerSQL Server (SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017) and Base de datos SQL de AzureAzure SQL Database

Reemplaza la opción de configuración de max_degree_of_parallelism mientras dure la operación de índice.Overrides the max degree of parallelism configuration option for the duration of the index operation. Para obtener más información, vea Establecer la opción de configuración del servidor Grado máximo de paralelismo.For more information, see Configure the max degree of parallelism Server Configuration Option. 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 puede tener estos valores:max_degree_of_parallelism can be:

11
Suprime la generación de planes paralelos.Suppresses parallel plan generation.

>1>1
Restringe el número máximo de procesadores utilizados en una operación de índice paralelo al número especificado o a un número inferior, en función de la actual carga de trabajo del sistema.Restricts the maximum number of processors used in a parallel index operation to the specified number or fewer based on the current system workload.

0 (predeterminado)0 (default)
Usa el número real de procesadores o menos, según la carga de trabajo actual del sistema.Uses 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 Ediciones y características admitidas de SQL Server 2016 y Ediciones y características admitidas de SQL Server 2017.For a list of features that are supported by the editions of SQL ServerSQL Server, see Editions and Supported Features for SQL Server 2016 and Editions and Supported Features for SQL Server 2017.

DATA_COMPRESSIONDATA_COMPRESSION
Especifica la opción de compresión de datos para el índice, número de partición o intervalo de particiones especificado.Specifies the data compression option for the specified index, partition number, or range of partitions. Las opciones son las siguientes:The options are as follows:

NingunoNONE
No se comprimen el índice ni las particiones especificadas.Index or specified partitions are not compressed.

ROWROW
El índice o las particiones especificadas se comprimen mediante la compresión de fila.Index or specified partitions are compressed by using row compression.

PAGEPAGE
El índice o las particiones especificadas se comprimen mediante la compresión de página.Index or specified partitions are compressed by using page compression.

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

ON PARTITIONS ( { <partition_number_expression> | <range> } [ , ...n ] ) ON PARTITIONS ( { <partition_number_expression> | <range> } [ ,...n ] )
Se aplica a: SQL ServerSQL Server (desde SQL Server 2008SQL Server 2008 hasta SQL Server 2017SQL Server 2017) y Base de datos SQL de AzureAzure SQL Database.Applies to: SQL ServerSQL Server (SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017) and Base de datos SQL de AzureAzure SQL Database

Especifica las particiones a las que se aplica el valor DATA_COMPRESSION.Specifies the partitions to which the DATA_COMPRESSION setting applies. Si el índice no tiene particiones, el argumento ON PARTITIONS generará un error.If the index is not partitioned, the ON PARTITIONS argument will generate an error. Si no se proporciona la cláusula ON PARTITIONS, la opción DATA_COMPRESSION se aplica a todas las particiones de un índice con particiones.If the ON PARTITIONS clause is not provided, the DATA_COMPRESSION option applies to all partitions of a partitioned index.

<partition_number_expression> se puede especificar de las maneras siguientes:<partition_number_expression> can be specified in the following ways:

  • Proporcionar el número de una partición, por ejemplo: EN PARTICIONES (2).Provide the number for a partition, for example: ON PARTITIONS (2).
  • Proporcionar los números de partición para varias particiones individuales separadas por comas, por ejemplo: EN PARTICIONES (1,5).Provide the partition numbers for several individual partitions separated by commas, for example: ON PARTITIONS (1, 5).
  • Proporcione ambos rangos y las particiones individuales, por ejemplo: EN PARTICIONES (2, 4, 6 A 8).Provide both ranges and individual partitions, for example: ON PARTITIONS (2, 4, 6 TO 8).

<range> se puede especificar como números de partición separados por la palabra TO, por ejemplo: ON PARTITIONS (6 TO 8).<range> can be specified as partition numbers separated by the word TO, for example: ON PARTITIONS (6 TO 8).

Para establecer diferentes tipos de compresión de datos para distintas particiones, especifique la opción DATA_COMPRESSION más de una vez, por ejemplo:To set different types of data compression for different partitions, specify the DATA_COMPRESSION option more than once, for example:

REBUILD WITH
(
DATA_COMPRESSION = NONE ON PARTITIONS (1),
DATA_COMPRESSION = ROW ON PARTITIONS (2, 4, 6 TO 8),
DATA_COMPRESSION = PAGE ON PARTITIONS (3, 5)
);

NotasRemarks

La instrucción CREATE INDEX se optimiza como cualquier otra consulta.The CREATE INDEX statement is optimized like any other query. Para guardar en operaciones de E/S, el procesador de consultas puede elegir examinar otro índice en lugar de realizar un recorrido de tabla.To save on I/O operations, the query processor may choose to scan another index instead of performing a table scan. La operación de orden se puede eliminar en algunos casos.The sort operation may be eliminated in some situations. En equipos con varios procesadores, CREATE INDEX puede utilizar más procesadores para realizar las operaciones de examen y orden asociadas a la creación del índice, al igual que hacen otras consultas.On multiprocessor computers CREATE INDEX can use more processors to perform the scan and sort operations associated with creating the index, in the same way as other queries do. Para obtener más información, vea Configurar operaciones de índice en paralelo.For more information, see Configure Parallel Index Operations.

La operación de creación de índices se registra al mínimo si el modelo de recuperación de base de datos se establece en Registro masivo o Sencillo.The create index operation can be minimally logged if the database recovery model is set to either bulk-logged or simple.

Los índices se pueden crear en una tabla temporal.Indexes can be created on a temporary table. Cuando se quita la tabla o finaliza la sesión, se quitan los índices.When the table is dropped or the session ends, the indexes are dropped.

Se puede crear un índice agrupado en una variable de tabla cuando se crea una clave principal.A clustered index can be built on a table variable when a Primary Key is created. Cuando se completa la consulta o se termina la sesión, se quita el índice.When the query completes or the session ends, the index is dropped.

Los índices admiten propiedades extendidas.Indexes support extended properties.

Índices clústerClustered Indexes

La creación de un índice clúster en una tabla (montón) o la eliminación y nueva creación de un índice clúster existente requiere área de espacio adicional disponible en la base de datos para acomodar la ordenación de datos y una copia temporal de la tabla original o datos del índice clúster existente.Creating a clustered index on a table (heap) or dropping and re-creating an existing clustered index requires additional workspace to be available in the database to accommodate data sorting and a temporary copy of the original table or existing clustered index data. Para obtener más información sobre los índices agrupados, vea Creación de índices agrupados y la Guía de diseño y de arquitectura de índices de SQL Server.For more information about clustered indexes, see Create Clustered Indexes and the SQL Server Index Architecture and Design Guide.

Índices no agrupadosNonclustered Indexes

A partir de SQL Server 2016 (13.x)SQL Server 2016 (13.x) y en Base de datos SQL de AzureAzure SQL Database, puede crear un índice no agrupado en una tabla almacenada como un índice de almacén de columnas agrupado.Starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x) and in Base de datos SQL de AzureAzure SQL Database, you can create a nonclustered index on a table stored as a clustered columnstore index. Si primero crea un índice no agrupado en una tabla almacenada como un índice agrupado o montón, el índice se conservará si más adelante convierte la tabla a un índice de almacén de columnas agrupado.If you first create a nonclustered index on a table stored as a heap or clustered index, the index will persist if you later convert the table to a clustered columnstore index. Además, no es necesario quitar el índice no agrupado al volver a generar el índice de almacén de columnas agrupado.It is also not necessary to drop the nonclustered index when you rebuild the clustered columnstore index.

Limitaciones y restricciones:Limitations and Restrictions:

  • La opción FILESTREAM_ON no es válida para crear un índice no agrupado en una tabla almacenada como un índice de almacén de columnas agrupado.The FILESTREAM_ON option is not valid when you create a nonclustered index on a table stored as a clustered columnstore index.

Índices únicosUnique Indexes

Cuando existe un índice único, Motor de base de datosDatabase Engine comprueba si hay valores duplicados cada vez que se agregan datos con una operación de inserción.When a unique index exists, the Motor de base de datosDatabase Engine checks for duplicate values each time data is added by a insert operations. Las operaciones de inserción que generarían valores de clave duplicados se revierten y el Motor de base de datosDatabase Engine muestra un mensaje de error.Insert operations that would generate duplicate key values are rolled back, and the Motor de base de datosDatabase Engine displays an error message. Esto se cumple incluso si la operación de inserción cambia muchas filas pero crea un único duplicado.This is true even if the insert operation changes many rows but causes only one duplicate. Si se intenta indicar datos donde existe un índice único y se ha especificado la cláusula IGNORE_DUP_KEY en ON, solo causarán un error las filas que infrinjan el índice UNIQUE.If an attempt is made to enter data for which there is a unique index and the IGNORE_DUP_KEY clause is set to ON, only the rows violating the UNIQUE index fail.

Índices con particionesPartitioned Indexes

La creación y el mantenimiento de los índices con particiones son similares a los de las tablas con particiones pero, al igual que en índices ordinarios, éstos son tratados como objetos de base de datos independientes.Partitioned indexes are created and maintained in a similar manner to partitioned tables, but like ordinary indexes, they are handled as separate database objects. Puede tener un índice con particiones en una tabla que carezca de particiones, y puede tener un índice sin particiones en una tabla que tenga particiones.You can have a partitioned index on a table that is not partitioned, and you can have a nonpartitioned index on a table that is partitioned.

Si crea un índice en una tabla con particiones y no especifica un grupo de archivos en el que desea ubicar el índice, se crean particiones en el índice de la misma manera que en la tabla subyacente.If you are creating an index on a partitioned table, and do not specify a filegroup on which to place the index, the index is partitioned in the same manner as the underlying table. Esto se debe a que, de manera predeterminada, los índices se ubican en los mismos grupos de archivos que sus tablas subyacentes, y en una tabla con particiones del mismo esquema de partición que usa las mismas columnas de partición.This is because indexes, by default, are placed on the same filegroups as their underlying tables, and for a partitioned table in the same partition scheme that uses the same partitioning columns. Cuando el índice usa el mismo esquema y columna de partición que la tabla, el índice está alineado con la tabla.When the index uses the same partition scheme and partitioning column as the table, the index is aligned with the table.

Advertencia

La creación y regeneración de índices no alineados en una tabla con más de 1.000 particiones es posible, pero no se admite.Creating and rebuilding nonaligned indexes on a table with more than 1,000 partitions is possible, but is not supported. Si se hace, se puede degradar el rendimiento o consumir excesiva memoria durante estas operaciones.Doing so may cause degraded performance or excessive memory consumption during these operations. Se recomienda usar solo índices alineados cuando el número de particiones sea superior a 1.000.We recommend using only aligned indexes when the number of partitions exceed 1,000.

Cuando se crean particiones en un índice clúster no único, el Motor de base de datosDatabase Engine agrega de forma predeterminada las columnas de partición a la lista de claves del índice clúster, en caso de que no se hubieran especificado aún.When partitioning a non-unique, clustered index, the Motor de base de datosDatabase Engine by default adds any partitioning columns to the list of clustered index keys, if not already specified.

Se pueden crear vistas indizadas en tablas con particiones de la misma manera que se hace con índices en tablas.Indexed views can be created on partitioned tables in the same manner as indexes on tables. Para obtener más información acerca de los índices con particiones, vea Índices y tablas con particiones y Guía de diseño y arquitectura de índices de SQL Server.For more information about partitioned indexes, see Partitioned Tables and Indexes and the SQL Server Index Architecture and Design Guide.

En SQL Server 2017SQL Server 2017, las estadísticas no se crean examinando todas las filas de la tabla cuando se crea o se vuelve a generar un índice con particiones.In SQL Server 2017SQL Server 2017, statistics are not created by scanning all the rows in the table when a partitioned index is created or rebuilt. En su lugar, el optimizador de consultas usa el algoritmo de muestreo predeterminado para generar estadísticas.Instead, the query optimizer uses the default sampling algorithm to generate statistics. Para obtener estadísticas sobre índices con particiones examinando todas las filas de la tabla, use CREATE STATISTICS o UPDATE STATISTICS con la cláusula FULLSCAN.To obtain statistics on partitioned indexes by scanning all the rows in the table, use CREATE STATISTICS or UPDATE STATISTICS with the FULLSCAN clause.

Í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 ONON ONON ONON OFFOFF
    ANSI_PADDINGANSI_PADDING ONON ONON ONON OFFOFF
    ANSI_WARNINGS*ANSI_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 con filtros, vea Creación de índices con filtros y Guía de diseño y arquitectura de índices de SQL Server.For more information about Filtered Indexes, see Create Filtered Indexes and the SQL Server Index Architecture and Design Guide.

Índices espacialesSpatial Indexes

Para obtener información sobre los índices espaciales, vea CREATE SPATIAL INDEX e Información general sobre los índices espaciales.For information about spatial indexes, see CREATE SPATIAL INDEX and Spatial Indexes Overview.

Índices XMLXML Indexes

Para obtener información sobre los índices XML, vea CREATE XML INDEX e Índices XML (SQL Server).For information about XML indexes see, CREATE XML INDEX and XML Indexes (SQL Server).

Tamaño de clave de índiceIndex Key Size

El tamaño máximo de una clave de índice es de 900 bytes para un índice agrupado y de 1700 bytes para un índice no agrupado.The maximum size for an index key is 900 bytes for a clustered index and 1,700 bytes for a nonclustered index. (Antes de SQL DatabaseSQL Database y SQL Server 2016 (13.x)SQL Server 2016 (13.x), el límite era siempre de 900 bytes). Se pueden crear índices en las columnas varchar cuyo tamaño sea superior al límite de bytes si los datos que contienen no superan ese tamaño al crearse el índice. Sin embargo, se producirá un error en las acciones de inserción o actualización posteriores en las columnas que hagan que el tamaño total sea mayor que el límite.(Before SQL DatabaseSQL Database and SQL Server 2016 (13.x)SQL Server 2016 (13.x) the limit was always 900 bytes.) Indexes on varchar columns that exceed the byte limit can be created if the existing data in the columns do not exceed the limit at the time the index is created; however, subsequent insert or update actions on the columns that cause the total size to be greater than the limit will fail. La clave de índice de un índice agrupado no puede contener columnas varchar con datos existentes en la unidad de asignación ROW_OVERFLOW_DATA.The index key of a clustered index cannot contain varchar columns that have existing data in the ROW_OVERFLOW_DATA allocation unit. Si se crea un índice agrupado en una columna varchar y los datos existentes están en la unidad de asignación IN_ROW_DATA, no se realizarán correctamente las siguientes acciones de inserción o actualización en la columna que intenten insertar los datos de manera no consecutiva.If a clustered index is created on a varchar column and the existing data is in the IN_ROW_DATA allocation unit, subsequent insert or update actions on the column that would push the data off-row will fail.

Los índices no clúster pueden incluir columnas que no son de clave en el nivel de hoja del índice.Nonclustered indexes can include non-key columns in the leaf level of the index. Motor de base de datosDatabase Engine no tiene en cuenta estas columnas al calcular el tamaño de clave de índice.These columns are not considered by the Motor de base de datosDatabase Engine when calculating the index key size . Para obtener más información vea Creación de índices con columnas incluidas y Guía de diseño y arquitectura de índices de SQL Server.For more information, see Create Indexes with Included Columns and the SQL Server Index Architecture and Design Guide.

Nota

Cuando se dividen las tablas, si las columnas de clave de la partición no están aún presentes en un índice clúster no único, el Motor de base de datosDatabase Engine las agrega al índice.When tables are partitioned, if the partitioning key columns are not already present in a non-unique clustered index, they are added to the index by the Motor de base de datosDatabase Engine. El tamaño combinado de las columnas indizadas (sin contar las columnas incluidas) más cualquier columna de partición agregada no puede exceder 1800 bytes en un índice clúster no único.The combined size of the indexed columns (not counting included columns), plus any added partitioning columns cannot exceed 1800 bytes in a non-unique clustered index.

Columnas calculadasComputed Columns

Los índices se pueden crear en columnas calculadas.Indexes can be created on computed columns. Además, las columnas calculadas pueden tener la propiedad PERSISTED.In addition, computed columns can have the property PERSISTED. Esto significa que Motor de base de datosDatabase Engine almacena los valores calculados en la tabla y los actualiza cuando se actualiza cualquier otra columna de la que depende la columna calculada.This means that the Motor de base de datosDatabase Engine stores the computed values in the table, and updates them when any other columns on which the computed column depends are updated. Motor de base de datosDatabase Engine utiliza estos valores persistentes cuando crea un índice en la columna y cuando se hace referencia al índice en una consulta.The Motor de base de datosDatabase Engine uses these persisted values when it creates an index on the column, and when the index is referenced in a query.

Para indizar una columna calculada, ésta debe ser determinista y precisa.To index a computed column, the computed column must deterministic and precise. No obstante, si se usa la propiedad PERSISTED, se amplía el tipo de columnas calculadas indizables para incluir:However, using the PERSISTED property expands the type of indexable computed columns to include:

  • Las columnas calculadas basadas en Transact-SQLTransact-SQL, funciones CLR y métodos de tipos definidos por el usuario CLR que el usuario ha marcado como deterministas.Computed columns based on Transact-SQLTransact-SQL and CLR functions and CLR user-defined type methods that are marked deterministic by the user.
  • Las columnas calculadas basadas en expresiones que son deterministas, como se definen en Motor de base de datosDatabase Engine, aunque imprecisas.Computed columns based on expressions that are deterministic as defined by the Motor de base de datosDatabase Engine but imprecise.

Las columnas calculadas persistentes requieren que se establezcan las siguientes opciones SET de la manera indicada en la sección anterior, Opciones SET requeridas para los índices filtrados.Persisted computed columns require the following SET options to be set as shown in the previous section Required SET Options for Filtered Indexes.

Las restricciones UNIQUE o PRIMARY KEY pueden contener una columna calculada siempre que cumplan con todas las condiciones de creación del índice.The UNIQUE or PRIMARY KEY constraint can contain a computed column as long as it satisfies all conditions for indexing. En concreto, la columna calculada debe ser determinista y precisa, o determinista y persistente.Specifically, the computed column must be deterministic and precise or deterministic and persisted. Para obtener más información sobre el determinismo, vea Funciones deterministas y no deterministas.For more information about determinism, see Deterministic and Nondeterministic Functions.

Las columnas calculadas derivadas de los tipos de datos image, ntext, text, varchar(max) , nvarchar(max) , varbinary(max) y xml se pueden indexar como una columna de clave o columna sin clave incluida, siempre que el tipo de datos de la columna calculada esté disponible como una columna índice de clave o sin clave.Computed columns derived from image, ntext, text, varchar(max), nvarchar(max), varbinary(max), and xml data types can be indexed either as a key or included non-key column as long as the computed column data type is allowable as an index key column or non-key column. Por ejemplo, no puede crear un índice XML principal en una columna xml calculada.For example, you cannot create a primary XML index on a computed xml column. Si el tamaño de clave de índice supera los 900 bytes, se muestra un mensaje de advertencia.If the index key size exceeds 900 bytes, a warning message is displayed.

La creación de un índice en una columna calculada puede producir un error en una operación de inserción o actualización que antes funcionaba.Creating an index on a computed column may cause the failure of an insert or update operation that previously worked. Este error podría ocurrir cuando la columna calculada produce un error aritmético.Such a failure may take place when the computed column results in arithmetic error. Por ejemplo, aunque la columna calculada c de la tabla siguiente produzca un error aritmético, la instrucción INSERT funcionará.For example, in the following table, although computed column c results in an arithmetic error, the INSERT statement works.

CREATE TABLE t1 (a int, b int, c AS a/b);
INSERT INTO t1 VALUES (1, 0);

En cambio, si después de crear la tabla crea un índice en la columna calculada c, la misma instrucción INSERT producirá un error.If, instead, after creating the table, you create an index on computed column c, the same INSERT statement will now fail.

CREATE TABLE t1 (a int, b int, c AS a/b);
CREATE UNIQUE CLUSTERED INDEX Idx1 ON t1(c);
INSERT INTO t1 VALUES (1, 0);

Para obtener más información, vea Indexes on Computed Columns.For more information, see Indexes on Computed Columns.

Columnas incluidas en índicesIncluded Columns in Indexes

Las columnas que no son de clave, denominadas columnas incluidas, se pueden agregar en el nivel hoja de un índice no clúster para mejorar el rendimiento de las consultas al cubrir la consulta.Non-key columns, called included columns, can be added to the leaf level of a nonclustered index to improve query performance by covering the query. Es decir, todas las columnas a las que se hace referencia en la consulta se incluyen en el índice como columnas de clave o que no son de clave.That is, all columns referenced in the query are included in the index as either key or non-key columns. De este modo, el optimizador de consultas puede ubicar toda la información requerida con un examen del índice; no se tiene acceso a los datos de la tabla o del índice clúster.This allows the query optimizer to locate all the required information from an index scan; the table or clustered index data is not accessed. Para obtener más información vea Creación de índices con columnas incluidas y Guía de diseño y arquitectura de índices de SQL Server.For more information, see Create Indexes with Included Columns and the SQL Server Index Architecture and Design Guide.

Especificar opciones de índiceSpecifying Index Options

SQL Server 2005 (9.x)SQL Server 2005 (9.x) incluye opciones de índice nuevas y también modifica el modo en que se especifican las opciones.introduced new index options and also modifies the way in which options are specified. En la sintaxis compatible con versiones anteriores, WITH option_name es equivalente a WITH ( <option_name> = ON ) .In backward compatible syntax, WITH option_name is equivalent to WITH ( <option_name> = ON ). Al establecer opciones de índice, se aplican las siguientes reglas:When you set index options, the following rules apply:

  • Las nuevas opciones de índice solo se pueden especificar mediante WITH ( option_name = ON | OFF).New index options can only be specified by using WITH (option_name = ON | OFF).
  • Las opciones no se pueden especificar utilizando la sintaxis compatible con versiones anteriores y la nueva sintaxis en la misma instrucción.Options cannot be specified by using both the backward compatible and new syntax in the same statement. Por ejemplo, al especificar WITH (DROP_EXISTING, ONLINE = ON), se genera un error en la instrucción.For example, specifying WITH (DROP_EXISTING, ONLINE = ON) causes the statement to fail.
  • Cuando se crea un índice XML, las opciones se deben especificar mediante WITH ( option_name= ON | OFF).When you create an XML index, the options must be specified by using WITH (option_name= ON | OFF).

Cláusula DROP_EXISTINGDROP_EXISTING Clause

Puede utilizar la cláusula DROP_EXISTING para volver a generar el índice, agregar o quitar columnas, modificar opciones, modificar el criterio de ordenación de las columnas o cambiar el grupo de archivos o el esquema de partición.You can use the DROP_EXISTING clause to rebuild the index, add or drop columns, modify options, modify column sort order, or change the partition scheme or filegroup.

Si el índice exige una restricción PRIMARY KEY o UNIQUE, y la definición de índice no se ha modificado en absoluto, se quita el índice y se vuelve a crear conservando la restricción existente.If the index enforces a PRIMARY KEY or UNIQUE constraint and the index definition is not altered in any way, the index is dropped and re-created preserving the existing constraint. Sin embargo, si se ha modificado la definición de índice, se genera un error en la instrucción.However, if the index definition is altered the statement fails. Para cambiar la definición de una restricción PRIMARY KEY o UNIQUE, quite la restricción y agregue una restricción con la nueva definición.To change the definition of a PRIMARY KEY or UNIQUE constraint, drop the constraint and add a constraint with the new definition.

DROP_EXISTING mejora el rendimiento cuando se vuelve a crear un índice clúster (con el mismo conjunto de claves o con uno distinto) en una tabla que también tiene índices no clúster.DROP_EXISTING enhances performance when you re-create a clustered index, with either the same or different set of keys, on a table that also has nonclustered indexes. DROP_EXISTING reemplaza la ejecución de una instrucción DROP INDEX en el antiguo índice clúster seguida de la ejecución de una instrucción CREATE INDEX para el nuevo índice clúster.DROP_EXISTING replaces the execution of a DROP INDEX statement on the old clustered index followed by the execution of a CREATE INDEX statement for the new clustered index. Los índices no clúster se vuelven a generar una vez, siempre que la definición de índice haya cambiado.The nonclustered indexes are rebuilt once, and then only if the index definition has changed. La cláusula DROP_EXISTING no vuelve a generar los índices no clúster cuando la definición de índice posee los mismos nombres de índice, clave y columnas de partición, atributo de unicidad y criterio de ordenación que el índice original.The DROP_EXISTING clause does not rebuild the nonclustered indexes when the index definition has the same index name, key and partition columns, uniqueness attribute, and sort order as the original index.

Independientemente de si se vuelven a generar o no los índices no clúster, éstos siempre permanecen en sus esquemas de partición o grupos de archivos originales, y utilizan las funciones de partición originales.Whether the nonclustered indexes are rebuilt or not, they always remain in their original filegroups or partition schemes and use the original partition functions. Si un índice clúster se vuelve a generar en un esquema de partición o grupo de archivos diferente, los índices no clúster no se mueven para coincidir con la nueva ubicación del índice clúster.If a clustered index is rebuilt to a different filegroup or partition scheme, the nonclustered indexes are not moved to coincide with the new location of the clustered index. Por lo tanto, es posible que incluso los índices no clúster alineados previamente con el índice clúster no se puedan alinear con éste.Therefore, even the nonclustered indexes previously aligned with the clustered index, they may no longer be aligned with it. Para más información sobre la alineación de índices con particiones, vea Tablas e índices con particiones.For more information about partitioned index alignment, see Partitioned Tables and Indexes.

La cláusula DROP_EXISTING no volverá a ordenar los datos si se utilizan las mismas columnas de clave de índice en el mismo orden y con la misma disposición ascendente o descendente, a menos que la instrucción del índice especifique un índice no clúster y la opción ONLINE se establezca en OFF.The DROP_EXISTING clause will not sort the data again if the same index key columns are used in the same order and with the same ascending or descending order, unless the index statement specifies a nonclustered index and the ONLINE option is set to OFF. Si se deshabilita el índice clúster, se debe establecer ONLINE en OFF para la operación CREATE INDEX WITH DROP_EXISTING.If the clustered index is disabled, the CREATE INDEX WITH DROP_EXISTING operation must be performed with ONLINE set to OFF. Si se deshabilita un índice no clúster y no se asocia con un índice clúster deshabilitado, se puede establecer ONLINE en OFF u ON para la operación CREATE INDEX WITH DROP_EXISTING.If a nonclustered index is disabled and is not associated with a disabled clustered index, the CREATE INDEX WITH DROP_EXISTING operation can be performed with ONLINE set to OFF or ON.

Nota

Cuando se quitan o se vuelven a generar índices con 128 o más extensiones, el Motor de base de datosDatabase Engine aplaza las cancelaciones de asignación de página reales y los bloqueos asociados, hasta después de que se confirme la transacción.When indexes with 128 extents or more are dropped or rebuilt, the Motor de base de datosDatabase Engine defers the actual page deallocations, and their associated locks, until after the transaction commits.

Opción ONLINEONLINE Option

Las directrices siguientes se aplican para el desarrollo de operaciones de índice en línea:The following guidelines apply for performing index operations online:

  • La tabla subyacente no se podrá alterar, truncar ni quitar mientras haya una operación de índice en línea en curso.The underlying table cannot be altered, truncated, or dropped while an online index operation is in process.
  • La operación de índice requiere un espacio en disco temporal adicional.Additional temporary disk space is required during the index operation.
  • Las operaciones en línea se pueden realizar en índices con particiones e índices que contienen columnas calculadas persistentes, o columnas incluidas.Online operations can be performed on partitioned indexes and indexes that contain persisted computed columns, or included columns.

Para más información, consulte Perform Index Operations Online.For more information, see Perform Index Operations Online.

Operaciones de índice reanudableResumable index operations

Se aplica a: SQL ServerSQL Server (A partir de SQL Server 2019SQL Server 2019) y Base de datos SQL de AzureAzure SQL Database (versión preliminar pública)Applies to: SQL ServerSQL Server (Starting with SQL Server 2019SQL Server 2019) and Base de datos SQL de AzureAzure SQL Database (public preview)

Se aplican las directrices siguientes para las operaciones de índice reanudable:The following guidelines apply for resumable index operations:

  • ONLINE INDEX CREATE se especifica como RESUMABLE con la opción RESUMABLE = ON.Online index create is specified as resumable using the RESUMABLE = ON option.
  • La opción RESUMABLE no se conserva en los metadatos de un índice dado y solo se aplica a la duración de una instrucción DDL actual.The RESUMABLE option is not persisted in the metadata for a given index and applies only to the duration of a current DDL statement. Por tanto, la cláusula RESUMABLE = ON debe especificarse explícitamente para habilitar la capacidad de reanudación.Therefore, the RESUMABLE = ON clause must be specified explicitly to enable resumability.
  • La opción MAX_DURATION solo se admite para la opción RESUMABLE = ON.MAX_DURATION option is only supported for RESUMABLE = ON option.
  • MAX_DURATION para la opción RESUMABLE especifica el intervalo de tiempo para compilar un índice.MAX_DURATION for RESUMABLE option specifies the time interval for an index being built. Una vez pasado este tiempo, la operación de compilación de índice se pausa o completa su ejecución.Once this time is used the index build is either paused or it completes its execution. El usuario decide cuándo se puede reanudar una compilación de un índice en pausa.User decides when a build for a paused index can be resumed. El valor time en minutos para MAX_DURATION debe ser mayor que 0 minutos o menor o igual que una semana (7 * 24 * 60 = 10080 minutos).The time in minutes for MAX_DURATION must be greater than 0 minutes and less or equal one week (7 * 24 * 60 = 10080 minutes). Si se hace una pausa larga en una operación de índice puede afectar al rendimiento de DML en una tabla específica, así como a la capacidad de disco de base de datos, dado que ambos índices, el original y el que se acaba de crear, necesitan espacio en disco y deben actualizarse durante las operaciones de DML.Having a long pause for an index operation may impact the DML performance on a specific table as well as the database disk capacity since both indexes the original one and the newly created one require disk space and need to be updated during DML operations. Si se omite la opción MAX_DURATION, la operación de índice continuará hasta su finalización o hasta que se produzca un error.If MAX_DURATION option is omitted, the index operation will continue until its completion or until a failure occurs.
  • Para pausar inmediatamente la operación de índice, puede detener (Ctrl+C) el comando en curso, ejecutar el comando ALTER INDEX PAUSE o ejecutar el comando KILL <session_id>.To pause immediately the index operation, you can stop (Ctrl-C) the ongoing command, execute the ALTER INDEX PAUSE command, or execute the KILL <session_id> command. Una vez que el comando está en pausa, se puede reanudar mediante el comando ALTER INDEX.Once the command is paused, it can be resumed using ALTER INDEX command.
  • Al volver a ejecutar la instrucción CREATE INDEX para el índice reanudable, se reanuda automáticamente una operación de creación de índice en pausa.Re-executing the original CREATE INDEX statement for resumable index, automatically resumes a paused index create operation.
  • La opción SORT_IN_TEMPDB = ON no es compatible con el índice reanudable.The SORT_IN_TEMPDB = ON option is not supported for resumable index.
  • El comando DDL con RESUMABLE = ON no se puede ejecutar dentro de una transacción explícita (no puede formar parte del bloque begin TRAN ... COMMIT).The DDL command with RESUMABLE = ON cannot be executed inside an explicit transaction (cannot be part of begin TRAN ... COMMIT block).
  • Para reanudar o anular una creación o recompilación de índice, use la sintaxis de T-SQL ALTER INDEXTo resume/abort an index create/rebuild, use the ALTER INDEX T-SQL syntax

Nota

El comando DDL se ejecuta hasta que se completa, se pone en pausa o genera un error.The DDL command runs until it completes, pauses or fails. En caso de que el comando se ponga en pausa, se emite un error que indica que se pausó la operación y que no se completó la creación de índice.In case the command pauses, an error will be issued indicating that the operation was paused and that the index creation did not complete. Para más información sobre el estado actual del índice, vea sys.index_resumable_operations.More information about the current index status can be obtained from sys.index_resumable_operations. Como ocurrió antes, en caso de fallo se generará también un error.As before in case of a failure an error will be issued as well.

Para indicar que la creación de un índice se ejecuta como una operación reanudable y para comprobar su estado de ejecución actual, consulte sys.index_resumable_operations.To indicate that an index create is executed as resumable operation and to check its current execution state, see sys.index_resumable_operations.

RecursosResources

Los recursos siguientes son necesarios para la operación de creación de índice en línea:The following resources are required for resumable online index create operation:

  • el espacio adicional necesario para mantener el índice que se está generando, incluida la hora en que se pausó el índice;Additional space required to keep the index being built, including the time when index is being paused
  • Rendimiento de registro adicional durante la fase de ordenación.Additional log throughput during the sorting phase. El uso de espacio del registro general para el índice reanudable es menor en comparación con la creación del índice en línea habitual y permite truncar el registro durante esta operación.The overall log space usage for resumable index is less compared to regular online index create and allows log truncation during this operation.
  • un estado DDL que impida cualquier modificación de DDL.A DDL state preventing any DDL modification
  • La limpieza de registros fantasma se bloquea en el índice de la compilación durante la operación, tanto en pausa como cuando la operación está en ejecución.Ghost cleanup is blocked on the in-build index for the duration of the operation both while paused and while the operation is running.

Limitaciones funcionales actualesCurrent functional limitations

Abajo se detallan las funciones que se deshabilitan para las operaciones de creación de índices reanudables:The following functionality is disabled for resumable index create operations:

  • Después de poner en pausa una operación de creación de índice reanudable en línea, no es posible modificar el valor inicial de MAXDOPAfter a resumable online index create operation is paused, the initial value of MAXDOP cannot be changed

  • Crear un índice que contiene:Create an index that contains:

    • Columna calculada o columna TIMESTAMP como columnas de claveComputed or TIMESTAMP column(s) as key columns
    • Columna LOB como columna incluida para la creación de índices reanudablesLOB column as included column for resumable index create
    • Índice filtradoFiltered index

Opciones de bloqueo de fila y páginaRow and Page Locks Options

Si ALLOW_ROW_LOCKS = ON y ALLOW_PAGE_LOCK = ON, se permiten los bloqueos de nivel de fila, página y tabla al obtener acceso al índice.When ALLOW_ROW_LOCKS = ON and ALLOW_PAGE_LOCK = ON, row-, page-, and table-level locks are allowed when accessing the index. Motor de base de datosDatabase Engine elige el bloqueo apropiado y puede cambiar de escala el bloqueo: de un bloqueo de fila o página a un bloqueo de tabla.The Motor de base de datosDatabase Engine chooses the appropriate lock and can escalate the lock from a row or page lock to a table lock.

Si ALLOW_ROW_LOCKS = OFF y ALLOW_PAGE_LOCK = OFF, solo se permite un bloqueo de nivel de tabla al obtener acceso al índice.When ALLOW_ROW_LOCKS = OFF and ALLOW_PAGE_LOCK = OFF, only a table-level lock is allowed when accessing the index.

Claves secuencialesSequential Keys

Se aplica a: SQL ServerSQL Server (a partir de SQL Server 2019SQL Server 2019)Applies to: SQL ServerSQL Server (Starting with SQL Server 2019SQL Server 2019)

La contención de inserción de la última página es un problema común de rendimiento que se produce cuando un gran número de subprocesos simultáneos intentan insertar filas en un índice con una clave secuencial.Last-page insert contention is a common performance problem that occurs when a large number of concurrent threads attempt to insert rows into an index with a sequential key. Un índice se considera secuencial cuando la columna de clave inicial contiene valores que siempre aumentan (o disminuyen), como una columna de identidad o una fecha que toma como valor predeterminado la fecha y hora actuales.An index is considered sequential when the leading key column contains values that are always increasing (or decreasing), such as an identity column or a date that defaults to the current date/time. Dado que las claves que se insertan son secuenciales, todas las nuevas filas se insertarán al final de la estructura del índice, es decir, en la misma página.Because the keys being inserted are sequential, all new rows will be inserted at the end of the index structure - in other words, on the same page. Esta situación conduce a la contención de la página en memoria que se puede observar cuando varios subprocesos están a la espera de PAGELATCH_EX para la página en cuestión.This leads to contention for the page in memory which can be observed as several threads waiting on PAGELATCH_EX for the page in question.

Al activar la opción de índice OPTIMIZE_FOR_SEQUENTIAL_KEY es posible una optimización en el motor de base de datos que ayuda a mejora el rendimiento de las inserciones de alta simultaneidad en el índice.Turning on the OPTIMIZE_FOR_SEQUENTIAL_KEY index option enables an optimization within the database engine that helps improve throughput for high-concurrency inserts into the index. Está concebida para los índices que tienen una clave secuencial y, por tanto, son propensos a la contención de inserción de la última página, pero también puede ayudar con índices que tienen zonas activas en otras áreas de la estructura del índice de árbol B.It is intended for indexes that have a sequential key and thus are prone to last-page insert contention, but it may also help with indexes that have hot spots in other areas of the B-Tree index structure.

Ver información de índiceViewing Index Information

Para devolver información sobre índices, puede utilizar vistas de catálogo, funciones del sistema y procedimientos almacenados del sistema.To return information about indexes, you can use catalog views, system functions, and system stored procedures.

Data CompressionData Compression

La compresión de datos se describe en el tema Compresión de datos.Data compression is described in the topic Data Compression. A continuación se muestran los puntos clave que se deben tener en cuenta:The following are key points to consider:

  • La compresión puede permitir que se almacenen más filas en una página, pero no cambia el tamaño máximo de la fila.Compression can allow more rows to be stored on a page, but does not change the maximum row size.
  • Las páginas no hoja de un índice no tienen compresión de página pero pueden tener compresión de fila.Non-leaf pages of an index are not page compressed but can be row compressed.
  • Cada índice no clúster tiene una configuración de compresión individual y no hereda la configuración de compresión de la tabla subyacente.Each nonclustered index has an individual compression setting, and does not inherit the compression setting of the underlying table.
  • Cuando se crea un índice clúster en un montón, el índice clúster hereda el estado de compresión del montón, a menos que se especifique otro estado de compresión.When a clustered index is created on a heap, the clustered index inherits the compression state of the heap unless an alternative compression state is specified.

Las restricciones siguientes se aplican a los índices con particiones:The following restrictions apply to partitioned indexes:

  • No se puede cambiar la configuración de compresión de una partición única si la tabla tiene índices no alineados.You cannot change the compression setting of a single partition if the table has nonaligned indexes.
  • La sintaxis ALTER INDEX <index> ... REBUILD PARTITION ... vuelve a generar la partición especificada del índice.The ALTER INDEX <index> ... REBUILD PARTITION ... syntax rebuilds the specified partition of the index.
  • La sintaxis ALTER INDEX <index> ... REBUILD WITH ... vuelve a generar todas las particiones del índice.The ALTER INDEX <index> ... REBUILD WITH ... syntax rebuilds all partitions of the index.

Para evaluar cómo afecta el cambio del estado de compresión a una tabla, índice o partición, use el procedimiento almacenado sp_estimate_data_compression_savings .To evaluate how changing the compression state will affect a table, an index, or a partition, use the sp_estimate_data_compression_savings stored procedure.

PermisosPermissions

Debe tener un permiso de ALTER sobre la tabla o vista.Requires ALTER permission on the table or view. El usuario debe ser miembro del role fijo de servidor sysadmin o los roles fijos de base de datos db_ddladmin y db_owner.User must be a member of the sysadmin fixed server role or the db_ddladmin and db_owner fixed database roles.

Limitaciones y restriccionesLimitations and Restrictions

En Almacenamiento de datos SQLSQL Data Warehouse y Almacenamiento de datos paralelosParallel Data Warehouse, no puede crear lo siguiente:In Almacenamiento de datos SQLSQL Data Warehouse and Almacenamiento de datos paralelosParallel Data Warehouse, you cannot create:

  • Un índice de almacén de filas agrupado o no agrupado en una tabla de almacén de datos cuando ya existe un índice de almacén de columnas.A clustered or nonclustered rowstore index on a data warehouse table when a columnstore index already exists. Este comportamiento es diferente de SMP SQL ServerSQL Server, que permite que los índices de almacén de filas y columnas coexistan en la misma tabla.This behavior is different from SMP SQL ServerSQL Server which allows both rowstore and columnstore indexes to co-exist on the same table.
  • No puede crear un índice en una vista.You cannot create an index on a view.

MetadatosMetadata

Para ver información sobre los índices existentes, puede consultar la vista de catálogo sys.indexes.To view information on existing indexes, you can query the sys.indexes catalog view.

Notas de la versiónVersion Notes

SQL DatabaseSQL Database no admite opciones de grupo de archivos ni secuencia de archivos.does not support filegroup and filestream options.

Ejemplos: Todas las versiones.Examples: All versions. Utiliza la base de datos AdventureWorksUses the AdventureWorks database

A.A. Crear un índice no clúster de almacén de filas simpleCreate a simple nonclustered rowstore index

El ejemplo siguiente crea un índice no agrupado en la columna VendorID de la tabla Purchasing.ProductVendor.The following examples create a nonclustered index on the VendorID column of the Purchasing.ProductVendor table.

CREATE INDEX IX_VendorID ON ProductVendor (VendorID);
CREATE INDEX IX_VendorID ON dbo.ProductVendor (VendorID DESC, Name ASC, Address DESC);
CREATE INDEX IX_VendorID ON Purchasing..ProductVendor (VendorID);

B.B. Crear un índice compuesto de almacén de filas no agrupadoCreate a simple nonclustered rowstore composite index

En el ejemplo siguiente se crea un índice compuesto no agrupado en las columnas SalesQuota y SalesYTD de la tabla Sales.SalesPerson.The following example creates a nonclustered composite index on the SalesQuota and SalesYTD columns of the Sales.SalesPerson table.

CREATE NONCLUSTERED INDEX IX_SalesPerson_SalesQuota_SalesYTD ON Sales.SalesPerson (SalesQuota, SalesYTD);

C.C. Crear un índice en una tabla de otra base de datosCreate an index on a table in another database

En el ejemplo siguiente se crea un índice agrupado en la columna VendorID de la tabla ProductVendor en la base de datos Purchasing.The following example creates a clustered index on the VendorID column of the ProductVendor table in the Purchasing database.

CREATE CLUSTERED INDEX IX_ProductVendor_VendorID ON Purchasing..ProductVendor (VendorID);

D.D. Agregar una columna a un índiceAdd a column to an index

En el ejemplo siguiente se crea el índice IX_FF con dos columnas de la tabla dbo.FactFinance.The following example creates index IX_FF with two columns from the dbo.FactFinance table. La instrucción siguiente vuelve a generar el índice con una columna más y mantiene el nombre existente.The next statement rebuilds the index with one more column and keeps the existing name.

CREATE INDEX IX_FF ON dbo.FactFinance ( FinanceKey ASC, DateKey ASC );

--Rebuild and add the OrganizationKey
CREATE INDEX IX_FF ON dbo.FactFinance ( FinanceKey, DateKey, OrganizationKey DESC)
WITH ( DROP_EXISTING = ON );

Ejemplos: SQL Server, Azure SQL DatabaseExamples: SQL Server, Azure SQL Database

E.E. Crear un índice no agrupado únicoCreate a unique nonclustered index

En el ejemplo siguiente se crea un índice no clúster único en la columna Name de la tabla Production.UnitMeasure en la base de datos AdventureWorks2012AdventureWorks2012.The following example creates a unique nonclustered index on the Name column of the Production.UnitMeasure table in the AdventureWorks2012AdventureWorks2012 database. El índice exigirá unicidad en los datos insertados en la columna Name.The index will enforce uniqueness on the data inserted into the Name column.

CREATE UNIQUE INDEX AK_UnitMeasure_Name
  ON Production.UnitMeasure(Name);

La consulta siguiente prueba la restricción de unicidad intentando insertar una fila con el mismo valor que el de una fila existente.The following query tests the uniqueness constraint by attempting to insert a row with the same value as that in an existing row.

--Verify the existing value.
SELECT Name FROM Production.UnitMeasure WHERE Name = N'Ounces';
GO
INSERT INTO Production.UnitMeasure (UnitMeasureCode, Name, ModifiedDate)
  VALUES ('OC', 'Ounces', GetDate());

El mensaje de error resultante es:The resulting error message is:

Server: Msg 2601, Level 14, State 1, Line 1
Cannot insert duplicate key row in object 'UnitMeasure' with unique index 'AK_UnitMeasure_Name'. The statement has been terminated.

F.F. Usar la opción IGNORE_DUP_KEYUse the IGNORE_DUP_KEY option

El ejemplo siguiente muestra el efecto de la opción IGNORE_DUP_KEY al insertar varias filas en una tabla temporal primero con la opción establecida en ON y luego con la opción establecida en OFF.The following example demonstrates the effect of the IGNORE_DUP_KEY option by inserting multiple rows into a temporary table first with the option set to ON and again with the option set to OFF. Se inserta una única fila en la tabla #Test que intencionadamente proporcionará un valor duplicado cuando se ejecuta la segunda instrucción INSERT de varias filas.A single row is inserted into the #Test table that will intentionally cause a duplicate value when the second multiple-row INSERT statement is executed. Un recuento de las filas de la tabla devuelve el número de filas insertadas.A count of rows in the table returns the number of rows inserted.

CREATE TABLE #Test (C1 nvarchar(10), C2 nvarchar(50), C3 datetime);
GO
CREATE UNIQUE INDEX AK_Index ON #Test (C2)
  WITH (IGNORE_DUP_KEY = ON);
GO
INSERT INTO #Test VALUES (N'OC', N'Ounces', GETDATE());
INSERT INTO #Test SELECT * FROM Production.UnitMeasure;
GO
SELECT COUNT(*)AS [Number of rows] FROM #Test;
GO
DROP TABLE #Test;
GO

A continuación se muestran los resultados de la segunda instrucción INSERT.Here are the results of the second INSERT statement.

Server: Msg 3604, Level 16, State 1, Line 5 Duplicate key was ignored.

Number of rows
--------------
38

Observe que las filas insertadas desde la tabla Production.UnitMeasure que no infringieron la restricción de unicidad se insertaron correctamente.Notice that the rows inserted from the Production.UnitMeasure table that did not violate the uniqueness constraint were successfully inserted. Se emitió una advertencia y se omitió la fila duplicada, pero no se revirtió la transacción completa.A warning was issued and the duplicate row ignored, but the entire transaction was not rolled back.

Las mismas instrucciones se ejecutan nuevamente, pero con IGNORE_DUP_KEY establecido en OFF.The same statements are executed again, but with IGNORE_DUP_KEY set to OFF.

CREATE TABLE #Test (C1 nvarchar(10), C2 nvarchar(50), C3 datetime);
GO
CREATE UNIQUE INDEX AK_Index ON #Test (C2)
  WITH (IGNORE_DUP_KEY = OFF);
GO
INSERT INTO #Test VALUES (N'OC', N'Ounces', GETDATE());
INSERT INTO #Test SELECT * FROM Production.UnitMeasure;
GO
SELECT COUNT(*)AS [Number of rows] FROM #Test;
GO
DROP TABLE #Test;
GO

A continuación se muestran los resultados de la segunda instrucción INSERT.Here are the results of the second INSERT statement.

Server: Msg 2601, Level 14, State 1, Line 5
Cannot insert duplicate key row in object '#Test' with unique index
'AK_Index'. The statement has been terminated.

Number of rows
--------------
1

Observe que ninguna de las filas de la tabla Production.UnitMeasure se insertó en la tabla aunque solo una fila de la tabla infringió la restricción de índice UNIQUE.Notice that none of the rows from the Production.UnitMeasure table were inserted into the table even though only one row in the table violated the UNIQUE index constraint.

G.G. Usar DROP_EXISTING para quitar y volver a crear un índiceUsing DROP_EXISTING to drop and re-create an index

En el ejemplo siguiente se quita y se vuelve a crear un índice existente en la columna ProductID de la tabla Production.WorkOrder en la base de datos AdventureWorks2012AdventureWorks2012 utilizando la opción DROP_EXISTING.The following example drops and re-creates an existing index on the ProductID column of the Production.WorkOrder table in the AdventureWorks2012AdventureWorks2012 database by using the DROP_EXISTING option. También se establecen las opciones FILLFACTOR y PAD_INDEX .The options FILLFACTOR and PAD_INDEX are also set.

CREATE NONCLUSTERED INDEX IX_WorkOrder_ProductID
  ON Production.WorkOrder(ProductID)
    WITH (FILLFACTOR = 80,
      PAD_INDEX = ON,
      DROP_EXISTING = ON);
GO

H.H. Crear un índice en una vistaCreate an index on a view

Este ejemplo siguiente crea una vista y un índice en esa vista.The following example creates a view and an index on that view. Se incluyen dos consultas que utilizan la vista indizada.Two queries are included that use the indexed view.

-- Set the options to support indexed views
SET NUMERIC_ROUNDABORT OFF;
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT,
  QUOTED_IDENTIFIER, ANSI_NULLS ON;
GO
-- Create view with schemabinding
IF OBJECT_ID ('Sales.vOrders', 'view') IS NOT NULL
DROP VIEW Sales.vOrders ;
GO
CREATE VIEW Sales.vOrders
WITH SCHEMABINDING
AS
  SELECT SUM(UnitPrice*OrderQty*(1.00-UnitPriceDiscount)) AS Revenue,
    OrderDate, ProductID, COUNT_BIG(*) AS COUNT
  FROM Sales.SalesOrderDetail AS od, Sales.SalesOrderHeader AS o
  WHERE od.SalesOrderID = o.SalesOrderID
  GROUP BY OrderDate, ProductID;
GO
-- Create an index on the view
CREATE UNIQUE CLUSTERED INDEX IDX_V1
  ON Sales.vOrders (OrderDate, ProductID);
GO
-- This query can use the indexed view even though the view is
-- not specified in the FROM clause.
SELECT SUM(UnitPrice*OrderQty*(1.00-UnitPriceDiscount)) AS Rev,
  OrderDate, ProductID
FROM Sales.SalesOrderDetail AS od
  JOIN Sales.SalesOrderHeader AS o ON od.SalesOrderID=o.SalesOrderID
    AND ProductID BETWEEN 700 and 800
    AND OrderDate >= CONVERT(datetime,'05/01/2002',101)
GROUP BY OrderDate, ProductID
ORDER BY Rev DESC;
GO
-- This query can use the above indexed view
SELECT OrderDate, SUM(UnitPrice*OrderQty*(1.00-UnitPriceDiscount)) AS Rev
FROM Sales.SalesOrderDetail AS od
  JOIN Sales.SalesOrderHeader AS o ON od.SalesOrderID=o.SalesOrderID
    AND DATEPART(mm,OrderDate)= 3
  AND DATEPART(yy,OrderDate) = 2002
GROUP BY OrderDate
ORDER BY OrderDate ASC;
GO

I.I. Crear un índice con columnas incluidas (sin clave)Create an index with included (non-key) columns

El ejemplo siguiente crea un índice no clúster con una columna de clave (PostalCode) y cuatro columnas que no son de clave (AddressLine1, AddressLine2, City, StateProvinceID).The following example creates a nonclustered index with one key column (PostalCode) and four non-key columns (AddressLine1, AddressLine2, City, StateProvinceID). A continuación se presenta una consulta cubierta por el índice.A query that is covered by the index follows. Para mostrar el índice seleccionado con el optimizador de consultas, en el menú Consulta de SQL Server Management StudioSQL Server Management Studio, seleccione Mostrar plan de ejecución estimado antes de ejecutar la consulta.To display the index that is selected by the query optimizer, on the Query menu in SQL Server Management StudioSQL Server Management Studio, select Display Actual Execution Plan before executing the query.

CREATE NONCLUSTERED INDEX IX_Address_PostalCode
  ON Person.Address (PostalCode)
  INCLUDE (AddressLine1, AddressLine2, City, StateProvinceID);
GO
SELECT AddressLine1, AddressLine2, City, StateProvinceID, PostalCode
FROM Person.Address
WHERE PostalCode BETWEEN N'98000' and N'99999';
GO

J.J. Crear un índice con particionesCreate a partitioned index

En el ejemplo siguiente se crea un índice no clúster con particiones en TransactionsPS1, un esquema de partición existente en la base de datos AdventureWorks2012AdventureWorks2012.The following example creates a nonclustered partitioned index on TransactionsPS1, an existing partition scheme in the AdventureWorks2012AdventureWorks2012 database. En este ejemplo se supone que se ha instalado el ejemplo de índice con particiones.This example assumes the partitioned index sample has been installed.

Se aplica a: SQL ServerSQL Server (desde SQL Server 2008SQL Server 2008 hasta SQL Server 2017SQL Server 2017) y Base de datos SQL de AzureAzure SQL Database.Applies to: SQL ServerSQL Server (SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017) and Base de datos SQL de AzureAzure SQL Database

CREATE NONCLUSTERED INDEX IX_TransactionHistory_ReferenceOrderID
  ON Production.TransactionHistory (ReferenceOrderID)
  ON TransactionsPS1 (TransactionDate);
GO

K.K. Crear un índice filtradoCreating a filtered index

En el ejemplo siguiente se crea un índice filtrado en la tabla Production.BillOfMaterials de la base de datos AdventureWorks2012AdventureWorks2012.The following example creates a filtered 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.

CREATE NONCLUSTERED INDEX "FIBillOfMaterialsWithEndDate"
  ON Production.BillOfMaterials (ComponentID, StartDate)
  WHERE EndDate IS NOT NULL;

L.L. Crear un índice comprimidoCreate a compressed index

En el ejemplo siguiente se crea un índice en una tabla sin particiones utilizando la compresión de fila.The following example creates an index on a nonpartitioned table by using row compression.

CREATE NONCLUSTERED INDEX IX_INDEX_1
  ON T1 (C2)
WITH ( DATA_COMPRESSION = ROW );
GO

En el ejemplo siguiente se crea un índice en una tabla con particiones utilizando la compresión de fila en todas las particiones del índice.The following example creates an index on a partitioned table by using row compression on all partitions of the index.

CREATE CLUSTERED INDEX IX_PartTab2Col1
ON PartitionTable1 (Col1)
WITH ( DATA_COMPRESSION = ROW );
GO

En el ejemplo siguiente se crea un índice en una tabla con particiones utilizando la compresión de página en la partición 1 del índice y la compresión de fila en las particiones 2 a 4 del índice.The following example creates an index on a partitioned table by using page compression on partition 1 of the index and row compression on partitions 2 through 4 of the index.

CREATE CLUSTERED INDEX IX_PartTab2Col1
ON PartitionTable1 (Col1)
WITH (DATA_COMPRESSION = PAGE ON PARTITIONS(1),
  DATA_COMPRESSION = ROW ON PARTITIONS (2 TO 4 ) ) ;
GO

M.M. Crear, reanudar, pausar y anular operaciones de índices reanudablesCreate, resume, pause, and abort resumable index operations

Se aplica a: SQL ServerSQL Server (A partir de SQL Server 2019SQL Server 2019) y Base de datos SQL de AzureAzure SQL Database (versión preliminar pública)Applies to: SQL ServerSQL Server (Starting with SQL Server 2019SQL Server 2019) and Base de datos SQL de AzureAzure SQL Database (public preview)

-- Execute a resumable online index create statement with MAXDOP=1
CREATE INDEX test_idx1 on test_table (col1) WITH (ONLINE=ON, MAXDOP=1, RESUMABLE=ON)

-- Executing the same command again (see above) after an index operation was paused, resumes automatically the index create operation.

-- Execute a resumable online index creates operation with MAX_DURATION set to 240 minutes. After the time expires, the resumable index create operation is paused.
CREATE INDEX test_idx2 on test_table (col2) WITH (ONLINE=ON, RESUMABLE=ON, MAX_DURATION=240)

-- Pause a running resumable online index creation
ALTER INDEX test_idx1 on test_table PAUSE
ALTER INDEX test_idx2 on test_table PAUSE

-- Resume a paused online index creation
ALTER INDEX test_idx1 on test_table RESUME
ALTER INDEX test_idx2 on test_table RESUME

-- Abort resumable index create operation which is running or paused
ALTER INDEX test_idx1 on test_table ABORT
ALTER INDEX test_idx2 on test_table ABORT

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

N.N. Sintaxis básicaBasic syntax

Crear, reanudar, pausar y anular operaciones de índices reanudablesCreate, resume, pause, and abort resumable index operations

Se aplica a: SQL ServerSQL Server (A partir de SQL Server 2019SQL Server 2019) y Base de datos SQL de AzureAzure SQL Database (versión preliminar pública)Applies to: SQL ServerSQL Server (Starting with SQL Server 2019SQL Server 2019) and Base de datos SQL de AzureAzure SQL Database (public preview)

-- Execute a resumable online index create statement with MAXDOP=1
CREATE INDEX test_idx on test_table WITH (ONLINE=ON, MAXDOP=1, RESUMABLE=ON)

-- Executing the same command again (see above) after an index operation was paused, resumes automatically the index create operation.

-- Execute a resumable online index creates operation with MAX_DURATION set to 240 minutes. After the time expires, the resumable index create operation is paused.
CREATE INDEX test_idx on test_table WITH (ONLINE=ON, RESUMABLE=ON, MAX_DURATION=240)

-- Pause a running resumable online index creation
ALTER INDEX test_idx on test_table PAUSE

-- Resume a paused online index creation
ALTER INDEX test_idx on test_table RESUME

-- Abort resumable index create operation which is running or paused
ALTER INDEX test_idx on test_table ABORT

O.O. Creación de un índice no agrupado en una tabla en la base de datos actualCreate a nonclustered index on a table in the current database

El ejemplo siguiente crea un índice no clúster en la columna VendorID de la tabla ProductVendor.The following example creates a nonclustered index on the VendorID column of the ProductVendor table.

CREATE INDEX IX_ProductVendor_VendorID
  ON ProductVendor (VendorID);

P.P. Crear un índice agrupado en una tabla de otra base de datosCreate a clustered index on a table in another database

En el ejemplo siguiente se crea un índice no clúster en la columna VendorID de la tabla ProductVendoren la base de datos Purchasing.The following example creates a nonclustered index on the VendorID column of the ProductVendor table in the Purchasing database.

CREATE CLUSTERED INDEX IX_ProductVendor_VendorID
  ON Purchasing..ProductVendor (VendorID);

Consulte tambiénSee Also

Guía de diseño y de arquitectura de índices de SQL Server SQL Server Index Architecture and Design Guide
Índices y ALTER TABLE Indexes and ALTER TABLE
ALTER INDEX ALTER INDEX
CREATE PARTITION FUNCTION CREATE PARTITION FUNCTION
CREATE PARTITION SCHEME CREATE PARTITION SCHEME
CREATE SPATIAL INDEX CREATE SPATIAL INDEX
CREATE STATISTICS CREATE STATISTICS
CREATE TABLE CREATE TABLE
CREATE XML INDEX CREATE XML INDEX
Tipos de datos Data Types
DBCC SHOW_STATISTICS DBCC SHOW_STATISTICS
DROP INDEX DROP INDEX
Índices XML (SQL Server) XML Indexes (SQL Server)
sys.indexes sys.indexes
sys.index_columns sys.index_columns
sys.xml_indexes sys.xml_indexes
EVENTDATAEVENTDATA