Sugerencias de tabla (Transact-SQL)

Sintaxis

WITH  ( <table_hint> [ [, ]...n ] )

<table_hint> ::= 
[ NOEXPAND ] { 
    INDEX ( index_value [ ,...n ] ) | INDEX =  ( index_value )
  | FASTFIRSTROW 
  | FORCESEEK [( index_value ( index_column_name  [ ,... ] ) ) ]
  | FORCESCAN
  | 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 
  | NOLOCK 
  | NOWAIT
  | PAGLOCK 
  | READCOMMITTED 
  | READCOMMITTEDLOCK 
  | READPAST 
  | REPEATABLEREAD 
  | ROWLOCK 
  | SERIALIZABLE 
  | TABLOCK 
  | TABLOCKX 
  | UPDLOCK 
  | XLOCK 
} 

Argumentos

• WITH ( <table_hint> ) [ [ , ]...n ]
  Con algunas excepciones, las sugerencias de tabla únicamente se admiten cuando las sugerencias se especifican con la palabra clave WITH. Es necesario usar paréntesis.

  Importante

  La omisión de la palabra clave WITH es una característica desusada y se quitará en una versión futura de Microsoft SQL Server. Especifique siempre WITH en los nuevos trabajos de desarrollo y modifique las aplicaciones que actualmente omiten esta palabra clave. La separación de las sugerencias con espacios en lugar de comas es una característica desusada y se quitará en una versión futura de Microsoft SQL Server. Especifique siempre comas en los nuevos trabajos de desarrollo y modifique las aplicaciones que actualmente omiten las comas.

  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 WITH (TABLOCK). Cuando la sugerencia se especifica con otra opción, debe especificarse con la palabra clave WITH. Por ejemplo: FROM t WITH (TABLOCK, INDEX(myindex)).

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

• 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 clúster. NOEXPAND solo se aplica a las vistas indizadas. Para obtener más información, vea la sección Comentarios.

• INDEX (index_value [,... n ] ) | INDEX = (index_value)
  La sintaxis INDEX(index_value) especifica el nombre o el identificador de uno o más índices que el optimizador de consultas usará al procesar la instrucción. La sintaxis alternativa INDEX = (index_value) solo permite un único valor de índice.

  Si existe un índice en clúster, INDEX(0) exige un examen del índice en clúster e INDEX(1) exige un examen o una búsqueda del índice en clúster. Si no existe un índice en clúster, INDEX(0) exige un examen de tabla e INDEX(1) se interpreta como error.

  Si se usan varios índices en una lista de sugerencias, los índices duplicados se omiten y el resto se emplea para recuperar las filas de la tabla. El orden de los índices de la sugerencia de índice es importante. Una sugerencia de varios índices también 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 índices sugeridos no incluye todas las columnas a las que hace referencia la consulta, se realiza una captura para recuperar las columnas restantes, una vez que SQL Server Database Engine (Motor de base de datos de SQL Server) recupera 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 no en clúster que se pueden especificar en la sugerencia de tabla es 250.

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

  Especifica que se usará el valor o los valores de identidad del 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, y la columna de identidad no es la última columna de la tabla, entonces deberá omitir la columna de identidad. 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 obtener un ejemplo que utiliza esta sugerencia en una instrucción INSERT... Para la instrucción 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
  Solamente se aplica a una instrucción INSERT cuando se utiliza la opción BULK con OPENROWSET.

  Especifica la inserción del valor predeterminado de la columna de una tabla, si existe, en lugar de NULL, cuando falta el valor del registro de datos de esa columna.

  Para obtener un ejemplo que utiliza esta sugerencia en una instrucción INSERT... SELECT <columns> 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 Sugerencias de consulta (Transact-SQL).

  Importante
  Esta característica se quitará en la versión siguiente de Microsoft SQL Server. No utilice esta característica en nuevos trabajos de desarrollo y modifique lo antes posible las aplicaciones que actualmente la utilizan.

