CREATE INDEX (Transact-SQL)

  • Artículo

Crea un índice relacional en una tabla especificada o una vista de una tabla especificada. Se puede crear un índice antes de que la tabla posea datos. Los índices relacionales se pueden crear en tablas o vistas de otra base de datos especificando un nombre completo de base de datos.

[!NOTA]

Para obtener información acerca de cómo crear un índice XML, vea CREATE XML INDEX (Transact-SQL). Para obtener información acerca de cómo crear un índice espacial, vea CREATE SPATIAL INDEX (Transact-SQL). Para obtener información acerca de cómo crear un índice de almacén de columnas optimizado de memoria xVelocity, vea CREATE COLUMNSTORE INDEX (Transact-SQL).

Icono de vínculo a temas Convenciones de sintaxis de Transact-SQL

Sintaxis

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 ] . | schema_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 }
  | DROP_EXISTING = { ON | OFF }
  | ONLINE = { ON | OFF }
  | ALLOW_ROW_LOCKS = { ON | OFF }
  | ALLOW_PAGE_LOCKS = { 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>


Backward Compatible Relational Index 
Important   The backward compatible relational index syntax structure will be removed in a future version of SQL Server. Avoid using this syntax structure in new development work, and plan to modify applications that currently use the feature. 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 
}

