Compartir a través de

  • Artículo

CREATE INDEX (Transact-SQL)

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 más 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).

Icono de vínculo a temasConvenciones de sintaxis de Transact-SQL

Sintaxis

Create Relational Index 
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. Para obtener más información, vea Estructuras de ndices clúster.

    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 Diseñar 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. Para obtener más información, vea Determinar requisitos de espacio en disco del índice.

  • 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. Para obtener más información, vea Estructuras de índices no agrupados.

    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 Índice 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 Crear í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, una columna UDT, una columna de tipo de datos espacial o 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.

    Los siguientes son algunos ejemplos de predicados de filtro para la tabla Production.BillOfMaterials:

    WHERE StartDate > '20040101' AND EndDate <= '20040630'

    WHERE ComponentID IN (533, 324, 753)

    WHERE StartDate IN ('20040404', '20040905') 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 en la base de datos al ejecutar CREATE PARTITION SCHEME o ALTER PARTITION SCHEME. column_name especifica la columna en la que se crearán particiones del í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 utiliza partition_scheme_name. column_name no se restringe a las columnas en la definición de í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 utiliza el mismo esquema de partición que utiliza la tabla. Para obtener información acerca de cómo crear un índice XML, vea CREATE XML INDEX (Transact-SQL).

    Para obtener más información acerca de los índices de partición, vea Directrices especiales para índices con particiones.

  • ON filegroup_name
    Crea el índice especificado en el grupo de archivos especificado. 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 una lista de temas sobre FILESTREAM, vea Diseñar e implementar almacenamiento FILESTREAM.

<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 Motor de base de datos el nivel hoja de cada página de índice cuando se crea o se vuelve a generar un índice. fillfactor debe ser un valor entero entre 1 y 100. Si el valor de fillfactor es 100, el Motor de base de datos crea índices con las páginas hoja llenas al máximo de su capacidad.

    La configuración de FILLFACTOR solo se aplica cuando se crea o se vuelve a generar 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 Factor de relleno.

  • 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 tempdb y la creación de í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 e í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 únicamente están disponibles en las ediciones Enterprise, Developer y Evaluation de SQL Server.

    • 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, tales como las instrucciones SELECT.

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

    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 agrupado único inicial en una vista.

    • Índices clúster deshabilitados.

    • Índice clúster si la tabla subyacente contiene tipos de datos LOB: image, ntext, text, varchar(max), nvarchar(max), varbinary(max) y xml.

    • Índice no clúster definido con columnas de tipo de datos LOB.

      Nota

      Se puede crear un índice no clúster no único en línea si la tabla contiene tipos de datos LOB, pero ninguna de estas columnas se utiliza en la definición de índice como columna con clave o columna sin clave (incluida).

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

  • ALLOW_ROW_LOCKS = { ON | OFF }
    Especifica si se permiten 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. 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 grado máximo de paralelismo durante la operación de índice. Utilice MAXDOP para establecer un límite para el número de procesadores utilizados en la ejecución de un plan paralelo. El máximo es 64 procesadores.

    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)
      Utiliza el número real de procesadores, o un número inferior, en función de la carga de trabajo actual del sistema.

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

    Nota

    Las operaciones de índices en paralelo únicamente están disponibles en las ediciones Enterprise, Developer y Evaluation de SQL Server.

  • 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 Crear tablas e índices comprimidos.

  • 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 que ejecutan SQL Server 2005 Enterprise Edition o SQL Server 2008, CREATE INDEX puede utilizar más procesadores para realizar operaciones de examen y ordenación asociadas con 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. Para obtener más información, vea Elegir un modelo de recuperación para las operaciones de índice.

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. Para obtener más información, vea Usar propiedades extendidas en objetos de base de datos.

Í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, vea Determinar requisitos de espacio en disco del índice. 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. Para obtener más información acerca de los índices únicos, vea Crear índices únicos.

Í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 las tablas subyacentes, y en una tabla con particiones del mismo esquema de partición que utiliza las mismas columnas de partición.

Cuando se crean particiones en un índice clúster no único, Motor de base de datos agrega, de forma predeterminada, las columnas de partición en 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.

Vistas indizadas

La creación de un índice clúster único en una vista mejora el rendimiento de la consulta porque la vista se almacena en la base de datos de la misma manera que se almacena una tabla con un índice clúster. El optimizador de consultas puede utilizar vistas indizadas para acelerar la ejecución de las consultas. No es necesario hacer referencia a la vista en la consulta para que el optimizador tenga en cuenta esa vista al hacer una sustitución.

