Sugerencias de tabla (Transact-SQL)

Actualizado: 12 de diciembre de 2006

Especifica que el optimizador de consultas utilice un recorrido de tabla, uno o varios índices, o un método de bloqueo con esta tabla o vista, y para esta instrucción SELECT, INSERT, UPDATE o DELETE. Aunque esta opción es posible, el optimizador de consultas suele poder elegir el mejor método de optimización sin que se especifiquen sugerencias.

Importante:
Como el optimizador de consultas de SQL Server 2005 selecciona normalmente el mejor plan de ejecución para las consultas, se recomienda que sólo los programadores y administradores de bases de datos experimentados utilicen estas sugerencias, incluida <table_hint>, como último recurso.

Se aplica a:

DELETE

INSERT

SELECT

UPDATE

Convenciones de sintaxis de Transact-SQL

Sintaxis

<table_hint> ::= [ NOEXPAND ] { 
    INDEX ( index_val [ ,...n ] )
  | FASTFIRSTROW 
  | HOLDLOCK 
  | NOLOCK 
  | NOWAIT
  | PAGLOCK 
  | READCOMMITTED 
  | READCOMMITTEDLOCK 
  | READPAST 
  | READUNCOMMITTED 
  | REPEATABLEREAD 
  | ROWLOCK 
  | SERIALIZABLE 
  | TABLOCK 
  | TABLOCKX 
  | UPDLOCK 
  | XLOCK 
} 

<table_hint_limited> ::=
{
    KEEPIDENTITY 
  | KEEPDEFAULTS 
  | FASTFIRSTROW 
  | HOLDLOCK 
  | IGNORE_CONSTRAINTS 
  | IGNORE_TRIGGERS 
  | NOWAIT
    | PAGLOCK 
  | READCOMMITTED 
  | READCOMMITTEDLOCK 
  | READPAST 
  | REPEATABLEREAD 
  | ROWLOCK 
  | SERIALIZABLE 
  | TABLOCK 
  | TABLOCKX 
  | UPDLOCK 
  | XLOCK 
} 

Argumentos

• NOEXPAND
  Especifica que las vistas indizadas no se expandan para obtener acceso a las tablas subyacentes cuando el optimizador de consultas procesa la consulta. El optimizador de consultas trata la vista como una tabla con un índice agrupado. NOEXPAND sólo se aplica a las vistas indizadas. Para obtener más información, vea la sección Notas.

• INDEX ( index_val [ ,... n ] )
  Especifica el nombre o Id. de los índices que el optimizador de consultas va a utilizar al procesar la instrucción. Sólo se puede especificar una sugerencia de índice por cada tabla.

  Si existe un índice agrupado, INDEX(0) exige un recorrido del índice agrupado e INDEX(1) exige un recorrido o una búsqueda del índice agrupado. Si no existe un índice agrupado, INDEX(0) exige un recorrido de la tabla e INDEX(1) se interpreta como error.

  La sintaxis alternativa INDEX = especifica una sugerencia de índice única. Esto sólo se admite para mantener la compatibilidad con versiones anteriores.

  Si se utilizan varios índices en una lista de sugerencias, los índices duplicados se omiten y el resto se utiliza para obtener las filas de la tabla. El orden de los índices de la sugerencia de índice es importante. Una sugerencia de varios índices obliga a hacer AND entre los índices y el optimizador de consultas aplica todas las condiciones posibles a cada uno de los índices a los que tiene acceso. Si la colección de los índices sugeridos no es suficiente, se hace una búsqueda después de que el SQL Server 2005 Database Engine (Motor de base de datos de SQL Server 2005) recupere todas las columnas indizadas.

  [!NOTA] Cuando se utiliza una sugerencia de índice que hace referencia a varios índices en la tabla de hechos de una combinación en estrella, el optimizador pasa por alto la sugerencia de índice y devuelve un mensaje de advertencia. Asimismo, no se admiten las operaciones OR de índices para una tabla con una sugerencia de índice especificada.

  El número máximo de índices de una sugerencia de tabla es 250 índices no agrupados.