Argumentos

  • UNIQUE
    Crea un índice único en una tabla o una vista. Un índice único es aquel en el que no se permite que dos filas tengan el mismo valor de clave del índice. El índice clúster de una vista debe ser único.

    Motor de base de datos 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. Si se intenta, Motor de base de datos muestra un mensaje de error. Se deben quitar los valores duplicados para poder crear un índice único en la columna o columnas. 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.

  • CLUSTERED
    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. El nivel inferior, u hoja, de un índice clúster contiene las filas de datos reales de la tabla. Una tabla o vista permite un índice clúster al mismo tiempo.

    Una vista con un índice clúster único se denomina vista indizada. La creación de un índice clúster único en una vista materializa físicamente la vista. Es necesario crear un índice clúster único en una vista para poder definir otros índices en la misma vista. Para obtener más información, vea Crear vistas indizadas.

    Cree el índice clúster antes de crear los índices no clúster. Los índices no clúster existentes en las tablas se vuelven a generar al crear un índice clúster.

    Si no se especifica CLUSTERED, se crea un índice no clúster.

    [!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. 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.

    En algunos casos, al crear un índice clúster se pueden habilitar previamente los índices deshabilitados. Para obtener más información, vea Habilitar índices y restricciones y Deshabilitar índices y restricciones.

  • NONCLUSTERED
    Crea un índice que especifica la ordenación lógica de una tabla. Con un índice no clúster, el orden físico de las filas de datos es independiente del orden indizado.

    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.

    Para las vistas indizadas, solo se pueden crear índices no clúster en una vista que ya tenga definido un índice clúster único.

    El valor predeterminado es NONCLUSTERED.

  • index_name
    Es el nombre del índice. Los nombres de índice deben ser únicos en una tabla o vista, pero no es necesario que sean únicos en una base de datos. Los nombres de índice deben seguir las reglas de los identificadores.

  • column
    Es la columna o columnas en las que se basa el índice. Especifique dos o más nombres de columna para crear un índice compuesto sobre los valores combinados de las columnas especificadas. Enumere las columnas que desee incluir en el índice compuesto (en orden de prioridad) entre paréntesis después de table_or_view_name.

    Se pueden combinar hasta 16 columnas en la clave de un único índice compuesto. Todas las columnas de una clave del índice compuesto deben encontrarse en la misma tabla o vista. El tamaño máximo permitido de los valores de índice combinado es 900 bytes.

    Las columnas de tipos de datos de objetos grandes (LOB) ntext, text, varchar(max), nvarchar(max), varbinary(max), xml o image no se pueden especificar como columnas de clave de un índice. 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.

    Puede crear índices en columnas de tipo definido por el usuario CLR si el tipo admite el orden binario. 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. Para obtener más información acerca de la indización de columnas de tipo definido por el usuario CLR, vea Tipos definidos por el usuario CLR.

  • [ ASC | DESC ]
    Determina la dirección ascendente o descendente del orden de la columna de índice determinada. El valor predeterminado es ASC.

  • INCLUDE **(**column [ ,... n ] )
    Especifica las columnas que no son de clave que se agregarán en el nivel hoja del índice no clúster. El índice no clúster puede ser único o no único.

    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. Los índices no clúster siempre contienen las columnas de índice clúster si se define un índice clúster en la tabla. Para obtener más información, vea Crear índices con columnas incluidas.

    Se admiten todos los tipos de datos, a excepción de text, ntext e 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).

    Las columnas calculadas que son deterministas, y precisas o imprecisas, pueden ser columnas incluidas. Las columnas calculadas derivadas de los tipos de datos image, ntext, text, varchar(max), nvarchar(max), varbinary(max) y xml pueden ser columnas que no son de clave incluidas, siempre que los tipos de datos de las columnas calculadas sean aceptables como columna incluida. Para obtener más información, vea Índices en columnas calculadas.

    Para obtener información acerca de cómo crear un índice XML, vea CREATE XML INDEX (Transact-SQL).

  • WHERE <filter_predicate>
    Crea un índice filtrado especificando qué filas se van a incluir en el índice. El índice filtrado debe ser un índice no clúster en una tabla. Crea las estadísticas filtradas para las filas de datos en el índice filtrado.

    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. Las comparaciones que utilizan literales NULL no se admiten con los operadores de comparación. En su lugar, use los operadores IS NULL e IS NOT NULL.

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

    WHERE StartDate > '20000101' AND EndDate <= '20000630'

    WHERE ComponentID IN (533, 324, 753)

    WHERE StartDate IN ('20000404', '20000905') AND EndDate IS NOT NULL

    Los índices filtrados no se aplican a los índices XML ni a los índices de texto completo. Para los índices UNIQUE, solo las filas seleccionadas deben tener valores de índice únicos. Los índices filtrados no admiten la opción IGNORE_DUP_KEY.

  • ON partition_scheme_name**(column_name)**
    Especifica el esquema de partición que define los grupos de archivos a los que se asignarán las particiones de un índice con particiones. El esquema de partición debe existir dentro de la base de datos mediante la ejecución de CREATE PARTITION SCHEME o de ALTER PARTITION SCHEME. column_name especifica la columna en la que se van a crear las particiones de un índice con particiones. Esta columna debe coincidir con el tipo de datos, la longitud y la precisión del argumento de la función de partición que partition_scheme_name emplea. column_name no está limitado a las columnas de la definición del índice. Se pueden especificar todas las columnas de la tabla base, excepto en el caso de partición de un índice UNIQUE en el que se debe elegir un valor para column_name entre las columnas utilizadas como clave única. Esta restricción permite que Motor de base de datos compruebe la unicidad de los valores de clave en una única partición solamente.

    [!NOTA]

    Cuando se crean particiones en un índice clúster no único, Motor de base de datos 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. Cuando se crean particiones en un índice no clúster que tampoco es único, Motor de base de datos agrega la columna de partición como una columna sin clave (incluida) del índice, si aún no se especificó.

    Si no se especificó partition_scheme_name o filegroup y se han creado particiones en la tabla, el índice se sitúa en el mismo esquema de partición y se utiliza la misma columna de partición para la tabla subyacente.

    [!NOTA]

    No se puede especificar un esquema de partición en un índice XML. Si se crean particiones en la tabla base, el índice XML usa el mismo esquema de partición que la tabla.

    Para obtener más información acerca de cómo crear particiones de índices, vea Tablas e índices con particiones.

  • ON filegroup_name
    Crea el índice especificado en el grupo de archivos indicado. 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. El grupo de archivos debe existir previamente.

  • ON "default"
    Crea el índice especificado en el grupo de archivos predeterminado.

    El término predeterminado (default), en este contexto, no es una palabra clave. Es un identificador para el grupo de archivos predeterminado y debe delimitarse, como en ON "default" o en ON [default]. Si se especifica "default", la opción QUOTED_IDENTIFIER debe ser ON para la sesión actual. Ésta es la configuración predeterminada. Para obtener más información, vea SET QUOTED_IDENTIFIER (Transact-SQL).

  • [ FILESTREAM_ON { filestream_filegroup_name | partition_scheme_name | "NULL" } ]
    Especifica la posición de datos FILESTREAM para la tabla cuando se crea un índice clúster. La cláusula FILESTREAM_ON permite mover los datos FILESTREAM a otro esquema de partición o a otro grupo de archivos FILESTREAM.

    filestream_filegroup_name es el nombre de un grupo de archivos FILESTREAM. El grupo de archivos debe tener un archivo definido para el grupo de archivos, utilizando para ello las instrucciones CREATE DATABASE o ALTER DATABASE; de lo contrario, se producirá un error.

    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. En caso contrario, se produce un error.

    Si la tabla no tiene particiones, no se pueden crear particiones en la columna FILESTREAM. Los datos FILESTREAM para la tabla deben estar almacenados en un grupo de archivos único que se especifica en la cláusula FILESTREAM_ON.

    NULL de FILESTREAM_ON 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.

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