Para crear una vista indizada, es necesario seguir los pasos descritos a continuación que son fundamentales para la correcta implementación de la vista:

  1. Compruebe que las opciones SET sean correctas para todas las tablas existentes a las que se hará referencia en la vista.

  2. Compruebe que las opciones SET de la sesión estén establecidas correctamente antes de crear cualquier tabla nueva y la vista.

  3. Compruebe que la definición de vista sea determinista.

  4. Cree la vista mediante la opción WITH SCHEMABINDING.

  5. Cree el índice clúster único en la vista.

Opciones SET requeridas para vistas indizadas

La evaluación de la misma expresión puede producir resultados diferentes en Motor de base de datos si hay diferentes opciones SET activas cuando se ejecuta la consulta. Por ejemplo, después de establecer la opción SET CONCAT_NULL_YIELDS_NULL en ON, la expresión 'abc' + NULL devuelve el valor NULL. Sin embargo, después de establecer CONCAT_NULL_YIEDS_NULL en OFF, la misma expresión produce 'abc'.

Para asegurar el correcto mantenimiento de las vistas y la generación de resultados coherentes, las vistas indizadas requieren valores fijos para varias opciones SET. Las opciones SET de la tabla siguiente se deben establecer según los valores mostrados en la columna Valorobligatorio cuando se producen las siguientes condiciones:

  • Se crea la vista indizada.

  • Se realiza una operación de inserción, actualización o eliminación en cualquier tabla que participa en la vista indizada. Esto incluye operaciones como copia masiva, replicación y consultas distribuidas.

  • El optimizador de consultas utiliza la vista indizada para producir el plan de consulta.

    Opciones SET

    Valor requerido

    Valor de servidor predeterminado

    Valor predeterminado

    Valor OLE DB y ODBC

    Valor predeterminado

    Valor de DB-Library

    ANSI_NULLS

    ON

    ON

    ON

    OFF

    ANSI_PADDING

    ON

    ON

    ON

    OFF

    ANSI_WARNINGS*

    ON

    ON

    ON

    OFF

    ARITHABORT

    ON

    ON

    OFF

    OFF

    CONCAT_NULL_YIELDS_NULL

    ON

    ON

    ON

    OFF

    NUMERIC_ROUNDABORT

    OFF

    OFF

    OFF

    OFF

    QUOTED_IDENTIFIER

    ON

    ON

    ON

    OFF

    *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 utiliza una conexión de servidor OLE DB u ODBC, el único valor que se debe modificar es la configuración de ARITHABORT. Todos los valores de DB-Library se deben establecer correctamente en el nivel de servidor mediante sp_configure o desde la aplicación utilizando el comando SET. Para obtener más información sobre opciones SET, vea Usar las opciones de SQL Server.

Nota importanteImportante

Se recomienda que la opción de usuario ARITHABORT se establezca en todo el servidor como ON tan pronto como se cree la primera vista indizada o índice en una columna calculada en cualquier base de datos del servidor.

Funciones deterministas

La definición de una vista indizada debe ser determinista. Una vista es determinista si todas las expresiones de la lista de selección y las cláusulas WHERE y GROUP BY son deterministas. Las expresiones deterministas siempre devuelven el mismo resultado cada vez que son evaluadas con un conjunto específico de valores de entrada. Solo las funciones deterministas pueden participar en expresiones deterministas. Por ejemplo, la función DATEADD es determinista porque siempre devuelve el mismo resultado para cualquier conjunto dado de valores de argumento para sus tres parámetros. GETDATE no es determinista porque siempre se invoca con el mismo argumento, pero el valor que devuelve varía cada vez que se ejecuta. Para obtener más información, vea Funciones deterministas y no deterministas.

Aun cuando una expresión sea determinista, si contiene expresiones de tipo float, es posible que un resultado exacto dependa de la arquitectura de procesador o de la versión de microcódigo. Para asegurar la integridad de los datos, estas expresiones solo pueden participar como columnas que no son de clave de vistas indizadas. Las expresiones deterministas que no contienen expresiones flotantes se denominan expresiones precisas. Solo las expresiones deterministas precisas pueden participar en columnas de clave y en cláusulas WHERE o GROUP BY de vistas indizadas.

Utilice la propiedad IsDeterministic de la función COLUMNPROPERTY para determinar si una columna de la vista es determinista. Utilice la propiedad IsPrecise de la función COLUMNPROPERTY para determinar si una columna determinista de una vista con enlaces de esquema es precisa. COLUMNPROPERTY devuelve 1 si el valor es TRUE, 0 si es FALSE y NULL en entradas no válidas. Esto significa que la columna no es determinista ni precisa.

Requisitos adicionales

