Compartir a través de

CREATE INDEX (Transact-SQL)

  • Artículo

Actualizado: 14 de abril de 2006

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

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 ] ) ]
    [ WITH ( <relational_index_option> [ ,...n ] ) ]
    [ ON { partition_scheme_name ( column_name ) 
         | filegroup_name 
         | default 
         }
    ]
[ ; ]

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

Create XML Index 
CREATE [ PRIMARY ] XML INDEX index_name 
    ON <object> ( xml_column_name )
    [ USING XML INDEX xml_index_name 
        [ FOR { VALUE | PATH | PROPERTY } ] ]
    [ WITH ( <xml_index_option> [ ,...n ] ) ]
[ ; ]

<object> ::=
{
    [ database_name. [ schema_name ] . | schema_name. ] 
        table_name
}

<xml_index_option> ::=
{ 
    PAD_INDEX  = { ON | OFF }
  | FILLFACTOR = fillfactor
  | SORT_IN_TEMPDB = { ON | OFF }
  | STATISTICS_NORECOMPUTE = { ON | OFF }
  | DROP_EXISTING = { ON | OFF }
  | ALLOW_ROW_LOCKS = { ON | OFF }
  | ALLOW_PAGE_LOCKS = { ON | OFF }
  | MAXDOP = max_degree_of_parallelism
}

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 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 agrupado de una vista debe ser único.

    SQL Server 2005 Database Engine (Motor de base de datos de SQL Server 2005) 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 realizarla, el Database Engine (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 nulos 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 agrupado contiene las filas de datos en sí de la tabla. Una tabla o vista permite un índice agrupado al mismo tiempo. Para obtener más información, vea Estructuras de índices agrupados.

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

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

    Si no se especifica CLUSTERED, se crea un índice no agrupado.

    [!NOTA] Debido a que el nivel hoja de un índice agrupado y sus páginas de datos son, por definición, lo mismo, la creación de un índice agrupado 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 qué grupos de archivos están disponibles y que esos grupos de archivos tengan suficiente espacio libre 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 agrupado, 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 249 índices agrupados, 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, sólo se pueden crear índices no agrupados en una vista que ya tenga definido un índice agrupado ú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.

    Los nombres de índices XML principales no pueden comenzar por los siguientes caracteres: #, ##, @ o @@.

  • 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. Para obtener más información sobre las columnas de tipo variable en índices compuestos, vea la sección Notas.

    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 sobre la indización de columnas de tipo definido por el usuario CLR, vea Tipos definidos por el usuario CLR (en inglés).

  • [ 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 sin clave que se agregarán en el nivel hoja del índice no agrupado. El índice no agrupado puede ser único o no único.

    El número máximo de columnas sin clave incluidas es 1.023 columnas; el mínimo, 1 columna.

    Los nombres de columna no se pueden repetir en la lista INCLUDE y no se pueden utilizar simultáneamente como columnas con y sin clave. 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 volver a generar sin conexión (ONLINE = OFF) si el tipo de datos de alguna de las columnas sin 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 sin 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.

  • 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 el Database Engine (Motor de base de datos) compruebe la unicidad de valores de clave en una única partición solamente.

    [!NOTA] Cuando se crean particiones en un índice agrupado no único, el Database Engine (Motor de base de datos) agrega, de manera predeterminada, la columna de partición a la lista de claves del índice agrupado, si aún no se especificó. Cuando se crean particiones en un índice no agrupado ni único, el Database Engine (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.

    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 más información sobre í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 como la tabla o vista subyacente. El grupo de archivos debe existir. Los índices XML utilizan el mismo grupo de archivos que utiliza la tabla.

  • 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 establecerse en ON en la sesión actual. Ésta es la configuración predeterminada. Para obtener más información, vea SET QUOTED_IDENTIFIER (Transact-SQL).

  • [PRIMARY] XML
    Crea un índice XML en la columna xml especificada. Cuando se especifica PRIMARY, se crea un índice agrupado con la clave agrupada formada a partir de la clave de agrupación de la tabla de usuario y un identificador del nodo XML. Cada tabla puede tener hasta 249 índices XML. Observe lo siguiente cuando cree un índice XML:

    • Debe existir un índice agrupado en la clave principal de la tabla de usuario.
    • La clave de agrupación de la tabla de usuario tiene un límite de 15 columnas.
    • Cada columna xml de una tabla puede tener un índice XML principal y varios índices XML secundarios.
    • Debe existir un índice XML principal en una columna xml para poder crear un índice XML secundario en la columna.
    • Sólo se puede crear un índice XML en una columna xml única. No puede crear un índice XML en una columna que no sea xml, como tampoco puede crear un índice relacional en una columna xml.
    • No puede crear un índice XML, ya sea principal o secundario, en una columna xml en una vista, en una variable con valores de tabla con columnas xml o en variables de tipo xml.
    • No puede crear un índice XML principal en una columna xml calculada.
    • La configuración de la opción SET debe ser la misma que la requerida para vistas indizadas e índices de columnas calculadas. Específicamente, se debe establecer la opción ARITHABORT en ON cuando se crea un índice XML y cuando se insertan, eliminan o actualizan valores en la columna xml. Para obtener más información, vea Opciones SET que afectan a los resultados.

    Para obtener más información, vea Índices en columnas de tipo de datos xml.

  • xml_column_name
    Es la columna xml en la que se basa el índice. Sólo se puede especificar una columna xml en una única definición de índice XML; sin embargo, se pueden crear varios índices XML secundarios en una columna xml.
  • USING XML INDEX xml_index_name
    Especifica el índice XML principal que se utilizará en la creación de un índice XML secundario.

  • FOR { VALUE | PATH | PROPERTY }
    Especifica el tipo de índice XML secundario.

    • VALUE
      Crea un índice XML secundario en las columnas en las que se encuentran las columnas de clave (ruta y valor del nodo) del índice XML principal.
    • PATH
      Crea un índice XML secundario en las columnas generadas a partir de valores de ruta y de nodo del índice XML principal. En el índice secundario PATH, los valores de ruta y de nodo son las columnas de clave que permiten exploraciones eficaces en la búsqueda de rutas.
    • PROPERTY
      Crea un índice XML secundario en las columnas (PK, y valor de ruta y de nodo) del índice XML principal, donde PK es la clave principal de la tabla base.
<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 agrupado único en una vista antes de crear los índices no agrupados. Para obtener más información sobre vistas indizadas, vea la sección Notas.

<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 libre especificado por fillfactor se aplica a 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 sólo es útil cuando se especifica también 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, el Database Engine (Motor de base de datos) anula 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 Database Engine (Motor de base de datos) el nivel hoja de cada página de índice durante la creación o nueva generación del índice. fillfactor debe ser un valor entero del 1 al 100. El valor predeterminado es 0. Si el valor de fillfactor es 100 ó 0, el Database Engine (Motor de base de datos) crea índices con páginas hoja rellenas al máximo.

    [!NOTA] Los valores de fill factor 0 y 100 son idénticos.

    La configuración de FILLFACTOR sólo se aplica cuando se crea o se vuelve a generar el índice. El Database Engine (Motor de base de datos) no mantiene dinámicamente el porcentaje especificado de espacio libre de las páginas. Para ver la configuración de fill factor, utilice la vista de catálogo sys.indexes.

    ms188783.note(es-es,SQL.90).gifImportante:
    La creación de un índice agrupado con un valor de FILLFACTOR menor que 100 afecta a la cantidad de espacio de almacenamiento que ocupan los datos, porque el Database Engine (Motor de base de datos) vuelve a distribuir los datos cuando crea el índice agrupado.

    Para obtener más información, vea Factor de relleno.

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

    • ON
      Los resultados de orden intermedio utilizados para generar el índice se almacenan en tempdb. Esto puede reducir el tiempo requerido para crear un índice si tempdb se encuentra en un conjunto de discos diferente de la base de datos de usuario. Sin embargo, se incrementa la cantidad de espacio en disco que se utiliza en la generación del índice.
    • OFF
      Los resultados de orden intermedio 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 para valores de clave duplicados en una operación de inserción de varias filas en un índice agrupado o no agrupado único. El valor predeterminado es OFF.

    • ON
      Se emite un mensaje de advertencia y sólo causarán un error las filas que infrinjan el índice único.
    • OFF
      Se emite un mensaje de error y se revierte la transacción INSERT completa.

    La configuración de IGNORE_DUP_KEY se aplica sólo a operaciones de inserción realizadas tras la creación o regeneración del índice. La configuración no tiene ningún efecto durante la operación de creación del índice.

    IGNORE_DUP_KEY no se puede establecer en ON para índices XML e índices creados en una vista.

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

    ms188783.note(es-es,SQL.90).gifImportante:
    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 agrupado o no agrupado preexistente mencionado, o el índice XML, debe quitarse y volverse a generar. El valor predeterminado es OFF.

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

    El tipo de índice, relacional o XML, no se puede cambiar con DROP_EXISTING. Asimismo, un índice XML principal no se puede volver a definir como un índice XML secundario, o viceversa.

    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 consultas y modificación de datos durante la operación de indización. El valor predeterminado es OFF.

    [!NOTA] Las operaciones de índice en línea sólo están disponibles en SQL Server 2005 Enterprise Edition.

    • 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, sólo se mantiene un bloqueo de intención compartida en la tabla de origen. Esto permite que no se interrumpan las consultas o actualizaciones de la tabla subyacente. En el inicio de la operación, se mantiene un bloqueo compartido en el objeto de origen por un breve período de tiempo. Al final de la operación, por un breve período de tiempo, se adquiere un bloqueo compartido (S) en el origen si se crea un índice no agrupado; o se adquiere un bloqueo de modificación del esquema (SCH-M) cuando se crea o quita un índice agrupado en línea y cuando se vuelve a generar un índice agrupado o no agrupado. 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 que crea, vuelve a generar o quita un índice agrupado, o vuelve a generar o quita un índice no agrupado, adquiere un bloqueo de modificación del esquema (Sch-M) en 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 agrupado 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 sobre 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 agrupados deshabilitados.

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

    • Índice no agrupado definido con columnas de tipo de datos LOB.

      [!NOTA] Se puede crear un índice no agrupado 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
      Se permiten bloqueos de fila en el acceso al índice. El Database Engine (Motor de base de datos) determina el momento en que se utilizan bloqueos de fila.
    • OFF
      No se utilizan bloqueos de fila.

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

    • ON
      Se permiten bloqueos de página en el acceso al índice. El Database Engine (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
    Anula la opción de configuración max degree of parallelism 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 valor máximo es de 64 procesadores.

    max_degree_of_parallelism puede ser:

    • 1
      Suprime la generación del plan paralelo.
    • >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, o un número inferior, de procesadores en función de la actual carga de trabajo del sistema.

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

    [!NOTA] Las operaciones de índices paralelos sólo están disponibles en SQL Server 2005 Enterprise Edition.

Notas

La instrucción CREATE INDEX se optimiza como cualquier otra consulta. Para guardar en operaciones de E/S, el procesador de consultas puede elegir recorrer otro índice en lugar de recorrer una tabla. La operación de orden se puede eliminar en algunos casos. En equipos con varios procesadores con SQL Server 2005 Enterprise Edition, CREATE INDEX puede utilizar más procesadores para realizar operaciones de recorrido y ordenación asociadas con la creación del índice, al igual que lo 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 termina 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 agrupados

La creación de un índice agrupado en una tabla (montón) o la eliminación y nueva creación de un índice agrupado 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 agrupado existente. Para obtener más información, vea Determinar requisitos de espacio en disco del índice. Para obtener más información sobre índices agrupados, vea Crear índices agrupados.

Índices únicos

Cuando existe un índice único, el Database Engine (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 Database Engine (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, sólo causarán un error las filas que infrinjan el índice UNIQUE. Para obtener más información sobre í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 no tiene particiones, y puede tener un índice sin particiones en una tabla que tiene 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 agrupado no único, el Database Engine (Motor de base de datos) agrega, de manera predeterminada, las columnas de partición en la lista de claves del índice agrupado, si aún no se especificaron.

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 sobre índices con particiones, vea Tablas e índices con particiones.

Vistas indizadas

La creación de un índice agrupado ú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 agrupado. 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 agrupado único en la vista.

Opciones SET requeridas para vistas indizadas

La evaluación de la misma expresión puede producir resultados diferentes en el Database Engine (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 garantizar 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, réplica y consultas distribuidas.

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

    Opciones SET Valor obligatorio 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

    *En SQL Server 2005, si se establece ANSI_WARNINGS en ON, se establece implícitamente ARITHABORT en ON cuando el nivel de compatibilidad de la base de datos está establecido en 90. Si el nivel de compatibilidad de la base de datos está establecido en 80 o en un nivel anterior, deberá establecerse 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 a través del comando SET. Para obtener más información sobre opciones SET, vea Usar las opciones de SQL Server.

ms188783.note(es-es,SQL.90).gifImportante:
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. Sólo 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 flotantes, es posible que un resultado exacto dependa de la arquitectura de procesador o la versión de microcódigo. Para garantizar la integridad de los datos, estas expresiones sólo pueden participar como columnas sin clave de vistas indizadas. Las expresiones deterministas que no contienen expresiones flotantes se denominan expresiones precisas. Sólo 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 agrupado único sólo 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 sólo 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 (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 agrupado.

Si GROUP BY está presente, la definición de VIEW debe contener COUNT_BIG(*) y no debe contener HAVING. Estas restricciones GROUP BY sólo 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 Database Engine (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á correctamente establecida, 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 inferior a 80. Una base de datos que contiene una vista indizada no puede cambiarse a un nivel de compatibilidad inferior a 80.

Índices XML

Para obtener más información, vea Índices en columnas de 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 los índices de las columnas varchar que exceden los 900 bytes si los datos existentes en las columnas no exceden los 900 bytes al crearse el índice; sin embargo, se producirá un error en las posteriores acciones de inserción o actualización en las columnas que eleven el tamaño total por encima de los 900 bytes. Para obtener más información, vea Tamaño máximo de las claves de índices. La clave de un índice agrupado no puede contener columnas varchar que posean datos existentes en la unidad de asignación ROW_OVERFLOW_DATA. Si se crea un índice agrupado en una columna varchar y los datos existentes se encuentran en la unidad de asignación IN_ROW_DATA, se generará un error en las posteriores acciones de inserción o actualización en la columna que convirtieras los datos en no consecutivos. Para obtener más información sobre unidades de asignación, vea Organización de tablas e índices.

En SQL Server 2005, los índices no agrupados pueden incluir columnas sin clave en el nivel hoja del índice. El Database Engine (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.

Columnas calculadas

Los índices se pueden crear en columnas calculadas. En SQL Server 2005, las columnas calculadas pueden tener la propiedad PERSISTED. Esto significa que el Database Engine (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. El Database Engine (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 tipo definido por el usuario CLR que el usuario ha marcado como deterministas.
  • Las columnas calculadas basadas en expresiones que son deterministas, como se definen en el Database Engine (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 pueden ser indizadas como una columna de clave o sin clave incluida, siempre que el tipo de datos de la columna calculada esté disponible como columna de clave de índice o columna sin clave. Por ejemplo, no puede crear un índice XML principal en una columna xml calculada. Si el tamaño de clave de índice excede 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 sin clave, denominadas columnas incluidas, se pueden agregar en el nivel hoja de un índice no agrupado 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 con o sin clave. De este modo, el optimizador de consultas puede ubicar toda la información requerida de un recorrido de índice; no se tiene acceso a los datos de la tabla o del índice agrupado. Para obtener más información, vea Índice con columnas incluidas.

Especificar opciones de índice

SQL Server 2005 ofrece nuevas opciones de índice y 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:

  • Sólo 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 orden 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 agrupado (con el mismo conjunto de claves o con uno distinto) en una tabla que también tiene índices no agrupados. DROP_EXISTING reemplaza la ejecución de una instrucción DROP INDEX en el antiguo índice agrupado seguida de la ejecución de una instrucción CREATE INDEX para el nuevo índice agrupado. Los índices no agrupados 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 agrupados cuando la definición de índice posee los mismos nombres de índice, clave y columnas de partición, atributo de unicidad y criterio de orden que el índice original.

Independientemente de si se vuelven a generar o no los índices no agrupados, é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 agrupado se vuelve a generar en un esquema de partición o grupo de archivos diferente, los índices no agrupados no se mueven para coincidir con la nueva ubicación del índice agrupado. Por lo tanto, es posible que incluso los índices no agrupados previamente alineados con el índice agrupado 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 agrupado y la opción ONLINE se establezca en OFF. Si se deshabilita el índice agrupado, se debe establecer ONLINE en OFF para la operación CREATE INDEX WITH DROP_EXISTING. Si se deshabilita un índice no agrupado y no se asocia con un índice agrupado 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, el Database Engine (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. El Database Engine (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, sólo 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.

Permisos

Requiere el permiso ALTER en la tabla o la vista. El usuario debe ser miembro de la función fija de servidor sysadmin o de las funciones fijas de base de datos db_ddladmin y db_owner.

Ejemplos

A. Crear un índice no agrupado sencillo

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

USE AdventureWorks;
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 (VendorID); 
GO

A. Crear un índice compuesto no agrupado sencillo

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

USE AdventureWorks
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 agrupado único

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

USE AdventureWorks;
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 intencionalmente 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 AdventureWorks;
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 AdventureWorks;
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 aun cuando sólo 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 AdventureWorks;
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 AdventureWorks;
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 agrupado con una columna de clave (PostalCode) y cuatro columnas sin 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 AdventureWorks;
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 XML principal

El ejemplo siguiente crea un índice XML principal en la columna CatalogDescription de la tabla Production.ProductModel.

USE AdventureWorks;
GO
IF EXISTS (SELECT * FROM sys.indexes
            WHERE name = N'PXML_ProductModel_CatalogDescription')
    DROP INDEX PXML_ProductModel_CatalogDescription 
        ON Production.ProductModel;
GO
CREATE PRIMARY XML INDEX PXML_ProductModel_CatalogDescription
    ON Production.ProductModel (CatalogDescription);
GO

I. Crear un índice XML secundario

El ejemplo siguiente crea un índice XML secundario en la columna CatalogDescription de la tabla Production.ProductModel.

USE AdventureWorks;
GO
IF EXISTS (SELECT name FROM sys.indexes
            WHERE name = N'IXML_ProductModel_CatalogDescription_Path')
    DROP INDEX IXML_ProductModel_CatalogDescription_Path
        ON Production.ProductModel;
GO
CREATE XML INDEX IXML_ProductModel_CatalogDescription_Path 
    ON Production.ProductModel (CatalogDescription)
    USING XML INDEX PXML_ProductModel_CatalogDescription FOR PATH ;
GO

J. Crear un índice con particiones

El ejemplo siguiente crea un índice no agrupado con particiones en TransactionsPS1, un esquema de partición existente. Este ejemplo asume que se ha instalado el ejemplo de índice con particiones. Para obtener información sobre la instalación, vea Readme_PartitioningScript.

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

Vea también

Referencia

ALTER INDEX (Transact-SQL)
CREATE PARTITION FUNCTION (Transact-SQL)
CREATE PARTITION SCHEME (Transact-SQL)
CREATE STATISTICS (Transact-SQL)
CREATE TABLE (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)

Otros recursos

Determinar requisitos de espacio en disco del índice
Directrices generales para diseñar índices
Índices en columnas de tipo de datos xml
Arquitectura de tablas e índices

Ayuda e información

Obtener ayuda sobre SQL Server 2005

Historial de cambios

Versión Historial

14 de abril de 2006
Contenido actualizado:
  • Se han quitado índices no agrupados únicos de la lista de índices que no se pueden crear en línea. Este cambio se aplica a SQL Server 2005 con el Service Pack 1 y a versiones posteriores.
  • Se ha agregado una nota al pie a la tabla de opciones de SET acerca del efecto en la configuración ARITHABORT cuando se establece ANSI_WARNINGS en ON.

5 de diciembre de 2005
Contenido nuevo:
  • Se han agregado índices no agrupados únicos a la lista de índices que no se pueden crear en línea.