<object>::=

Es el objeto completo o no que se indizará.

  • database_name
    Es el nombre de la base de datos.

  • schema_name
    Es el nombre del esquema al que pertenece la tabla o la vista.

  • table_or_view_name
    Es el nombre de la tabla o la vista que se va a indizar.

    La vista debe definirse con SCHEMABINDING para crear un índice en ella. Es necesario crear un índice clúster único en una vista antes de crear los índices no clúster. Para obtener más información acerca de las vistas indizadas, vea la sección Comentarios.

<relational_index_option>::=

Especifica las opciones que se van a utilizar en la creación del índice.

  • PAD_INDEX = { ON | OFF }
    Especifica el relleno de índice. El valor predeterminado es OFF.

    • ON
      El porcentaje de espacio disponible especificado por fillfactor se aplica a las páginas de nivel intermedio del índice.

    • No se especifica OFF ni fillfactor.
      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.

    La opción PAD_INDEX solamente resulta útil si también se especifica FILLFACTOR, porque PAD_INDEX utiliza el mismo porcentaje especificado por FILLFACTOR. Si el porcentaje especificado para FILLFACTOR no es lo suficientemente grande como para admitir una fila, Motor de base de datos invalida internamente el porcentaje para permitir el valor mínimo. 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.

    En la sintaxis compatible con versiones anteriores, WITH PAD_INDEX es equivalente a WITH PAD_INDEX = ON.

  • FILLFACTOR **=**fillfactor
    Especifica un porcentaje que indica cuánto debe llenar el Motor de base de datos el nivel hoja de cada página de índice durante la creación o nueva generación de los índices. fillfactor debe ser un valor entero comprendido entre 1 y 100. Si fillfactor es 100, el Motor de base de datos crea índices con páginas hoja rellenas en toda su capacidad.

    El valor FILLFACTOR solo se aplica cuando se crea o se regenera el índice. Motor de base de datos no mantiene dinámicamente el porcentaje especificado de espacio disponible de las páginas. Para ver la configuración de fill factor, utilice la vista de catálogo sys.indexes.

    Nota importanteImportante

    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 datos vuelve a distribuir los datos cuando crea el índice clúster.

    Para obtener más información, vea Especificar el factor de relleno para un índice.

  • SORT_IN_TEMPDB = { ON | OFF }
    Indica si deben almacenarse resultados temporales de orden en tempdb. El valor predeterminado es OFF.

    • ON
      Los resultados de ordena intermedios utilizados para generar el índice se almacenan en 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. Sin embargo, esto aumenta la cantidad de espacio en disco utilizado durante la creación del índice.

    • OFF
      Los resultados de orden intermedios se almacenan en la misma base de datos que el índice.

    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. Para obtener más información, vea Opción SORT_IN_TEMPDB para índices.

    En la sintaxis compatible con versiones anteriores, WITH SORT_IN_TEMPDB es equivalente a WITH SORT_IN_TEMPDB = ON.

  • 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. La opción IGNORE_DUP_KEY se aplica solamente a operaciones de inserción realizadas tras crear o volver a generar el índice. La opción no tiene efecto cuando se ejecutan CREATE INDEX, ALTER INDEX o UPDATE. El valor predeterminado es OFF.

    • ON
      Se producirá un mensaje de advertencia cuando se inserten valores de clave duplicados en un índice único. Solo las filas que infrinjan la restricción de unicidad darán error.

    • OFF
      Se producirá un mensaje de error cuando se inserten valores de clave duplicados en un índice único. Toda la operación INSERT se revertirá.

    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.

    Para ver IGNORE_DUP_KEY, utilice sys.indexes.

    En la sintaxis compatible con versiones anteriores, WITH IGNORE_DUP_KEY es equivalente a WITH IGNORE_DUP_KEY = ON.

  • STATISTICS_NORECOMPUTE = { ON | OFF}
    Especifica si se vuelven a calcular las estadísticas de distribución. El valor predeterminado es OFF.

    • ON
      Las estadísticas obsoletas no se vuelven a calcular automáticamente.

    • OFF
      Se habilita la actualización automática de las estadísticas.

    Para restaurar la actualización automática de estadísticas, establezca STATISTICS_NORECOMPUTE en OFF o ejecute UPDATE STATISTICS sin la cláusula NORECOMPUTE.

    Nota importanteImportante

    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.

    En la sintaxis compatible con versiones anteriores, WITH STATISTICS_NORECOMPUTE es equivalente a WITH STATISTICS_NORECOMPUTE = ON.

  • DROP_EXISTING = { ON | OFF }
    Especifica que el índice clúster o no clúster preexistente mencionado debe quitarse y volver a generarse. El valor predeterminado es OFF.

    • ON
      El índice existente se quita y se vuelve a generar. El nombre de índice especificado debe ser el mismo que el de un índice actualmente existente; sin embargo, la definición se puede modificar. Por ejemplo, puede especificar columnas, criterio de ordenación, esquema de particionamiento u opciones de índice diferentes.

    • OFF
      Se muestra un error si ya existe el nombre de índice especificado.

    El tipo de índice no puede cambiarse utilizando DROP_EXISTING.

    En la sintaxis compatible con versiones anteriores, WITH DROP_EXISTING es equivalente a WITH DROP_EXISTING = ON.

  • 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 indización. El valor predeterminado es OFF.

    [!NOTA]

    Las operaciones de índices en línea no están disponibles en todas las ediciones de Microsoft SQL Server. Para obtener una lista de características admitidas por las ediciones de SQL Server, vea Características compatibles con las ediciones de SQL Server 2012.

    • ON
      Los bloqueos de tabla de larga duración no se mantienen durante la operación de indización. Durante la fase principal de la operación de índice, solo se mantiene un bloqueo preventivo en la tabla de origen. Esto habilita las consultas o actualizaciones en la tabla subyacente y en los índices. 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. 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. ONLINE no se puede establecer en ON cuando se crea un índice en una tabla temporal local.

    • OFF
      Los bloqueos de tabla se aplican durante la operación de índice. 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. Esto evita que todos los usuarios tengan acceso a la tabla subyacente durante la operación. Una operación de índice sin conexión que crea un índice no clúster adquiere un bloqueo compartido (S) en la tabla. Esto evita que se realicen actualizaciones en la tabla subyacente, pero permite la realización de operaciones de lectura, como instrucciones SELECT.

    Para obtener más información, vea Cómo funcionan las operaciones de índice en línea.

    Los índices, incluidos los índices de las tablas temp globales, se pueden crear en línea, con las excepciones siguientes:

    • Índice XML

    • Índice en una tabla temp local

    • Índice clúster único inicial en una vista.

    • Índices clúster deshabilitados.

    • Índice clúster si la tabla subyacente contiene tipos de datos LOB: image, ntext, text y tipos espaciales.

    Para obtener más información, vea Realizar operaciones de índice en línea.

  • ALLOW_ROW_LOCKS = { ON | OFF }
    Especifica si se admiten los bloqueos de fila. El valor predeterminado es ON.

    • ON
      Los bloqueos de fila se admiten al obtener acceso al índice. Motor de base de datos determina cuándo se utilizan los bloqueos de fila.

    • OFF
      Los bloqueos de fila no se utilizan.

  • ALLOW_PAGE_LOCKS = { ON | OFF }
    Especifica si se permiten bloqueos de página. El valor predeterminado es ON.

    • ON
      Los bloqueos de página se permiten al obtener acceso al índice. El Motor de base de datos determina el momento en que se utilizan bloqueos de página.

    • OFF
      No se utilizan bloqueos de página.

  • MAXDOP = max_degree_of_parallelism
    Invalida la opción de configuración Establecer la opción de configuración del servidor Grado máximo de paralelismo mientras se prolongue la operación del índice. Utilice MAXDOP para establecer un límite para el número de procesadores utilizados en la ejecución de un plan paralelo. El máximo es 64 procesadores.

    max_degree_of_parallelism puede ser:

    • 1
      Suprime la generación de planes paralelos.

    • >1
      Restringe el número máximo de procesadores utilizados en una operación de índice paralelo al número especificado o a un número inferior, en función de la actual carga de trabajo del sistema.

    • 0 (predeterminado)
      Usa el número real de procesadores o menos, según la carga de trabajo actual del sistema.

    Para obtener más información, vea Configurar operaciones de índice en paralelo.

    [!NOTA]

    Las operaciones de índices en paralelo no están disponibles en todas las ediciones de Microsoft SQL Server. Para obtener una lista de características admitidas por las ediciones de SQL Server, vea Características compatibles con las ediciones de SQL Server 2012.

  • DATA_COMPRESSION
    Especifica la opción de compresión de datos para el índice, número de partición o intervalo de particiones especificado. Las opciones son las siguientes:

    • NONE
      No se comprimen el índice ni las particiones especificadas.

    • ROW
      El índice o las particiones especificadas se comprimen mediante la compresión de fila.

    • PAGE
      El índice o las particiones especificadas se comprimen mediante la compresión de página.

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

  • ON PARTITIONS ( { <partition_number_expression> | <range> } [ ,...n ] )
    Especifica las particiones a las que se aplica el valor DATA_COMPRESSION. Si el índice no tiene particiones, el argumento ON PARTITIONS generará un 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.

    <partition_number_expression> se puede especificar de las maneras siguientes:

    • Proporcionar el número de una partición, por ejemplo: ON PARTITIONS (2).

    • Proporcionar los números de partición de varias particiones separados por comas, por ejemplo: ON PARTITIONS (1, 5).

    • Proporcionar intervalos y particiones individuales: ON PARTITIONS (2, 4, 6 TO 8).

    <intervalo> se puede especificar como números de partición separados por la palabra TO, por ejemplo: 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:

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