• KEEPIDENTITY
  Sólo se aplica a una instrucción INSERT cuando se utiliza la opción BULK con OPENROWSET.

  Especifica que se debe utilizar uno o varios valores de identidad en el archivo de datos importado para la columna de identidad. Si no se especifica KEEPIDENTITY, los valores de identidad de esta columna se comprueban pero no se importan, y el optimizador de consultas asigna automáticamente valores únicos basados en los valores de inicialización y de incremento especificados durante la creación de la tabla.

  Importante:

  Si el archivo de datos no contiene valores para la columna de identidad de la tabla o vista, debe omitir la columna de identidad, a menos que sea la última columna de la tabla. Para obtener más información, vea Usar un archivo de formato para omitir un campo de datos. Si una columna de identidad se omite correctamente, el optimizador de consultas asigna automáticamente valores únicos para la columna de identidad en las filas importadas de la tabla.

  Para ver un ejemplo que utiliza esta sugerencia en una instrucción INSERT... SELECT * FROM OPENROWSET(BULK...), vea Mantener valores de identidad al importar datos de forma masiva.

  Para obtener información sobre cómo comprobar el valor de identidad de una tabla, vea DBCC CHECKIDENT (Transact-SQL).

• KEEPDEFAULTS
  Sólo se aplica a una instrucción INSERT cuando se utiliza la opción BULK con OPENROWSET.

  Especifica que se inserte el valor predeterminado de la columna de una tabla, si existe, en lugar de NULL, cuando falta el valor de los datos de esa columna.

  Para ver un ejemplo que utiliza esta sugerencia en una instrucción INSERT... SELECT * FROM OPENROWSET(BULK...), vea Mantener valores NULL o utilizar valores predeterminados durante la importación masiva.

• FASTFIRSTROW
  Equivalente a OPTION (FAST 1). Para obtener más información, vea FAST en la cláusula OPTION de SELECT.

• HOLDLOCK
  Equivalente a SERIALIZABLE. Para obtener más información, vea SERIALIZABLE más adelante en este tema. HOLDLOCK sólo se aplica a la tabla o vista para la que se especificó, y sólo durante la transacción definida por la instrucción en la que se utiliza. HOLDLOCK no se puede utilizar en una instrucción SELECT que incluya la opción FOR BROWSE.

• IGNORE_CONSTRAINTS
  Sólo se aplica a una instrucción INSERT cuando se utiliza la opción BULK con OPENROWSET.

  Especifica que la operación de importación masiva pase por alto las restricciones de la tabla. De forma predeterminada, INSERT comprueba las restricciones CHECK y FOREIGN KEY. Cuando IGNORE_CONSTRAINTS se especifica para una operación de importación masiva, INSERT debe pasar por alto estas restricciones en una tabla de destino. Observe que no puede deshabilitar las restricciones UNIQUE, PRIMARY KEY o NOT NULL.

  Una situación en la que puede ser conveniente deshabilitar las instrucciones CHECK y FOREING KEY es cuando los datos de entrada contienen filas que infringen las restricciones. Si se deshabilitan las restricciones CHECK y FOREIGN KEY, se pueden importar los datos y, a continuación, utilizar instrucciones Transact-SQL para limpiarlos.

  Sin embargo, tenga en cuenta que cuando se pasan por alto las restricciones CHECK y FOREIGN KEY, después de la operación de cada restricción pasada por alto en la tabla, se marca como is_not_trusted en la vista de catálogo sys.check_constraints o sys.foreign_keys. En algún momento, tendrá que comprobar las restricciones en la tabla completa. Si la tabla no estaba vacía antes de la operación de importación masiva, el costo de volver a validar la restricción puede ser mayor que el costo de aplicar las restricciones CHECK o FOREIGN KEY a los datos incrementales.

