Sugerencias de tabla (Transact-SQL)

Se aplica a:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceBase de datos de Azure SQL de Microsoft Fabric

Las sugerencias de tabla se usan para invalidar el comportamiento predeterminado del optimizador de consultas durante la instrucción DML (lenguaje de manipulación de datos). Puede especificar un método de bloqueo, uno o varios índices, una operación de procesamiento de consultas (como un recorrido de tabla o una búsqueda de índice) u otras opciones. Las sugerencias de tabla se especifican en la cláusula FROM de la instrucción DML y afectan solo a la tabla o vista a la que se hace referencia en esa cláusula.

Se aplica a:

• DELETE
• INSERT
• SELECT
• UPDATE
• MERGE

Convenciones de sintaxis de Transact-SQL

Sintaxis

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

<table_hint> ::=
{ NOEXPAND
  | INDEX ( <index_value> [ , ...n ] ) | INDEX = ( <index_value> )
  | FORCESEEK [ ( <index_value> ( <index_column_name> [ , ... ] ) ) ]
  | FORCESCAN
  | HOLDLOCK
  | NOLOCK
  | NOWAIT
  | PAGLOCK
  | READCOMMITTED
  | READCOMMITTEDLOCK
  | READPAST
  | READUNCOMMITTED
  | REPEATABLEREAD
  | ROWLOCK
  | SERIALIZABLE
  | SNAPSHOT
  | SPATIAL_WINDOW_MAX_CELLS = <integer_value>
  | TABLOCK
  | TABLOCKX
  | UPDLOCK
  | XLOCK
}

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

Argumentos

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

Con algunas excepciones, las sugerencias de tabla se admiten en la cláusula FROM solo cuando se especifican las sugerencias con la palabra clave WITH. Las sugerencias de tabla deben especificarse también con paréntesis.

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

FROM t (TABLOCK)

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

FROM t WITH (TABLOCK, INDEX(myindex))

Recomendamos utilizar comas entre las sugerencias de tabla.

NOEXPAND

Especifica que las vistas indexadas no se expanden 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 indexadas. Para obtener más información, vea Usar NOEXPAND.

INDEX ( <index_value> [ , ...n ] ) | INDEX = ( <index_value> )

La sintaxis INDEX() especifica los nombres o los identificadores de los índices que el optimizador de consultas debe usar al procesar la instrucción. La sintaxis alternativa INDEX = especifica un valor de índice único. Solamente 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 tabla e INDEX(1) se interpreta como un error.

Si se usan varios índices en una lista de sugerencias, los duplicados se omiten y el resto de los índices enumerados se usa para recuperar 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 í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 el Motor de base de datos de SQL Server recupera todas las columnas indexadas.

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

KEEPIDENTITY

Solo se aplica en una instrucción INSERT cuando se usa la opción BULK con OPENROWSET.

Especifica que se usará el valor o 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 en función de los valores de inicialización e incremento especificados durante la creación de la tabla.

Para obtener un ejemplo que use esta sugerencia en una instrucción INSERT ... SELECT * FROM OPENROWSET(BULK...), consulte Mantener valores de identidad al importar datos de forma masiva (SQL Server).

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

KEEPDEFAULTS

Solo se aplica en una instrucción INSERT cuando se usa la opción BULK con OPENROWSET.

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

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

FORCESEEK [ ( <index_value> ( <index_column_name> [ , ...n ] ) ) ]

Especifica que el optimizador de consultas usa solamente una operación de búsqueda de índice como ruta de acceso a los datos de la tabla o la vista.

• 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. 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. El optimizador puede considerar más columnas si es necesario. Por ejemplo, si se especifica un índice no clúster, el optimizador puede optar por usar columnas de clave de índice agrupadas además de las columnas especificadas.

La sugerencia FORCESEEK se puede especificar de las maneras que se indican a continuación.

Sintaxis	Ejemplo	Descripción
Sin un índice o sugerencia INDEX	FROM dbo.MyTable WITH (FORCESEEK)	El optimizador de consultas sólo 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 sobre las sugerencias de consulta, consulte 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 no se encuentra ningún plan, se devuelve el error 8622.

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