Comentarios

La instrucción CREATE INDEX se optimiza como cualquier otra consulta. Para guardar en operaciones de E/S, el procesador de consultas puede elegir examinar otro índice en lugar de realizar un recorrido de tabla. La operación de orden se puede eliminar en algunos casos. 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. Para obtener más información, vea Configurar operaciones de índice en paralelo.

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.

Los índices se pueden crear en una tabla temporal. Cuando se quita la tabla o finaliza la sesión, se quitan los índices.

Los índices admiten propiedades extendidas.

Índices clúster

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. Para obtener más información sobre los índices clúster, vea Crear índices clúster.

Índices únicos

Cuando existe un índice único, Motor de base de datos comprueba si hay valores duplicados cada vez que se agregan datos con una operación de inserción. Las operaciones de inserción que generarían valores de clave duplicados se revierten y el Motor de base de datos muestra un mensaje de error. Esto se cumple incluso si la operación de inserción cambia muchas filas pero crea un único duplicado. 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.

Índices con particiones

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

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. 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. Cuando el índice usa el mismo esquema y columna de partición que la tabla, el índice está alineado con la tabla.

Nota de advertenciaAdvertencia

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. Si se hace, se puede degradar el rendimiento o consumir excesiva memoria durante estas operaciones. Se recomienda usar solo índices alineados cuando el número de particiones sea superior a 1.000.