• IGNORE_TRIGGERS
  Sólo se aplica a una instrucción INSERT cuando se utiliza la opción BULK con OPENROWSET.

  Especifica que la operación de importación masiva pase por alto todos los desencadenadores definidos en la tabla. De manera predeterminada, INSERT aplica desencadenadores.

  Use IGNORE_TRIGGERS sólo si su aplicación no depende de ningún desencadenador y es importante maximizar el rendimiento.

• NOLOCK
  Equivalente a READUNCOMMITTED. Para obtener más información, vea READUNCOMMITTED más adelante en este mismo tema.

• NOWAIT
  Indica al SQL Server 2005 Database Engine (Motor de base de datos de SQL Server 2005) que devuelva un mensaje cuando encuentre un bloqueo en la tabla. NOWAIT equivale a especificar SET LOCK_TIMEOUT 0 para una tabla específica.

• PAGLOCK
  Aplica bloqueos de página en los casos en que se suelen aplicar bloqueos individuales en filas o claves, o bien en los casos en los que se suele aplicar un único bloqueo de tabla. De manera predeterminada, utiliza el modo de bloqueo apropiado para la operación. Cuando se especifica en transacciones que funcionan con el nivel de aislamiento SNAPSHOT, no se utilizan bloqueos de página a menos que se combine PAGLOCK con otras sugerencias de tabla que requieran bloqueos, como UPDLOCK y HOLDLOCK.

• READCOMMITTED
  Especifica que las operaciones de lectura cumplan con las reglas para el nivel de aislamiento READ COMMITTED, a través del uso de bloqueos o versiones de filas. Si la opción de base de datos READ_COMMITTED_SNAPSHOT está establecida en OFF, el Database Engine (Motor de base de datos) adquiere los bloqueos compartidos a medida que se leen los datos y los libera cuando finaliza la operación de lectura. Si la opción de base de datos READ_COMMITTED_SNAPSHOT está establecida en ON, el Database Engine (Motor de base de datos) no adquiere bloqueos y utiliza las versiones de filas. Para obtener más información sobre los niveles de aislamiento, vea SET TRANSACTION ISOLATION LEVEL (Transact-SQL).

• READCOMMITTEDLOCK
  Especifica que las operaciones de lectura cumplan con las reglas para el nivel de aislamiento READ COMMITTED, a través del uso de bloqueos. El Database Engine (Motor de base de datos) adquiere bloqueos compartidos a medida que se leen los datos y los libera cuando finaliza la operación de lectura, independientemente de la configuración de la opción de base de datos READ_COMMITTED_SNAPSHOT. Para obtener más información sobre los niveles de aislamiento, vea SET TRANSACTION ISOLATION LEVEL (Transact-SQL).

• READPAST
  Especifica que Database Engine (Motor de base de datos) no lea las filas bloqueadas por otras transacciones. En la mayoría de las circunstancias se cumple lo mismo para las páginas. El Database Engine (Motor de base de datos) omite las filas o las páginas en lugar de bloquear la transacción actual hasta que se liberen los bloqueos. READPAST sólo se puede especificar en transacciones que funcionen con niveles de aislamiento READ COMMITTED o REPEATABLE READ. Cuando se especifica en transacciones que funcionan con el nivel de aislamiento SNAPSHOT, READPAST debe combinarse con otras sugerencias de tabla que requieran bloqueos, como UPDLOCK y HOLDLOCK. Cuando se especifica READPAST, se omiten los bloqueos tanto a nivel de fila como de página. READPAST se puede especificar para cualquier tabla a la que se haga referencia en una instrucción UPDATE o DELETE, o en una cláusula FROM. Cuando se especifica en una instrucción UPDATE, READPAST sólo se aplica al leer los datos para identificar los registros que se van a actualizar, independientemente de dónde se especifique en la instrucción. No se puede especificar READPAST para tablas en la cláusula INTO de una instrucción INSERT.

  Las operaciones de lectura que utilizan READPAST no se bloquean. Las operaciones de actualización o eliminación que utilizan READPAST se pueden bloquear al leer claves externas o vistas indizadas, o al modificar índices secundarios.

  Por ejemplo, suponga que la tabla T1 contiene una columna de tipo entero con los valores 1, 2, 3, 4, 5. Si la transacción A modifica el valor de 3 a 8 pero aún no ha sido confirmada, una instrucción SELECT * FROM T1 (READPAST) devuelve los valores 1, 2, 4, 5. READPAST se utiliza principalmente para reducir el conflicto de bloqueos al implementar una cola de trabajo que utilice una tabla de SQL Server. Un lector de cola que utilice READPAST omite las entradas de cola bloqueadas por otras transacciones y pasa a la siguiente entrada de cola disponible, sin tener que esperar a que las otras transacciones liberen los bloqueos.