Además de los requisitos de opciones SET y funciones deterministas, se debe cumplir con los requisitos siguientes:

  • El usuario que ejecuta CREATE INDEX debe ser el propietario de la vista.

  • Si la definición de vista contiene una cláusula GROUP BY, la clave del índice clúster único solo puede hacer referencia a las columnas especificadas en esta cláusula.

  • Las tablas base deben tener las opciones SET correctas establecidas en el momento de la creación de la tabla; en caso contrario, la vista con enlaces de esquema no podrá hacer referencia a ésta.

  • En la definición de vista, los nombres compuestos de dos partes, schema**.**tablename, deben hacer referencia a las tablas.

  • Se deben crear funciones definidas por el usuario utilizando la opción WITH SCHEMABINDING.

  • Los nombres compuestos de dos partes, schema**.**function, deben hacer referencia a las funciones definidas por el usuario.

  • Esta vista se debe crear utilizando la opción WITH SCHEMABINDING.

  • La vista solo debe hacer referencia a tablas base, y no a otras vistas.

  • La definición de vista no debe contener lo siguiente:

    COUNT(*)

    Función ROWSET

    Tabla derivada

    Autocombinación

    DISTINCT

    STDEV, VARIANCE, AVG

    Columnas float*, text, ntext o image

    Subconsulta

    Predicados de texto completo (CONTAIN, FREETEXT)

    SUM en una expresión que admite el valor NULL

    Función de agregado definida por el usuario CLR

    TOP

    MIN, MAX

    UNION

    *La vista indizada puede contener columnas float; sin embargo, estas columnas no se pueden incluir en la clave de índice clúster.

Si GROUP BY está presente, la definición de VIEW debe contener COUNT_BIG(*) y no debe contener HAVING. Estas restricciones GROUP BY solo se pueden aplicar a la definición de vista indizada. Una consulta puede utilizar una vista indizada en su plan de ejecución aun cuando no satisfaga estas restricciones GROUP BY.

En una tabla con particiones se pueden crear vistas indizadas, en las que a su vez se pueden crear particiones. Para obtener más información sobre particiones, vea la sección anterior "Índices con particiones".

Para evitar que el Motor de base de datos utilice vistas indizadas, incluya la sugerencia OPTION (EXPAND VIEWS) en la consulta. Además, si alguna de las opciones enumeradas no está establecida correctamente, el optimizador no utilizará los índices en las vistas. Para obtener más información sobre la sugerencia OPTION (EXPAND VIEWS), vea SELECT (Transact-SQL).

El nivel de compatibilidad de la base de datos no puede ser menor que 80. Una base de datos que contenga una vista indizada no se puede cambiar a un nivel de compatibilidad inferior al 80.

Í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 Directrices generales para diseñar índices filtrados.

Índices espaciales

Para obtener información sobre índices espaciales, vea CREATE SPATIAL INDEX (Transact-SQL) y Trabajar con índices espaciales (motor de base de datos).

Índices XML

Para obtener información acerca de los índices XML, vea CREATE XML INDEX (Transact-SQL) y Índices en columnas del tipo de datos XML.

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. Para obtener más información, vea Tamaño máximo de las claves de índices. La clave de índice de un índice clúster no puede contener columnas varchar que tengan datos existentes en la unidad de asignación ROW_OVERFLOW_DATA. Si un índice clúster se crea en una columna varchar y los datos existentes se encuentran en la unidad de asignación IN_ROW_DATA, las acciones de inserción o actualización siguientes en la columna que provocarán el desbordamiento de los datos de la fila no serán correctas. Para obtener más información acerca de las unidades de asignación, vea Organización de tablas e índices.

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 Índice 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 Crear í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 Índice 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 Directrices especiales para índices con particiones.

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 vuelven a generar índices con 128 o más extensiones, 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. Para obtener más información, vea Quitar y volver a generar objetos grandes.

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. Para obtener más información, vea Determinar requisitos de espacio en disco del índice.

  • 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 índices 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 desde un bloqueo de fila o página a un bloqueo de tabla. Para obtener más información, vea Concentración de bloqueos (motor de base de datos).

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.

Para obtener más información sobre la configuración de la granularidad del bloqueo de un índice, vea Personalizar el bloqueo de un í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. Para obtener más información, vea Ver información de índice.

Compresión de datos

La compresión de datos se describe en el tema Crear tablas e índices comprimidos. 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> ... REBUILD PARTITION ... vuelve a generar la partición especificada del índice.

  • La sintaxis ALTER INDEX <index> ... 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 BusinessEntityID de la tabla Purchasing.ProductVendor.

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

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

A. 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 AdventureWorks2008R2;
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 al intentar 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 AdventureWorks2008R2;
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 AdventureWorks2008R2;
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 AdventureWorks2008R2;
GO
CREATE NONCLUSTERED INDEX IX_WorkOrder_ProductID
    ON Production.WorkOrder(ProductID)
    WITH (FILLFACTOR = 80,
        PAD_INDEX = ON,
        DROP_EXISTING = ON);
GO

G. 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 AdventureWorks2008R2;
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 (sin clave) incluidas

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