Cuando se crean particiones en un índice clúster no único, el Motor de base de datos 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.

Se pueden crear vistas indizadas en tablas con particiones de la misma manera que se hace con índices en tablas. Para obtener más información acerca de los índices con particiones, vea Tablas e índices con particiones.

En SQL Server 2012, 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. En su lugar, el optimizador de consultas usa el algoritmo de muestreo predeterminado para generar estadísticas. 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.

Índices filtrados

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. Utiliza un predicado de filtro para indizar una parte de los datos de la tabla. Un índice filtrado bien diseñado puede mejorar el rendimiento de las consultas, reducir los costos de almacenamiento y de mantenimiento.

Opciones SET requeridas para los índices filtrados

Las opciones SET de la columna Required Value son necesarias siempre que se dé alguna de las condiciones siguientes:

  • Se crea un índice filtrado.

  • La operación INSERT, UPDATE, DELETE o MERGE modifica los datos de un índice filtrado.

  • El optimizador de consultas utiliza el índice filtrado del plan de ejecución de consultas.

    Opciones SET

    Valor requerido

    ANSI_NULLS

    ON

    ANSI_PADDING

    ON

    ANSI_WARNINGS*

    ON

    ARITHABORT

    ON

    CONCAT_NULL_YIELDS_NULL

    ON

    NUMERIC_ROUNDABORT

    OFF

    QUOTED_IDENTIFIER

    ON

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

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

  • El índice filtrado no se crea.

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

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

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