• READUNCOMMITTED
  Especifica que se admiten lecturas no actualizadas. No se emiten bloqueos compartidos para impedir que otras transacciones modifiquen los datos leídos por la transacción actual, y los bloqueos exclusivos establecidos por otras transacciones no impiden que la transacción actual lea los datos bloqueados. La posibilidad de efectuar lecturas no actualizadas aumenta en gran medida la simultaneidad, pero a costa de leer modificaciones de datos que otras transacciones revierten más adelante. Esto puede generar errores en la transacción, o bien presentar a los usuarios datos no confirmados.

  Las sugerencias READUNCOMMITTED y NOLOCK sólo se aplican a bloqueos de datos. Todas las consultas, incluidas las que tienen sugerencias READUNCOMMITTED y NOLOCK, adquieren bloqueos Sch-S (estabilidad del esquema) durante la compilación y la ejecución. Por esta razón, las consultas se bloquean cuando una transacción simultánea mantiene un bloqueo Sch-M (modificación del esquema) en la tabla. Por ejemplo, una operación de lenguaje de definición de datos (DDL) adquiere un bloqueo Sch-M antes de modificar la información del esquema de la tabla. Las consultas simultáneas, incluidas las que se ejecutan con sugerencias READUNCOMMITTED o NOLOCK, se bloquean cuando intentan adquirir un bloqueo Sch-S. A la inversa, una consulta que mantiene un bloqueo Sch-S bloquea una transacción simultánea que intenta adquirir un bloqueo Sch-M. Para obtener más información acerca del comportamiento de bloqueo, vea Compatibilidad de bloqueos (motor de base de datos).

  No se pueden especificar READUNCOMMITTED, ni NOLOCK, en tablas modificadas por la inserción, actualización y eliminación de operaciones. El optimizador de consultas de SQL Server omite las sugerencias READUNCOMMITTED y NOLOCK de la cláusula FROM que se aplica a la tabla de destino de una instrucción UPDATE o DELETE.

  [!NOTA] En una versión futura de Microsoft SQL Server no se permitirá utilizar las sugerencias READUNCOMMITTED y NOLOCK en la cláusula FROM que se aplican a la tabla de destino de una instrucción UPDATE o DELETE. Evite usar estas sugerencias en este contexto en el nuevo trabajo de desarrollo y planee modificar las aplicaciones que las usan actualmente.

  En SQL Server 2005, también se puede reducir al mínimo la contención de bloqueos y, al mismo tiempo, proteger las transacciones de las lecturas no actualizadas de modificaciones de datos no confirmadas mediante una de estas dos alternativas:

  • El nivel de aislamiento READ COMMITTED con la opción de base de datos READ_COMMITTED_SNAPSHOT establecida en ON.
  • El nivel de aislamiento SNAPSHOT.

  Para obtener más información sobre los niveles de aislamiento, vea SET TRANSACTION ISOLATION LEVEL (Transact-SQL).

  [!NOTA] Si al especificar READUNCOMMITTED, recibe el mensaje de error 601, resuélvalo como si fueran errores de interbloqueo (1205) y vuelva a ejecutar la instrucción.

• REPEATABLEREAD
  Especifica que el recorrido se haga con la misma semántica de bloqueo que una transacción que se ejecute con el nivel de aislamiento REPEATABLE READ. Para obtener más información sobre los niveles de aislamiento, vea SET TRANSACTION ISOLATION LEVEL (Transact-SQL).