• No se puede especificar la sugerencia para una tabla que sea el destino de una instrucción INSERT, UPDATEo DELETE.
• 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, pero 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) serían sintaxis válidas. También serían sintaxis válidas FORCESEEK (MyIndex (c)) y FORCESEEK (MyIndex (a, c).
• 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 agrupadas que se incluyen automáticamente en el índice, pero que el optimizador podría usar.
• Un índice de almacén de columnas optimizado para memoria xVelocity no se puede especificar como parámetro de índice. Se devuelve el error 366.
• La modificación de la definición de índice (por ejemplo, mediante la adición o eliminación de columnas) puede requerir modificaciones en las consultas que hacen referencia a ese í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 partición agregada implícitamente por SQL Server no se puede especificar en la sugerencia FORCESEEK.

FORCESCAN

Se aplica a: SQL Server 2008 R2 (10.50.x) Service Pack 1 y versiones posteriores.

Especifica que el optimizador de consultas usa solamente una operación de búsqueda de índice como la ruta de acceso a la vista o la tabla 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 examinar las rutas de acceso a través del índice especificado, al acceder 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 el caso de las tablas e índices con particiones, se aplica FORCESCAN después de eliminar las particiones mediante la evaluación del predicado de consulta. Esto significa que el recorrido se aplica únicamente a las particiones restantes, no a toda la tabla.

La sugerencia FORCESCAN tiene las restricciones siguientes:

• No se puede especificar la sugerencia para una tabla que sea el destino de una instrucción INSERT, UPDATEo DELETE.
• La sugerencia no se puede emplear con más de una sugerencia de índice.
• La sugerencia impide que el optimizador de consultas considere un í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, consulte SERIALIZABLE más adelante en este artículo. HOLDLOCK solo se aplica a la tabla o vista para la que se especifica y solo durante la duración de la transacción definida por la instrucción en la que se usa. HOLDLOCK no se puede usar en una instrucción SELECT que incluya la opción FOR BROWSE.

IGNORE_CONSTRAINTS

Solo se aplica en una instrucción INSERT cuando se usa la opción BULK con OPENROWSET.

Especifica que la operación de importación masiva omite las restricciones de la tabla. De forma predeterminada, INSERT comprueba restricciones Unique y las restricciones check y restricciones de clave principal y externa. Cuando se especifica IGNORE_CONSTRAINTS para una operación de importación masiva, INSERT debe omitir estas restricciones en una tabla de destino. No se pueden deshabilitar las restricciones UNIQUE, PRIMARY KEYo NOT NULL.

Es posible que desee deshabilitar las restricciones CHECK y FOREIGN KEY si los datos de entrada contienen filas que infringen las restricciones. Al deshabilitar las restricciones CHECK y FOREIGN KEY, puede importar los datos y, a continuación, usar instrucciones Transact-SQL para limpiar los datos.

Sin embargo, cuando se omiten las restricciones CHECK y FOREIGN KEY, cada restricción omitida de la tabla se marca como is_not_trusted en la vista de catálogo de 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 podría superar el costo de aplicar restricciones CHECK y FOREIGN KEY a los datos incrementales.

IGNORE_TRIGGERS

Solo se aplica en una instrucción INSERT cuando se usa la opción BULK con OPENROWSET.

Especifica que la operación de importación masiva ignore todos los desencadenadores definidos en la tabla. De forma predeterminada, INSERT aplica desencadenadores.

Use IGNORE_TRIGGERS solo si la aplicación no depende de ningún desencadenador y maximizar el rendimiento es importante.

NOLOCK

Equivalente a READUNCOMMITTED. Para obtener más información, consulte READUNCOMMITTED más adelante en este artículo.

NOWAIT

Indica al 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. La sugerencia NOWAIT no funciona cuando también se incluye la sugerencia TABLOCK. Para terminar una consulta sin esperas cuando se usa la sugerencia TABLOCK, preceda la consulta con SET LOCK_TIMEOUT 0;.

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 usan 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 cumplen las reglas del nivel de aislamiento de READ COMMITTED mediante el control de versiones de fila o el bloqueo. Si la opción de base de datos READ_COMMITTED_SNAPSHOT es OFF, el motor de base de datos adquiere bloqueos compartidos a medida que se leen los datos y libera esos bloqueos cuando se completa la operación de lectura. Si la opción de base de datos READ_COMMITTED_SNAPSHOT es ON, el motor de base de datos no adquiere bloqueos y usa el control de versiones de fila. Para obtener más información sobre los niveles de aislamiento, vea SET TRANSACTION ISOLATION LEVEL.

READCOMMITTEDLOCK

Especifica que las operaciones de lectura cumplen las reglas del nivel de aislamiento de READ COMMITTED mediante el bloqueo. 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 sobre los niveles de aislamiento, vea SET TRANSACTION ISOLATION LEVEL. Esta sugerencia no se puede especificar en la tabla de destino de una instrucción INSERT; Se devuelve el error 4140.

READPAST

Especifica que el Motor de base de datos no lea las filas bloqueadas por otras transacciones. Cuando se especifica READPAST, se omiten los bloqueos de nivel de fila, pero no los de nivel de página. Es decir, el 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, las instrucciones SELECT * FROM T1 (READPAST) generarán los valores 1, 2, 4, 5. READPAST se usa principalmente para reducir la contención de bloqueos cuando se implementa una cola de trabajo que usa una tabla de SQL Server. Un lector de cola que use READPAST omitirá las entradas de cola anteriores bloqueadas por otras transacciones y pasará 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 hace referencia en una instrucción UPDATE o DELETE y en cualquier tabla a la que se hace referencia en una cláusula FROM. Cuando se especifica en una instrucción UPDATE, READPAST solo se aplica al leer datos para identificar qué registros actualizar, independientemente de dónde se especifique en la instrucción especificada. READPAST no se puede especificar para las tablas de la cláusula INTO de una instrucción INSERT. Las operaciones de actualización o eliminación que usan READPAST podrían bloquearse al leer claves externas o vistas indizadas, o al modificar índices secundarios.

READPAST solo se pueden especificar en transacciones que funcionan en los niveles de aislamiento de 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 cumple 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 que lee 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 para la transacción, presentar usuarios con datos que nunca se han confirmado o hacer que los usuarios vean los registros dos veces (o no en absoluto).

Las sugerencias READUNCOMMITTED y NOLOCK solamente se aplican a bloqueos de datos. Todas las consultas, incluidas las consultas con READUNCOMMITTED y sugerencias de NOLOCK, adquieren bloqueos de Sch-S (estabilidad de 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 consultas que se ejecutan con READUNCOMMITTED o sugerencias de NOLOCK, se bloquean al intentar adquirir un bloqueo de 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.

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 aplican a la tabla de destino de una instrucción UPDATE o DELETE.

Puede minimizar la contención de bloqueo al proteger las transacciones de lecturas sucias de modificaciones de datos no confirmadas mediante cualquiera de las siguientes opciones:

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

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

REPEATABLEREAD

Especifica que un examen se realiza con la misma semántica de bloqueo que una transacción que se ejecuta en REPEATABLE READ nivel de aislamiento. Para obtener más información sobre los niveles de aislamiento, vea SET TRANSACTION ISOLATION LEVEL.

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 usan bloqueos de fila a menos que se combine ROWLOCK con otras sugerencias de tabla que requieran bloqueos, como UPDLOCK y HOLDLOCK. ROWLOCK no se puede usar con una tabla que tenga un índice de almacén de columnas agrupado. En el ejemplo siguiente se devuelve el error 651 a la aplicación.

UPDATE [dbo].[FactResellerSalesXL_CCI] WITH (ROWLOCK)
    SET UnitPrice = 50
WHERE ProductKey = 150;

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.

SNAPSHOT

Se aplica a: SQL Server 2014 (12.x) y versiones posteriores.

Se tiene acceso a la tabla optimizada para memoria con aislamiento SNAPSHOT. SNAPSHOT solo se puede usar con tablas optimizadas para memoria (no con tablas basadas en disco), como se ve en el ejemplo siguiente. Para obtener más información, vea Introducción a las tablas con optimización para memoria.

SELECT *
FROM dbo.Customers AS c WITH (SNAPSHOT)
     LEFT OUTER JOIN dbo.[Order History] AS oh
         ON c.customer_id = oh.customer_id;

SPATIAL_WINDOW_MAX_CELLS = <valor_entero>

Se aplica a: SQL Server 2012 (11.x) y versiones posteriores.

Especifica el número máximo de celdas que se van a usar para teselar un objeto de geometría o geografía. <integer_value> es un valor entero entre 1 y 8192.

Esta opción permite optimizar el tiempo de ejecución de la consulta ajustando el equilibrio entre el tiempo de ejecución del filtro primario y secundario. Un número mayor reduce el tiempo de ejecución del filtro secundario, pero aumenta el tiempo de ejecución del filtro primario y un número menor disminuye el tiempo de ejecución del filtro primario, pero aumenta el tiempo de ejecución del filtro secundario. En el caso de datos espaciales más densos, un número mayor debe producir un tiempo de ejecución más rápido proporcionando una mejor aproximación con el filtro primario y reduciendo el tiempo de ejecución del filtro secundario. Si se trata de datos más dispersos, un número menor disminuirá el tiempo de ejecución del filtro primario.

Esta opción funciona para las teselaciones de cuadrícula manuales y automáticas.

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 podría 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 a un montón mediante la instrucción INSERT INTO <target_table> SELECT <columns> FROM <source_table>, puede habilitar el registro mínimo y el bloqueo óptimo para la instrucción al especificar 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. La sugerencia TABLOCK también permite inserciones paralelas en montones o en índices de almacén de columnas agrupados. Para obtener más información, vea INSERT.

Cuando se usa con el proveedor de conjuntos de filas BULK OPENROWSET para importar los datos a una tabla, TABLOCK permite que varios clientes carguen datos de forma simultánea en la tabla de destino con el bloqueo y el registro optimizados. Para obtener más información, consulte requisitos previos de para el registro mínimo en 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.

Observaciones

Las sugerencias de tabla se pasan por alto si el plan de consulta no accede a la tabla. Esto puede deberse a que el optimizador no tiene acceso a la tabla en absoluto o porque se tiene acceso a una vista indizada en su lugar. En este último caso, se puede evitar el acceso a una vista indizada mediante 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, UPDLOCKy XLOCK que adquieren bloqueos de nivel de fila pueden colocar bloqueos en claves de índice en lugar de en las filas de datos reales. Por ejemplo, si una tabla tiene un índice no agrupado y una instrucción SELECT mediante una sugerencia de bloqueo se controla mediante un índice de cobertura, se adquiere un bloqueo en la clave de índice del índice de cobertura en lugar de en la fila de datos de la tabla base.

Si una tabla contiene columnas calculadas mediante expresiones o funciones que acceden a columnas de otras tablas, las sugerencias de tabla no se usan en las otras tablas y 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 usa la sugerencia de tabla NOLOCK cuando se accede.

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

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

Sugerencias de índice filtrado

Un índice filtrado se puede usar como sugerencia de tabla, pero hace que el optimizador de consultas genere el error 8622 si no cubre todas las filas que selecciona la consulta. A continuación se muestra un ejemplo de una sugerencia de índice filtrado no válida. En el ejemplo se crea el índice filtrado FIBillOfMaterialsWithComponentID y, a continuación, se usa como sugerencia de índice para una instrucción SELECT. El predicado de índice filtrado incluye las filas de datos para los ComponentID 533, 324 y 753. El predicado de consulta también incluye las filas de datos para los ComponentID 533, 324 y 753, pero amplía el conjunto de resultados para incluir los ComponentID 855 y 924, que no están en el índice filtrado. Por tanto, el optimizador de consultas no puede usar la sugerencia de índice filtrado y genera el error 8622. Para obtener más información, vea Crear índices filtrados.

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

El optimizador de consultas no tiene en cuenta una sugerencia de índice si las opciones de SET no tienen los valores necesarios para los índices filtrados. Para obtener más información, vea CREATE INDEX.

Uso de NOEXPAND

NOEXPAND solo se aplica a las vistas indexadas. Una vista indizada es una vista con un único índice clúster 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 funcionalidad se denomina coincidencia de vista indizada. Antes de SQL Server 2016 (13.x) con Service Pack 1, el uso automático de una vista indizada por el optimizador de consultas solo se admite en ediciones específicas de SQL Server. En SQL Server 2016 (13.x) con Service Pack 1 y versiones posteriores, todas las ediciones admiten el uso automático de una vista indizada. La base de datos de Azure SQL y Azure SQL Managed Instance también admiten el uso automático de vistas indexadas sin especificar la sugerencia NOEXPAND.

Para obtener más información, consulte Guía de arquitectura de procesamiento de consulta.

Para obtener una lista de las características admitidas por ediciones de SQL Server en Windows, vea:

• Ediciones y características admitidas de SQL Server 2022
• Ediciones y características admitidas de SQL Server 2019
• Ediciones y las características admitidas de SQL Server 2017
• Ediciones y las características admitidas de SQL Server 2016

Sin embargo, para que el optimizador de consultas considere las vistas indizadas para buscar coincidencias o use una vista indizada a la que se hace referencia con la sugerencia NOEXPAND, las siguientes opciones de SET deben establecerse en ON.

• ANSI_NULLS
• ANSI_PADDING
• ANSI_WARNINGS
• ARITHABORT ¹
• CONCAT_NULL_YIELDS_NULL
• QUOTED_IDENTIFIER

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

Además, la opción NUMERIC_ROUNDABORT debe establecerse en OFF.

Para exigir que el optimizador de consultas use un índice para una vista indexada, especifique la opción NOEXPAND. Esta sugerencia solo se puede usar si la vista también aparece en la consulta. SQL Server no proporciona una sugerencia para forzar que se use una vista indizada determinada en una consulta que no asigne un nombre a la vista directamente en la cláusula FROM. Aun así, el optimizador de consultas considera el uso de vistas indexadas, incluso aunque no se haga referencia directa a ellas en la consulta. El motor de base de datos de SQL Server solo crea automáticamente estadísticas en una vista indizada cuando se usa una sugerencia de tabla NOEXPAND. La omisión de esta sugerencia puede provocar advertencias del plan de ejecución sobre estadísticas que faltan que no se pueden resolver mediante la creación manual de estadísticas.

Durante la optimización de consultas, el Motor de base de datos usa las estadísticas de vista creadas de forma automática o manual cuando la consulta hace referencia directamente a la vista y se usa la sugerencia NOEXPAND.

Uso de una sugerencia de tabla como sugerencia de consulta

sugerencias de tabla también se pueden especificar como sugerencia de consulta mediante la cláusula OPTION (TABLE HINT). Se recomienda usar una sugerencia de tabla como una sugerencia de consulta únicamente en el contexto de una guía de plan. Para las consultas ad hoc, especifique estas sugerencias únicamente como sugerencias de tabla. Para obtener más información, consulte Sugerencias de consulta.

Permisos

Las sugerencias de KEEPIDENTITY, IGNORE_CONSTRAINTSy IGNORE_TRIGGERS requieren permisos ALTER en la tabla.

Ejemplos

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

En el ejemplo siguiente se especifica que se toma un bloqueo compartido en la tabla Production.Product de la base de datos AdventureWorks2022 y se mantiene hasta el final de la instrucción UPDATE.

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

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

En el ejemplo siguiente, se usa la sugerencia FORCESEEK sin especificar un índice para obligar a que el optimizador de consultas realice una operación de búsqueda de índice en la tabla Sales.SalesOrderDetail de la base de datos AdventureWorks2022.

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

En el ejemplo siguiente se usa la sugerencia FORCESEEK con un índice para obligar a que el optimizador de consultas realice una operación de búsqueda de índice en el índice y la columna de índice especificados.

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. Uso de la sugerencia FORCESCAN para especificar una operación de examen de índice

En el ejemplo siguiente, se usa la sugerencia FORCESCAN para obligar a que el optimizador de consultas realice una operación de examen en la tabla Sales.SalesOrderDetail de la base de datos AdventureWorks2022.

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

Contenido relacionado

• OPENROWSET (Transact-SQL)
• Sugerencias (Transact-SQL)
• Sugerencias de consulta (Transact-SQL)