Índices espaciales

Para obtener información sobre índices espaciales, vea CREATE SPATIAL INDEX (Transact-SQL) y Información general sobre los índices espaciales.

Índices XML

Para obtener información acerca de los índices XML, vea CREATE XML INDEX (Transact-SQL) y Índices XML (SQL Server).

Tamaño de clave de índice

El tamaño máximo para una clave de índice es 900 bytes. Se pueden crear índices en las columnas varchar cuyo tamaño sea superior a 900 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 900 bytes. La clave de índice de un índice clúster no puede contener columnas varchar con datos existentes en la unidad de asignación ROW_OVERFLOW_DATA. Si se crea un índice clúster 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 de la columna que intenten insertar los datos de manera no consecutiva.

Los índices no clúster pueden incluir columnas que no son de clave en el nivel de hoja del índice. Motor de base de datos no tiene en cuenta estas columnas al calcular el tamaño de clave de índice. Para obtener más información, vea Crear índices con columnas incluidas.

[!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 datos las agrega al índice. 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.

Columnas calculadas

Los índices se pueden crear en columnas calculadas. Además, las columnas calculadas pueden tener la propiedad PERSISTED. Esto significa que Motor de base de datos almacena los valores calculados en la tabla y los actualiza cuando se actualiza cualquier otra columna de la que depende la columna calculada. Motor de base de datos utiliza estos valores persistentes cuando crea un índice en la columna y cuando se hace referencia al índice en una consulta.

Para indizar una columna calculada, ésta debe ser determinista y precisa. No obstante, si se usa la propiedad PERSISTED, se amplía el tipo de columnas calculadas indizables para incluir:

  • Las columnas calculadas basadas en Transact-SQL, funciones CLR y métodos de tipos definidos por el usuario CLR que el usuario ha marcado como deterministas.

  • Las columnas calculadas basadas en expresiones que son deterministas, como se definen en Motor de base de datos, aunque imprecisas.

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 vistas indizadas".

Las restricciones UNIQUE o PRIMARY KEY pueden contener una columna calculada siempre que cumplan con todas las condiciones de creación del índice. En concreto, la columna calculada debe ser determinista y precisa, o determinista y persistente. Para obtener más información acerca del determinismo, vea Funciones deterministas y no deterministas.

Las columnas calculadas derivadas de los tipos de datos image, ntext, text, varchar(max), nvarchar(max), varbinary(max) y xml se pueden indizar como columnas de clave o sin clave incluida, siempre que el tipo de datos de la columna calculada esté disponible como una columna de clave de índice o columna que no es de clave. Por ejemplo, no puede crear un índice XML principal en una columna xml calculada. Si el tamaño de clave de índice supera los 900 bytes, se muestra un mensaje de advertencia.

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. Este error podría ocurrir cuando la columna calculada produce un error aritmético. Por ejemplo, aunque la columna calculada c de la tabla siguiente produzca un error aritmético, la instrucción INSERT funcionará.

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.

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 Índices en columnas calculadas.

Columnas incluidas en índices

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. 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. 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. Para obtener más información, vea Crear índices con columnas incluidas.

Especificar opciones de índice

SQL Server 2005 incluye opciones de índice nuevas y también modifica el modo en que se especifican las opciones. En la sintaxis compatible con versiones anteriores, WITH option_name es equivalente a WITH ( <option_name> = ON ). Al establecer opciones de índice, se aplican las siguientes reglas:

  • Solo se pueden especificar nuevas opciones de índice mediante 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. Por ejemplo, al especificar WITH (DROP_EXISTING, ONLINE = ON**)**, se genera un error en la instrucción.

  • Cuando se crea un índice XML, las opciones se deben especificar mediante WITH (option_name = ON | OFF).

Cláusula DROP_EXISTING

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.

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. Sin embargo, si se ha modificado la definición de índice, se genera un error en la instrucción. 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.

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 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. Los índices no clúster se vuelven a generar una vez, siempre que la definición de índice haya cambiado. 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.

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. 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. 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. Para obtener más información sobre la alineación de índices con particiones, vea.

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. Si se deshabilita el índice clúster, se debe establecer ONLINE en OFF para la operación CREATE INDEX WITH DROP_EXISTING. 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.

Cuando se quitan o se vuelven a generar índices con 128 o más extensiones, el Motor de base de datos aplaza las cancelaciones de asignación de página reales y los bloqueos asociados, hasta después de que se confirme la transacción.

Opción ONLINE

Las directrices siguientes se aplican para el desarrollo de operaciones de índice en línea:

  • La tabla subyacente no se podrá alterar, truncar ni quitar mientras haya una operación de índice en línea en curso.

  • La operación de índice requiere un espacio en disco temporal adicional.

  • Las operaciones en línea se pueden realizar en índices con particiones e índices que contienen columnas calculadas persistentes, o columnas incluidas.

Para obtener más información, vea Realizar operaciones de índice en línea.

Opciones de bloqueo de fila y página

Si ALLOW_ROW_LOCKS = ON y ALLOW_PAGE_LOCK = ON, se permiten los bloqueos de nivel de fila, página y tabla cuando se tiene acceso al índice. Motor de base de datos elige el bloqueo apropiado y puede cambiar de escala el bloqueo: de un bloqueo de fila o página a un bloqueo de tabla.

Si ALLOW_ROW_LOCKS = OFF y ALLOW_PAGE_LOCK = OFF, solo se permiten los bloqueos de nivel de tabla cuando se tiene acceso al índice.

Ver información de índice

Para devolver información sobre índices, puede utilizar vistas de catálogo, funciones del sistema y procedimientos almacenados del sistema.

Compresión de datos

La compresión de datos se describe en el tema Compresión de datos. A continuación se muestran los puntos clave que se deben tener en cuenta:

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

  • Las páginas no hoja de un índice no tienen compresión de página pero pueden tener compresión de fila.

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

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

Las restricciones siguientes se aplican a los índices con particiones:

  • No se puede cambiar la configuración de compresión de una partición única si la tabla tiene índices no alineados.

  • La sintaxis ALTER INDEX <index> ... La sintaxis REBUILD PARTITION ... vuelve a generar la partición especificada del índice.

  • La sintaxis ALTER INDEX <index> ... La sintaxis REBUILD WITH ... vuelve a generar todas las particiones del índice.

Para evaluar cómo afecta el cambio el estado de compresión a una tabla, índice o partición, utilice el procedimiento almacenado sp_estimate_data_compression_savings.

Permisos

Requiere el permiso ALTER en la tabla o la vista. El usuario debe ser miembro del rol fijo de servidor sysadmin o de los roles fijos de base de datos db_ddladmin y db_owner.

Ejemplos

A.Crear un índice no clúster sencillo

El ejemplo siguiente crea un índice no clúster en la columna VendorID de la tabla Purchasing.ProductVendor .

USE AdventureWorks2012;
GO
IF EXISTS (SELECT name FROM sys.indexes
            WHERE name = N'IX_ProductVendor_VendorID')
    DROP INDEX IX_ProductVendor_VendorID ON Purchasing.ProductVendor;
GO
CREATE INDEX IX_ProductVendor_VendorID 
    ON Purchasing.ProductVendor (BusinessEntityID); 
GO

B.Crear un índice compuesto no clúster sencillo

El ejemplo siguiente crea un índice compuesto no agrupado en las columnas SalesQuota y SalesYTD de la tabla Sales.SalesPerson.

USE AdventureWorks2012
GO
IF EXISTS (SELECT name FROM sys.indexes
            WHERE name = N'IX_SalesPerson_SalesQuota_SalesYTD')
    DROP INDEX IX_SalesPerson_SalesQuota_SalesYTD ON Sales.SalesPerson ;
GO
CREATE NONCLUSTERED INDEX IX_SalesPerson_SalesQuota_SalesYTD
    ON Sales.SalesPerson (SalesQuota, SalesYTD);
GO

C.Crear un índice no clúster único

El ejemplo siguiente crea un índice no clúster único en la columna Name de la tabla Production.UnitMeasure. El índice exigirá unicidad en los datos insertados en la columna Name.

USE AdventureWorks2012;
GO
IF EXISTS (SELECT name from sys.indexes
             WHERE name = N'AK_UnitMeasure_Name')
    DROP INDEX AK_UnitMeasure_Name ON Production.UnitMeasure;
GO
CREATE UNIQUE INDEX AK_UnitMeasure_Name 
    ON Production.UnitMeasure(Name);
GO

La consulta siguiente prueba la restricción de unicidad intentando insertar una fila con el mismo valor que el de una fila existente.

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

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.

D.Usar la opción IGNORE_DUP_KEY

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. 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. Un recuento de las filas de la tabla devuelve el número de filas insertadas.

USE AdventureWorks2012;
GO
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.

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. Se emitió una advertencia y se omitió la fila duplicada, pero no se revirtió la transacción completa.

Las mismas instrucciones se ejecutan nuevamente, pero con IGNORE_DUP_KEY establecido en OFF.

USE AdentureWorks2012;
GO
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.

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.

E.Usar DROP_EXISTING para quitar y volver a crear un índice

El ejemplo siguiente quita y vuelve a crear un índice existente en la columna ProductID de la tabla Production.WorkOrder utilizando la opción DROP_EXISTING. También se establecen las opciones FILLFACTOR y PAD_INDEX.

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

F.Crear un índice en una vista

Este ejemplo siguiente crea una vista y un índice en esa vista. Se incluyen dos consultas que utilizan la vista indizada.

USE AdventureWorks2012;
GO
--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

G.Crear un índice con columnas incluidas (sin clave)

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). A continuación se presenta una consulta cubierta por el índice. Para mostrar el índice seleccionado con el optimizador de consultas, en el menú Consulta de SQL Server Management Studio, seleccione Mostrar plan de ejecución estimado antes de ejecutar la consulta.