• ROWLOCK
  Especifica que se apliquen bloqueos de fila cuando normalmente se aplicarían bloqueos de página o de tabla. Cuando se especifica en transacciones que funcionan con el nivel de aislamiento SNAPSHOT, no se utilizan bloqueos de fila a menos que se combine ROWLOCK con otras sugerencias de tabla que requieran bloqueos, como UPDLOCK y HOLDLOCK.

• SERIALIZABLE
  Equivalente a HOLDLOCK. Hace que los bloqueos compartidos sean más restrictivos, manteniéndolos hasta la finalización de la transacción, en lugar de liberarlos cuando la tabla o página de datos deja de ser necesaria, se haya completado la transacción o no. El recorrido se hace con la misma semántica que una transacción que se ejecuta con el nivel de aislamiento SERIALIZABLE. Para obtener más información sobre los niveles de aislamiento, vea SET TRANSACTION ISOLATION LEVEL (Transact-SQL).

• TABLOCK
  Especifica que los bloqueos de tabla se apliquen hasta el final de la instrucción. Si se están leyendo datos, se aplica un bloqueo compartido. Si se están modificando datos, se aplica un bloqueo exclusivo. Si también se especifica HOLDLOCK, un bloqueo de tabla compartido se aplica hasta el final de la transacción.

  Cuando se utiliza con el proveedor de conjuntos de filas masivos OPENROWSET para importar datos en una tabla sin índices, TABLOCK permite que varios clientes carguen datos simultáneamente en la tabla de destino, con el bloqueo y el registro optimizados.

• TABLOCKX
  Especifica que se aplique un bloqueo exclusivo en la tabla hasta que se complete la transacción.

• UPDLOCK
  Especifica que se apliquen bloqueos de actualización y se mantengan hasta que finalice la transacción.

• XLOCK
  Especifica que se apliquen bloqueos exclusivos y se mantengan hasta que finalice la transacción. Si se especifica junto con ROWLOCK, PAGLOCK o TABLOCK, los bloqueos exclusivos se aplican al nivel de granularidad apropiado.

Notas

Las sugerencias de tabla se pasan por alto si el plan de consultas no tiene acceso a la tabla. Esto puede deberse a que el optimizador elija no tener acceso a la tabla o a que, en su lugar, se tenga acceso a una vista indizada. En el último caso, el acceso a una vista indizada puede evitarse con la sugerencia de consulta OPTION (EXPAND VIEWS).

Aunque es opcional, se recomienda el uso de comas entre las sugerencias de tabla. La separación de las sugerencias con espacios en lugar de comas se acepta por compatibilidad con versiones anteriores.

En SQL Server 2005, con algunas excepciones, las sugerencias de tabla sólo se admiten en la cláusula FROM cuando se especifican con la palabra clave WITH. Las sugerencias de tabla deben especificarse también con paréntesis.

Las sugerencias de tabla permitidas con y sin la palabra clave WITH son las siguientes: NOLOCK, READUNCOMMITTED, UPDLOCK, REPEATABLEREAD, SERIALIZABLE, READCOMMITTED, FASTFIRSTROW, TABLOCK, TABLOCKX, PAGLOCK, ROWLOCK, NOWAIT, READPAST, XLOCK y NOEXPAND. Cuando estas sugerencias de tabla se especifican sin la palabra clave WITH, deben especificarse solas. Por ejemplo:

FROM t (FASTFIRSTROW)

Cuando la sugerencia se especifica con otra opción, debe especificarse con la palabra clave WITH:

FROM t WITH (FASTFIRSTROW, INDEX(myindex))

Las restricciones se aplican cuando las sugerencias se utilizan en consultas de bases de datos con un nivel de compatibilidad de 90.

En SQL Server 2005, todas las sugerencias de bloqueo se propagan a todas las tablas y vistas a las que se hace referencia en una vista. Asimismo, SQL Server lleva a cabo las comprobaciones de coherencia de bloqueo correspondientes.