• FORCESEEK [ **(index_value(**index_column_name [ ,... n ] )) ]
  Especifica que el optimizador de consultas use solamente una operación de búsqueda de índice como ruta de acceso a los datos de la tabla o la vista. A partir de SQL Server 2008 R2 SP1, también se pueden especificar parámetros de índice. Especificar FORCESEEK con parámetros de índice es similar a usar FORCESEEK con una sugerencia INDEX. Sin embargo, puede lograr mayor control sobre la ruta de acceso empleada por el optimizador de consultas si se especifican tanto el índice que se va a buscar como las columnas de índice que hay que tener en cuenta en la operación de búsqueda.

  • index_value
    Es el nombre o el valor de identificador del índice. No se puede especificar el identificador de índice 0 (montón). Para devolver el nombre o el identificador del índice, consulte la vista de catálogo sys.indexes.

  • index_column_name
    Es el nombre de la columna de índice que se va a incluir en la operación de búsqueda. El optimizador de consultas solo considera las operaciones de búsqueda de índices mediante el índice especificado usando al menos las columnas de índice especificadas. El optimizador también puede considerar columnas adicionales si es necesario. Por ejemplo, si se especifica un índice no clúster, el optimizador puede elegir el uso de columnas de clave de índice clúster además de las columnas especificadas.

  La sugerencia FORCESEEK se puede especificar de las maneras siguientes.

  Sintaxis	Ejemplo	Descripción
  Sin un índice o sugerencia INDEX	FROM dbo.MyTable WITH (FORCESEEK)	El optimizador de consultas solo considera las operaciones de búsqueda de índice para tener acceso a la tabla o la vista mediante cualquier índice pertinente.
  Combinado con una sugerencia INDEX	FROM dbo.MyTable WITH (FORCESEEK, INDEX (MyIndex))	El optimizador de consultas solo considera las operaciones de búsqueda de índice para tener acceso a la tabla o la vista mediante el índice especificado.
  Parametrizado especificando un índice y columnas de índice	FROM dbo.MyTable WITH (FORCESEEK (MyIndex (col1, col2, col3)))	El optimizador de consultas solo considera las operaciones de búsqueda de índice para tener acceso a la tabla o la vista mediante el índice especificado usando al menos las columnas de índice especificadas.

  Cuando se usa la sugerencia FORCESEEK (con o sin parámetros de índice), tenga en cuenta las directrices siguientes.

  • La sugerencia se puede especificar como sugerencia de tabla o como sugerencia de consulta. Para obtener más información acerca de las sugerencias de consulta, vea Sugerencias de consulta (Transact-SQL).

  • Para aplicar FORCESEEK a una vista indizada, se debe especificar también la sugerencia NOEXPAND.

  • La sugerencia se puede aplicar una vez por tabla o vista como máximo.

  • No se puede especificar la sugerencia para un origen de datos remoto. Se devuelve el error 7377 cuando se especifica FORCESEEK con una sugerencia de índice y el error 8180 cuando se usa FORCESEEK sin una sugerencia de índice.

  • Si FORCESEEK hace que no se encuentre ningún plan, se devuelve el error 8622.

  Cuando FORCESEEK se especifica con parámetros de índice, se aplican las siguientes directrices y restricciones.

  • La sugerencia no se puede especificar junto con una sugerencia INDEX o con otra sugerencia FORCESEEK.

  • Se debe especificar al menos una columna y debe ser la columna de clave inicial.

  • Se pueden especificar columnas de índice adicionales, aunque no se pueden omitir columnas de clave. Por ejemplo, si el índice especificado contiene las columnas de clave a, b y c, FORCESEEK (MyIndex (a)) y FORCESEEK (MyIndex (a, b) son sintaxis válidas. FORCESEEK (MyIndex (c)) y FORCESEEK (MyIndex (a, c) serían sintaxis no válidas.

  • El orden de los nombres de columna especificados en la sugerencia debe coincidir con el orden de las columnas en el índice al que se hace referencia.

  • No se pueden especificar columnas que no estén en la definición de clave de índice. Por ejemplo, en un índice no clúster solo se pueden especificar las columnas de clave de índice definidas. No se pueden especificar columnas de clave en clúster que se incluyen automáticamente en el índice, aunque el optimizador puede usarlas.

  • La modificación de la definición de índice (por ejemplo, agregando o quitando columnas) puede hacer que sea necesario modificar las consultas que hacen referencia a dicho índice.

  • La sugerencia impide que el optimizador considere ningún índice espacial o XML de la tabla.

  • La sugerencia no se puede especificar junto con la sugerencia FORCESCAN.

  • En el caso de índices con particiones, la columna de creación de particiones agregada implícitamente por SQL Server no se puede especificar en la sugerencia FORCESEEK. Para obtener más información, vea Directrices especiales para índices con particiones.

  Advertencia

  Al especificar FORCESEEK con parámetros de índice se limita el número de planes que el optimizador puede considerar en comparación con cuando se especifica FORCESEEK sin parámetros. Esto puede producir un error "No se puede generar el plan" en más casos. En una versión futura, las modificaciones internas realizadas en el optimizador pueden permitir que se consideren más planes. Para obtener más información, vea Utilizar la sugerencia de tabla FORCESEEK.

• FORCESCAN
  Esta sugerencia, presentada en SQL Server 2008 R2 SP1, especifica que el optimizador de consultas solo usa una operación de examen de índice como ruta de acceso a la tabla o la vista a la que se hace referencia. La sugerencia FORCESCAN puede ser útil para consultas en las que el optimizador subestima el número de filas afectadas y elige una operación de búsqueda en lugar de una operación de examen. Cuando esto ocurre, la cantidad de memoria asignada a la operación es demasiado pequeña y el rendimiento de la consulta se ve afectado.

  FORCESCAN se puede especificar con o sin una sugerencia INDEX. Cuando se combina con una sugerencia de índice, (INDEX = index_name, FORCESCAN), el optimizador de consultas solo considera el examen de rutas de acceso mediante el índice especificado al tener acceso a la tabla a la que se hace referencia. Se puede especificar FORCESCAN con la sugerencia de índice INDEX(0) para forzar una operación de recorrido de tabla en la tabla base.

  En las tablas y los índices con particiones, FORCESCAN se aplica una vez eliminadas las particiones mediante la evaluación de predicados de las consultas. Esto significa que el recorrido se aplica únicamente a las particiones restantes, no a toda la tabla.

  La sugerencia FORCESCAN tiene las restricciones siguientes.

  • La sugerencia no se puede especificar para una tabla que es el destino de una instrucción INSERT, UPDATE o DELETE.

  • La sugerencia no se puede emplear con más de una sugerencia de índice.

  • La sugerencia impide que el optimizador considere ningún índice espacial o XML de la tabla.

  • No se puede especificar la sugerencia para un origen de datos remoto.

  • La sugerencia no se puede especificar junto con la sugerencia FORCESEEK.

• HOLDLOCK
  Equivalente a SERIALIZABLE. Para obtener más información, vea SERIALIZABLE más adelante en este tema. HOLDLOCK solamente se aplica a la tabla o vista para la que se especificó, y únicamente 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
  Solamente 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 ignora 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 ignorar estas restricciones en una tabla de destino. Tenga en cuenta que no puede deshabilitar las restricciones UNIQUE, PRIMARY KEY o NOT NULL.

  Puede ser conveniente deshabilitar las instrucciones CHECK y FOREING KEY si 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, si se omiten las restricciones CHECK y FOREIGN KEY, cada una de las restricciones omitidas de la tabla se marcará como is_not_trusted en la vista de catálogo sys.check_constraints o sys.foreign_keys después de la operación. En algún momento, deberá 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
  Solamente 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 ignore todos los desencadenadores definidos en la tabla. De manera predeterminada, INSERT aplica desencadenadores.

  Use IGNORE_TRIGGERS únicamente 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.

  Nota

  Para las instrucciones UPDATE o DELETE: Esta característica se quitará en una versión futura de Microsoft SQL Server. Evite utilizar esta característica en nuevos trabajos de desarrollo y tenga previsto modificar las aplicaciones que actualmente la utilizan.

• NOWAIT
  Indica a Motor de base de datos 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 las reglas del nivel de aislamiento READ COMMITTED, a través del uso de bloqueos o control de versiones de filas. Si la opción de la base de datos READ_COMMITTED_SNAPSHOT está establecida en OFF, 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. Si la opción de base de datos READ_COMMITTED_SNAPSHOT está establecida en ON, el Motor de base de datos no adquiere bloqueos y utiliza el control de versiones de filas. Para obtener más información acerca de los niveles de aislamiento, vea SET TRANSACTION ISOLATION LEVEL (Transact-SQL).

  Nota

  Para las instrucciones UPDATE o DELETE: Esta característica se quitará en una versión futura de Microsoft SQL Server. Evite utilizar esta característica en nuevos trabajos de desarrollo y tenga previsto modificar las aplicaciones que actualmente la utilizan.

• READCOMMITTEDLOCK
  Especifica que las operaciones de lectura cumplan, mediante el uso de bloqueos, las reglas del nivel de aislamiento READ COMMITTED. El Motor de base de datos adquiere bloqueos compartidos a medida que se leen los datos y los libera cuando se completa 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 acerca de los niveles de aislamiento, vea SET TRANSACTION ISOLATION LEVEL (Transact-SQL). Esta sugerencia no se puede especificar en la tabla de destino de una instrucción INSERT; de lo contrario, se devuelve el error 4140.

• READPAST
  Especifica que Motor de base de datos no lea las filas bloqueadas por otras transacciones. Cuando se especifica READPAST, se omiten los bloqueos en el nivel de fila. Es decir, Motor de base de datos omite las filas en lugar de bloquear la transacción actual hasta que se liberen los bloqueos. Suponga, por ejemplo, una tabla T1 que contiene una única columna de tipo entero con los valores 1, 2, 3, 4, 5. Si una transacción A cambia el valor de 3 a 8 pero aún no se ha confirmado, una instrucción SELECT * FROM T1 (READPAST) generará los valores 1, 2, 4, 5. READPAST se utiliza principalmente para reducir el conflicto de bloqueos cuando se implementa una cola de trabajo que utiliza 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.

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

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

  No se puede especificar la sugerencia de tabla READPAST cuando la opción de base de datos READ_COMMITTED_SNAPSHOT esté establecida en ON y se cumpla alguna de las condiciones siguientes.

  • El nivel de aislamiento de transacción de la sesión es READ COMMITTED.

  • La sugerencia de tabla READCOMMITTED también se especifica en la consulta.

  Para especificar la sugerencia READPAST en estos casos, quite la sugerencia de tabla READCOMMITTED, si está presente, e incluya la sugerencia de tabla READCOMMITTEDLOCK en la consulta.

• 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, mientras que 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, presentar a los usuarios datos no confirmados o hacer que los usuarios vean los registros dos veces (o nunca). Para obtener más información acerca de las lecturas de datos no actualizados, las lecturas no repetibles y las lecturas ficticias, vea Efectos de la simultaneidad.

  Las sugerencias READUNCOMMITTED y NOLOCK solamente 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. Debido a ello, las consultas se bloquean cuando una transacción simultánea aloja un bloqueo de modificación del esquema (Sch-M) 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 operaciones de inserción, actualización y eliminación. 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 SQL Server se quitará el uso de 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.

  Si lo desea, también puede reducir al mínimo el conflicto de bloqueos mientras protege las transacciones de lecturas de datos no actualizados 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 acerca de 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 acerca de 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 acerca de los niveles de aislamiento, vea SET TRANSACTION ISOLATION LEVEL (Transact-SQL).

• TABLOCK
  Especifica que el bloqueo adquirido se aplique en el nivel de tabla. El tipo de bloqueo que se ha adquirido depende de la instrucción que se esté ejecutando. Por ejemplo, una instrucción SELECT puede adquirir un bloqueo compartido. Al especificar TABLOCK, el bloqueo compartido se aplica a toda la tabla, no en el nivel de fila o de página. Si también se especifica HOLDLOCK, el bloqueo de tabla se mantiene hasta el final de la transacción.

  Al importar los datos en un montón usando la instrucción INSERT INTO <target_table> SELECT <columns> FROM <source_table> puede habilitar el registro optimizado y el bloqueo de la instrucción especificando la sugerencia TABLOCK para la tabla de destino. Además, el modelo de recuperación de la base de datos debe establecerse en registro simple o masivo. Para obtener más información, vea INSERT (Transact-SQL).

  Cuando se utiliza con el proveedor de conjuntos de filas BULK OPENROWSET para importar los datos en una tabla, TABLOCK permite que varios clientes carguen datos simultáneamente en la tabla de destino, con el bloqueo y el registro optimizados. Para obtener más información, vea Requisitos previos para el registro mínimo durante la importación masiva.

• TABLOCKX
  Especifica que se aplique un bloqueo exclusivo en la tabla.

• UPDLOCK
  Especifica que se apliquen bloqueos de actualización y se mantengan hasta que se complete la transacción. UPDLOCK aplica bloqueos de actualización para operaciones de lectura solo en el nivel de fila o de página. Si UPDLOCK se combina con TABLOCK, o si se aplica un bloqueo de nivel de tabla por alguna otra razón, se aplicará un bloqueo exclusivo (X) en su lugar.

  Cuando se especifica UPDLOCK, las sugerencias de nivel de aislamiento READCOMMITTED y READCOMMITTEDLOCK se omiten. Por ejemplo, si el nivel de aislamiento de la sesión se establece en SERIALIZABLE y una consulta especifica (UPDLOCK, READCOMMITTED), la sugerencia READCOMMITTED se omitirá y la transacción se ejecutará usando el nivel de aislamiento SERIALIZABLE.

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

Comentarios

Las sugerencias de tabla se pasan por alto si el plan de consulta 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).

Todas las sugerencias de bloqueo se propagan a todas las tablas y vistas a las que tiene acceso el plan de consulta, incluidas 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 de 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 clúster 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 de 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 mediante 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, READCOMMITTEDLOCK, ROWLOCK, TABLOCK o TABLOCKX.

• Sugerencias de nivel de aislamiento: HOLDLOCK, NOLOCK, READCOMMITTED, REPEATABLEREAD, SERIALIZABLE.

Sugerencias de índice filtrado

USE AdventureWorks2008R2;
GO
IF EXISTS (SELECT name FROM sys.indexes
    WHERE name = N'FIBillOfMaterialsWithComponentID' 
    AND object_id = OBJECT_ID(N'Production.BillOfMaterials'))
DROP INDEX FIBillOfMaterialsWithComponentID
    ON Production.BillOfMaterials;
GO
CREATE NONCLUSTERED INDEX "FIBillOfMaterialsWithComponentID"
    ON Production.BillOfMaterials (ComponentID, StartDate, EndDate)
    WHERE ComponentID IN (533, 324, 753);
GO
SELECT StartDate, ComponentID FROM Production.BillOfMaterials
    WITH( INDEX (FIBillOfMaterialsWithComponentID) )
    WHERE ComponentID in (533, 324, 753, 855, 924);
GO

Usar NOEXPAND

Usar una sugerencia de tabla como una sugerencia de consulta

Permisos

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

Ejemplos

A. Usar la sugerencia TABLOCK para especificar un método de bloqueo

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

B. Usar la sugerencia FORCESEEK para especificar una operación de búsqueda de índices

USE AdventureWorks2008R2;
GO
SELECT *
FROM Sales.SalesOrderHeader AS h
INNER JOIN Sales.SalesOrderDetail AS d WITH (FORCESEEK)
    ON h.SalesOrderID = d.SalesOrderID 
WHERE h.TotalDue > 100
AND (d.OrderQty > 5 OR d.LineTotal < 1000.00);
GO

USE AdventureWorks2008R2; 
GO
SELECT h.SalesOrderID, h.TotalDue, d.OrderQty
FROM Sales.SalesOrderHeader AS h
    INNER JOIN Sales.SalesOrderDetail AS d 
    WITH (FORCESEEK (PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID (SalesOrderID))) 
    ON h.SalesOrderID = d.SalesOrderID 
WHERE h.TotalDue > 100
AND (d.OrderQty > 5 OR d.LineTotal < 1000.00); 
GO

C. Usar la sugerencia FORCESCAN para especificar una operación de examen de índice

USE AdventureWorks2008R2; 
GO
SELECT h.SalesOrderID, h.TotalDue, d.OrderQty
FROM Sales.SalesOrderHeader AS h
    INNER JOIN Sales.SalesOrderDetail AS d 
    WITH (FORCESCAN) 
    ON h.SalesOrderID = d.SalesOrderID 
WHERE h.TotalDue > 100
AND (d.OrderQty > 5 OR d.LineTotal < 1000.00);

Vea también

Referencia

Conceptos