USE AdventureWorks2012;
GO
IF EXISTS (SELECT name FROM sys.indexes
            WHERE name = N'IX_Address_PostalCode')
    DROP INDEX IX_Address_PostalCode ON Person.Address;
GO
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

H.Crear un índice con particiones

El ejemplo siguiente crea un índice no agrupado con particiones en TransactionsPS1, un esquema de partición existente. En este ejemplo se supone que se ha instalado el ejemplo de índice con particiones.

USE AdentureWorks2012;
GO
IF EXISTS (SELECT name FROM sys.indexes
    WHERE name = N'IX_TransactionHistory_ReferenceOrderID'
    AND object_id = OBJECT_ID(N'Production.TransactionHistory'))
DROP INDEX IX_TransactionHistory_ReferenceOrderID
    ON Production.TransactionHistory;
GO
CREATE NONCLUSTERED INDEX IX_TransactionHistory_ReferenceOrderID
    ON Production.TransactionHistory (ReferenceOrderID)
    ON TransactionsPS1 (TransactionDate);
GO

I.Crear un índice filtrado

En el ejemplo siguiente se crea un índice filtrado en la tabla Production.BillOfMaterials. El predicado de filtro puede incluir columnas que no son columnas de clave en el índice filtrado. El predicado de este ejemplo selecciona solo las filas en que EndDate no es NULL.