Las sugerencias de bloqueo ROWLOCK, UPDLOCK y XLOCK que adquieren bloqueos a nivel de fila pueden aplicar bloqueos a claves de índice en lugar de a las filas de datos. Por ejemplo, si una tabla tiene un índice no agrupado y un índice de cobertura controla una instrucción SELECT que utiliza una sugerencia de bloqueo, se aplicará un bloqueo a la clave de índice en el índice de cobertura en lugar de aplicarse a la fila de datos en la tabla base.

Si una tabla contiene columnas calculadas y los cálculos de dichas columnas se hacen con expresiones o funciones que tienen acceso a columnas de otras tablas, las sugerencias de tabla no se utilizan en las otras tablas. Esto significa que las sugerencias de tabla no se propagan. Por ejemplo, una sugerencia de tabla NOLOCK se especifica para una tabla de la consulta. Esta tabla tiene columnas calculadas que se calculan a través de una combinación de expresiones y funciones que tienen acceso a columnas de otra tabla. En las tablas a las que hacen referencia estas expresiones y funciones no se utiliza la sugerencia de tabla NOLOCK cuando se tiene acceso.

SQL Server no permite más de una sugerencia de tabla de cada uno de los siguientes grupos para cada tabla en la cláusula FROM:

• Sugerencias de granularidad: PAGLOCK, NOLOCK, ROWLOCK, TABLOCK o TABLOCKX.
• Sugerencias de nivel de aislamiento: HOLDLOCK, NOLOCK, READCOMMITTED, REPEATABLEREAD, SERIALIZABLE.

Usar NOEXPAND

NOEXPAND sólo se aplica a las vistas indizadas. Una vista indizada es una vista con un único índice agrupado creado en ella. Si una consulta tiene referencias a columnas que están presentes en una vista indizada y en tablas base, y el optimizador de consultas determina que el uso de vistas indizadas proporciona el mejor método para ejecutar la consulta, el optimizador de consultas utiliza el índice en la vista. Esta función se denomina coincidencia de vista indizada y sólo se admite en las ediciones Enterprise y Developer de SQL Server 2005.

Sin embargo, para que el optimizador considere las vistas indizadas para establecer coincidencias o utilizar una vista indizada a la que se hace referencia con una sugerencia NOEXPAND, las siguientes opciones SET deben estar establecidas en ON:

1 ARITHABORT se establece implícitamente en ON al establecer ANSI_WARNINGS en ON. Por lo tanto, no es necesario ajustar manualmente este valor.

Asimismo, la opción NUMERIC_ROUNDABORT debe establecerse en OFF.

Para exigir que el optimizador utilice un índice para una vista indizada, especifique la opción NOEXPAND. Esta sugerencia sólo se puede utilizar si la vista también aparece en la consulta. SQL Server 2005 no proporciona ninguna sugerencia que obligue a utilizar una vista indizada particular en una consulta que no mencione a la vista directamente en la cláusula FROM; sin embargo, el optimizador de consultas considera el uso de vistas indizadas, incluso si no se hace referencia directa a ellas en la consulta.

Para obtener más información, vea Resolver índices de vistas.

Permisos

Las sugerencias KEEPIDENTITY, IGNORE_CONSTRAINTS e IGNORE_TRIGGERS requieren permisos ALTER en la tabla.

Ejemplos

El ejemplo siguiente especifica que se aplique un bloqueo compartido a la tabla Production.Product y que se mantenga hasta que finalice la instrucción UPDATE.

UPDATE Production.Product
WITH (TABLOCK)
SET ListPrice = ListPrice * 1.10
WHERE ProductNumber LIKE 'BK-%'

Vea también

Referencia

OPENROWSET (Transact-SQL)
Sugerencias (Transact-SQL)

Otros recursos

Sugerencias de bloqueo
Resolución de vistas

Ayuda e información

Obtener ayuda sobre SQL Server 2005

Historial de cambios