USE AdventureWorks2012;
GO
IF EXISTS (SELECT name FROM sys.indexes
    WHERE name = N'FIBillOfMaterialsWithEndDate' 
    AND object_id = OBJECT_ID(N'Production.BillOfMaterials'))
DROP INDEX FIBillOfMaterialsWithEndDate
    ON Production.BillOfMaterials;
GO
CREATE NONCLUSTERED INDEX "FIBillOfMaterialsWithEndDate"
    ON Production.BillOfMaterials (ComponentID, StartDate)
    WHERE EndDate IS NOT NULL;
GO

J.Crear un índice comprimido

En el ejemplo siguiente se crea un índice en una tabla sin particiones utilizando la compresión de fila.

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.

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.

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

Vea también

Referencia

ALTER INDEX (Transact-SQL)

CREATE PARTITION FUNCTION (Transact-SQL)

CREATE PARTITION SCHEME (Transact-SQL)

CREATE SPATIAL INDEX (Transact-SQL)

CREATE STATISTICS (Transact-SQL)

CREATE TABLE (Transact-SQL)

CREATE XML INDEX (Transact-SQL)

Tipos de datos (Transact-SQL)

DBCC SHOW_STATISTICS (Transact-SQL)

DROP INDEX (Transact-SQL)

sys.indexes (Transact-SQL)

sys.index_columns (Transact-SQL)

sys.xml_indexes (Transact-SQL)

EVENTDATA (Transact-SQL)

Conceptos

Índices XML (SQL Server)