Dicas de tabela (Transact-SQL)

Aplica-se a:SQL ServerBanco de Dados SQL do AzureInstância Gerenciada de SQL do Azure

Dicas de tabela são usadas para substituir o comportamento padrão do otimizador de consulta durante a instrução DML (linguagem de manipulação de dados). Você pode especificar um método de bloqueio, um ou mais índices, uma operação de processamento de consulta, como uma verificação de tabela ou busca de índice, ou outras opções. As dicas da tabela são especificadas na cláusula FROM da instrução DML e afetam apenas a tabela ou exibição referenciada nessa cláusula.

Aplica-se a:

• DELETE
• INSERT
• SELECT
• UPDATE
• MERGE

Convenções de sintaxe do Transact-SQL

Sintaxe

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 ]

Com algumas exceções, há suporte para dicas de tabela na cláusula FROM somente quando elas são especificadas com a palavra-chave WITH. Dicas de tabela também devem ser especificadas com parênteses.

As seguintes dicas de tabela são permitidas com e sem a palavra-chave WITH: NOLOCK, READUNCOMMITTED, UPDLOCK, REPEATABLEREAD, SERIALIZABLE, READCOMMITTED, TABLOCK, TABLOCKX, PAGLOCK, ROWLOCK, NOWAIT, READPAST, XLOCK, SNAPSHOT e NOEXPAND. Quando essas dicas de tabela forem especificadas sem a palavra-chave WITH, elas deverão ser especificadas sozinhas. Por exemplo:

FROM t (TABLOCK)

Quando especificada com outra opção, a dica deverá ser especificada com a palavra-chave WITH:

FROM t WITH (TABLOCK, INDEX(myindex))

É recomendável usar vírgulas entre dicas de tabela.

NOEXPAND

Especifica que qualquer exibição indexada não será expandida para acessar tabelas subjacentes quando o otimizador de consulta processar a consulta. O otimizador de consulta trata a exibição como uma tabela com índice clusterizado. NOEXPAND aplica-se apenas a exibições indexadas. Para obter mais informações, confira Usando NOEXPAND.

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

A sintaxe de INDEX() especifica os nomes ou as IDs de um ou mais índices a serem usados pelo otimizador de consulta ao processar a instrução. A sintaxe INDEX = alternativa especifica um único valor de índice. Apenas uma dica de índice por tabela pode ser especificada.

Se houver um índice clusterizado, INDEX(0) forçará uma verificação de índice clusterizado e INDEX(1) forçará uma verificação ou busca de índice clusterizado. Se não existir nenhum índice clusterizado, INDEX(0) forçará uma verificação de tabela e INDEX(1) será interpretado como um erro.

Se forem usados vários índices em apenas uma lista de índices, as duplicatas serão ignoradas e os demais índices listados serão usados para recuperar as linhas da tabela. A ordem dos índices na dica de índice é importante. Uma dica de vários índices também impõe o uso de AND de índice e o otimizador de consulta aplicará tantas condições quantas forem possíveis em cada índice acessado. Se a coleção de índices com dica não incluir todas as colunas referidas pela consulta, uma busca será executada para recuperar as colunas restantes depois que o Mecanismo de Banco de Dados do SQL Server recuperar todas as colunas indexadas.

O número máximo de índices na dica de tabela é de 250 índices não clusterizados.

KEEPIDENTITY

É aplicável apenas em uma instrução INSERT quando a opção BULK é usada com OPENROWSET.

Especifica que o valor, ou valores, de identidade no arquivo de dados importado deve ser usado para a coluna de identidade. Se KEPIDENTITY não estiver especificado, os valores de identidade dessa coluna serão verificados, mas não importados, e o otimizador de consulta atribuirá automaticamente os valores com base nos valores de semente e de incremento especificados durante a criação da tabela.

Para obter um exemplo que usa essa dica em uma instrução INSERT ... SELECT * FROM OPENROWSET(BULK...), confira Manter valores de identidade ao importar dados em massa (SQL Server).

Para obter informações sobre como verificar o valor de identidade de uma tabela, confira DBCC CHECKIDENT (Transact-SQL).

KEEPDEFAULTS

É aplicável apenas em uma instrução INSERT quando a opção BULK é usada com OPENROWSET.

Especifica a inserção de um valor padrão da coluna de tabela, se houver algum, em vez de NULL, se o registro de dados não tiver um valor para a coluna.

Para obter um exemplo que usa essa dica em uma instrução INSERT... SELECT * FROM OPENROWSET(BULK...), confira Manter valores nulos ou usar os valores padrão durante a importação em massa (SQL Server).

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

Especifica que o otimizador de consulta usará apenas uma operação de busca de índice como o caminho de acesso aos dados na tabela ou exibição.

• index_value

  É o valor do nome ou da ID do índice. A ID do índice 0 (heap) não pode ser especificada. Para retornar o nome ou a ID do índice, confira a exibição de catálogo sys.indexes.

• index_column_name

  É o nome da coluna de índice a ser incluída na operação de busca. Especificar FORCESEEK com parâmetros de índice é semelhante a usar FORCESEEK com uma dica INDEX. Entretanto, você pode adquirir maior controle sobre o caminho de acesso usado pelo otimizador de consulta especificando o índice a ser pesquisado e as colunas de índice a serem consideradas na operação de busca. O otimizador pode considerar colunas adicionais, caso necessário. Por exemplo, se um índice não clusterizado for especificado, o otimizador poderá optar por usar colunas de chave de índice clusterizado além das colunas especificadas.

A dica FORCESEEK pode ser especificada das maneiras a seguir.

Sintaxe	Exemplo	Descrição
Sem um índice ou uma dica INDEX	FROM dbo.MyTable WITH (FORCESEEK)	O otimizador de consulta considera apenas as operações de busca de índice para acessar a tabela ou exibição através de qualquer índice relevante.
Combinado com uma dica INDEX	FROM dbo.MyTable WITH (FORCESEEK, INDEX (MyIndex))	O otimizador de consulta considera apenas as operações de busca de índice para acessar a tabela ou exibição através do índice especificado.
Parametrizado especificando um índice e colunas do índice	FROM dbo.MyTable WITH (FORCESEEK (MyIndex (col1, col2, col3)))	O otimizador de consulta considera apenas as operações de busca de índice para acessar a tabela ou exibição através do índice especificado, usando pelo menos as colunas de índice especificadas.

Ao usar a dica FORCESEEK (com ou sem parâmetros de índice), considere as seguintes diretrizes:

• A dica pode ser especificada como uma dica de tabela ou como uma dica de consulta. Para obter mais informações sobre dicas de consulta, confira Dicas de consulta (Transact-SQL).
• Para aplicar FORCESEEK a uma exibição indexada, a dica NOEXPAND também deve ser especificada.
• A dica pode ser aplicada no máximo uma vez por tabela ou exibição.
• A dica não pode ser especificada para uma fonte de dados remota. O erro 7377 é retornado quando FORCESEEK é especificado com uma dica de índice e o erro 8180 é retornado quando FORCESEEK é usado sem uma dica de índice.
• Se FORCESEEK impedir a localização de um plano, o erro 8622 será retornado.

Quando FORCESEEK for especificado com parâmetros de índice, as seguintes diretrizes e restrições se aplicarão:

• A dica não pode ser especificada para uma tabela que é o destino de uma instrução INSERT, UPDATE ou DELETE.
• A dica não pode ser especificada em combinação com uma dica INDEX ou outra dica FORCESEEK.
• Pelo menos uma coluna deve ser especificada e ela deve ser a coluna de chave à esquerda.
• Colunas de índice adicionais podem ser especificadas; entretanto, colunas de chave não podem ser ignoradas. Por exemplo, se o índice especificado contiver as colunas de chave a, b e c, a sintaxe válida incluirá FORCESEEK (MyIndex (a)) e FORCESEEK (MyIndex (a, b). A sintaxe inválida incluiria FORCESEEK (MyIndex (c)) e FORCESEEK (MyIndex (a, c).
• A ordem de nomes de coluna especificada na dica deve corresponder à ordem das colunas no índice referenciado.
• As colunas que não constam na definição de chave de índice não podem ser especificadas. Por exemplo, em um índice não clusterizado, apenas as colunas de chave de índice definidas podem ser especificadas. As colunas de chave clusterizadas que são incluídas automaticamente no índice não podem ser especificadas, mas podem ser usadas pelo otimizador.
• Um índice columnstore xVelocity otimizado para memória não pode ser especificado como parâmetro de índice. O erro 366 é retornado.
• A modificação da definição de índice (por exemplo, adicionando ou removendo colunas) talvez exija modificações nas consultas que fazem referência a esse índice.
• A dica impede que o otimizador considere índices espaciais ou XML na tabela.
• A dica não pode ser especificada em combinação com a dica FORCESCAN.
• Para índices particionados, a coluna de particionamento implicitamente adicionada pelo SQL Server não pode ser especificada na dica FORCESEEK.

FORCESCAN

Aplica-se a: SQL Server 2008 R2 (10.50.x) Service Pack 1 e versões posteriores

Especifica que o otimizador de consulta usará apenas uma operação de verificação de índice como o caminho de acesso na tabela ou exibição referenciada. A dica FORCESCAN pode ser útil em consultas nas quais o otimizador menospreza o número de linhas afetadas e escolhe uma operação de busca em vez de uma operação de verificação. Quando isso ocorre, a quantidade de memória concedida para a operação é muito baixa e o desempenho da consulta é afetado.

FORCESCAN pode ser especificado com ou sem uma dica INDEX. Quando combinado com uma dica de índice, (INDEX = index_name, FORCESCAN), o otimizador de consulta considera apenas os caminhos de acesso de verificação por meio do índice especificado ao acessar a tabela referenciada. FORCESCAN pode ser especificado com a dica de índice INDEX(0) para forçar uma operação de verificação de tabela na tabela base.

Para tabelas e índices particionados, FORCESCAN é aplicado após a eliminação de partições através da avaliação do predicado de consulta. Isso significa que a verificação é aplicada apenas às partições restantes e não à tabela inteira.

A dica FORCESCAN tem as seguintes restrições:

• A dica não pode ser especificada para uma tabela que é o destino de uma instrução INSERT, UPDATE ou DELETE.
• A dica não pode ser usada com mais de uma dica de índice.
• A dica impede que o otimizador de consulta considere índices espaciais ou XML na tabela.
• A dica não pode ser especificada para uma fonte de dados remota.
• A dica não pode ser especificada em combinação com a dica FORCESEEK.

HOLDLOCK

Equivalente a SERIALIZABLE. Para obter mais informações, consulte SERIALIZABLE mais adiante neste artigo. HOLDLOCK se aplica apenas à tabela ou à exibição para a qual está especificada e somente durante a transação definida pela instrução usada. HOLDLOCK não pode ser usado em uma instrução SELECT que inclua a opção FOR BROWSE.

IGNORE_CONSTRAINTS

É aplicável apenas em uma instrução INSERT quando a opção BULK é usada com OPENROWSET.

Especifica que qualquer restrição da tabela é ignorada pela operação de importação em massa. Por padrão, INSERT verifica Restrições exclusivas e restrições de verificação e Restrições primárias e de chave estrangeira. Quando a opção IGNORE_CONSTRAINTS for especificada para uma operação de importação em massa, INSERT deverá ignorar essas restrições em uma tabela de destino. Observe que não é possível desabilitar as restrições UNIQUE, PRIMARY KEY ou NOT NULL.

Convém desabilitar restrições CHECK e FOREIGN KEY se os dados de entrada contiverem linhas que violam restrições. Ao desabilitar as restrições CHECK e FOREIGN KEY, será possível importar os dados e usar instruções Transact-SQL para limpar os dados.

No entanto, quando as restrições FOREIGN KEY e CHECK são ignoradas, cada restrição ignorada na tabela é marcada como is_not_trusted na exibição de catálogo sys.check_constraints ou sys.foreign_keys após a operação. Em algum ponto, verifique as restrições de toda a tabela. Se a tabela não estiver vazia antes da operação de importação em massa, o custo de revalidação da restrição poderá exceder o custo da aplicação das restrições CHECK e FOREIGN KEY aos dados incrementais.

IGNORE_TRIGGERS

É aplicável apenas em uma instrução INSERT quando a opção BULK é usada com OPENROWSET.

Especifica que qualquer gatilho definido na tabela será ignorado pela operação de importação em massa. Por padrão, INSERT aplica gatilhos.

Use IGNORE_TRIGGERS apenas se o aplicativo não depender de nenhum gatilho e se for importante maximizar o desempenho.

NOLOCK

Equivalente a READUNCOMMITTED. Para obter mais informações, consulte READUNCOMMITTED mais adiante neste artigo.

NOWAIT

Instrui o Mecanismo de Banco de Dados a retornar uma mensagem assim que um bloqueio for encontrado na tabela. NOWAIT é equivalente a especificar SET LOCK_TIMEOUT 0 para uma tabela designada. A dica NOWAIT não funciona quando a dica TABLOCK também é incluída. Para terminar uma consulta sem aguardar ao usar a dica TABLOCK, preceda a consulta com SET LOCK_TIMEOUT 0;.

PAGLOCK

Usa bloqueios de página onde bloqueios individuais são usados normalmente em linhas ou chaves ou onde um único bloqueio de tabela é usado normalmente. Por padrão, usa o modo de bloqueio adequado para a operação. Quando especificados em transações que operam no nível de isolamento de SNAPSHOT, os bloqueios de página não são usados a menos que PAGLOCK seja combinado com outras dicas de tabela que requerem bloqueios, como UPDLOCK e HOLDLOCK.

READCOMMITTED

Especifica que as operações de leitura obedecem a regras do nível de isolamento READ COMMITTED usando bloqueio ou controle de versão de linha. Se a opção READ_COMMITTED_SNAPSHOT do banco de dados for OFF, o Mecanismo de Banco de Dados vai adquirir bloqueios compartilhados conforme os dados forem lidos e vai liberar esses bloqueios quando a operação de leitura estiver concluída. Se a opção READ_COMMITTED_SNAPSHOT do banco de dados for ON, o Mecanismo de Banco de Dados não vai adquirir bloqueios e usará controle de versão de linha. Para obter mais informações sobre os níveis de isolamento, confira SET TRANSACTION ISOLATION LEVEL (Transact-SQL).

READCOMMITTEDLOCK

Especifica que operações de leitura obedecem às regras do nível de isolamento READ COMMITTED usando bloqueio. O Mecanismo de Banco de Dados adquire bloqueios compartilhados conforme os dados são lidos e libera esses bloqueios quando a operação de leitura é concluída, independentemente da configuração da opção READ_COMMITTED_SNAPSHOT do banco de dados. Para obter mais informações sobre os níveis de isolamento, confira SET TRANSACTION ISOLATION LEVEL (Transact-SQL). Esta dica não pode ser especificada na tabela de destino de uma instrução INSERT, ou o erro 4140 será retornado.

READPAST

Especifica que o Mecanismo de Banco de Dados não lê linhas bloqueadas por outras transações. Quando READPAST é especificado, os bloqueios no nível da linha são ignorados, ao contrário dos bloqueios no nível da página. Ou seja, o Mecanismo de Banco de Dados ignorará as linhas em vez de bloquear a transação atual até que os bloqueios sejam liberados. Por exemplo, suponhamos que a tabela T1 contenha uma única coluna de inteiros com os valores 1, 2, 3, 4, 5. Se a transação A alterar o valor de 3 para 8, mas ainda não foi confirmada, a instrução SELECT * FROM T1 (READPAST) produzirá os valores 1, 2, 4, 5. READPAST é usado principalmente para reduzir a contenção de bloqueio ao implementar uma fila de trabalho que usa uma tabela do SQL Server. Um leitor de fila que usa READPAST ignora entradas da fila bloqueadas por outras transações, passando para a próxima entrada disponível da fila, sem precisar esperar até que outras transações liberem seus bloqueios.

READPAST pode ser especificado para qualquer tabela referenciada em uma instrução UPDATE ou DELETE e em qualquer tabela referenciada em uma cláusula FROM. Quando especificado em uma instrução UPDATE, READPAST será aplicado apenas ao ler dados para identificar quais registros atualizar, independentemente de onde na instrução ele foi especificado. READPAST não pode ser especificado para tabelas na cláusula INTO de uma instrução INSERT. Operações de atualização ou de exclusão que usam READPAST podem ser bloqueadas ao ler chaves estrangeiras ou exibições indexadas ou ao modificar índices secundários.

READPAST pode ser especificado apenas em transações que operam nos níveis de isolamento READ COMMITTED ou REPEATABLE READ. Quando especificados em transações que operam no nível de isolamento de SNAPSHOT, READPAST devem ser combinados com outras dicas de tabela que exigem bloqueios, como UPDLOCK e HOLDLOCK.

A dica de tabela READPAST não pode ser especificada quando a opção do banco de dados READ_COMMITTED_SNAPSHOT for definida como ON e qualquer uma das seguintes condições forem verdadeiras:

• O nível de isolamento da transação da sessão é READ COMMITTED.
• A dica de tabela READCOMMITTED também é especificada na consulta.

Para especificar a dica READPAST nesses casos, remova a dica de tabela READCOMMITTED, se ela estiver presente, e inclua a dica de tabela READCOMMITTEDLOCK na consulta.

READUNCOMMITTED

Especifica que leituras sujas são permitidas. Nenhum bloqueio compartilhado é emitido para impedir que outras transações modifiquem dados lidos pela transação atual, e bloqueios exclusivos definidos por outras transações não impedem que a transação atual leia os dados bloqueados. Permitir leituras sujas pode provocar maior simultaneidade, mas à custa da leitura de modificações de dados que, em seguida, serão revertidas por outras transações. Isso pode gerar erros para sua transação, apresentar aos usuários dados que nunca foram confirmados ou fazer com que os usuários vejam registros duplicados (ou nenhum).

As sicas READUNCOMMITTED e NOLOCK se aplicam apenas a bloqueios de dados. Todas as consultas, inclusive aquelas com dicas READUNCOMMITTED e NOLOCK, adquirem bloqueios Sch-S (estabilidade de esquema) durante a compilação e a execução. Por causa disso, as consultas são bloqueadas quando uma transação simultânea mantém um bloqueio Sch-M (modificação de esquema) na tabela. Por exemplo, uma operação DDL (Linguagem de Definição de Dados) adquire um bloqueio Sch-M antes de modificar as informações do esquema da tabela. Todas as consultas simultâneas, inclusive aquelas executadas com dicas READUNCOMMITTED ou NOLOCK, serão bloqueadas ao tentar adquirir um bloqueio Sch-S. Da mesma forma, uma consulta que mantém um bloqueio Sch-S bloqueará uma transação simultânea que tentar adquirir um bloqueio Sch-M.

READUNCOMMITTED e NOLOCK não podem ser especificados para tabelas modificadas por operações de inserção, atualização ou exclusão. O otimizador de consulta do SQL Server ignora as dicas READUNCOMMITTED e NOLOCK na cláusula FROM que se aplicam à tabela de destino de uma instrução UPDATE ou DELETE.

É possível minimizar a contenção de bloqueio ao proteger transações de leituras sujas de modificações de dados não confirmadas usando:

• O nível de isolamento READ COMMITTED com a opção de banco de dados READ_COMMITTED_SNAPSHOT definida como ON.
• O nível de isolamento SNAPSHOT.

Para obter mais informações sobre os níveis de isolamento, confira SET TRANSACTION ISOLATION LEVEL (Transact-SQL).

REPEATABLEREAD

Especifica que um exame é executado com a mesma semântica de bloqueio de uma transação que está sendo executada no nível de isolamento SERIALIZABLE. Para obter mais informações sobre os níveis de isolamento, confira SET TRANSACTION ISOLATION LEVEL (Transact-SQL).

ROWLOCK

Especifica que bloqueios de linha serão usados quando os bloqueios de página ou de tabela forem usados normalmente. Quando especificados em transações que operam no nível de isolamento de SNAPSHOT, os bloqueios de linha não são usados a menos que ROWLOCK seja combinado com outras dicas de tabela que requerem bloqueios, como UPDLOCK e HOLDLOCK. ROWLOCK não pode ser usado com uma tabela que tenha um índice columnstore clusterizado. O exemplo a seguir retorna o erro 651 ao aplicativo.

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

SERIALIZABLE

Equivalente a HOLDLOCK. Torna bloqueios compartilhados mais restritivos ao mantê-los até que uma transação seja concluída, em vez de liberar o bloqueio compartilhado assim que a tabela ou página de dados requerida não seja mais necessária, quer a transação tenha sido concluída ou não. A verificação é executada com a mesma semântica da transação que está sendo executada no nível de isolamento SERIALIZABLE. Para obter mais informações sobre os níveis de isolamento, confira SET TRANSACTION ISOLATION LEVEL (Transact-SQL).

SNAPSHOT

Aplica-se a: SQL Server 2014 (12.x) e versões posteriores

A tabela com otimização de memória é acessada sob o isolamento SNAPSHOT. SNAPSHOT só pode ser usado com tabelas com otimização de memória (não com tabelas baseadas em disco), conforme visto no exemplo a seguir. Para obter mais informações, confira Introdução às tabelas com otimização de memória.

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

SPATIAL_WINDOW_MAX_CELLS = <integer_value>

Aplica-se a: SQL Server 2012 (11.x) e versões posteriores

Especifica o número máximo de células para usar para fazer um mosaico de geometria ou objeto de geografia. <integer_value> é um valor entre 1 e 8192.

Esta opção permite ajustar o tempo de execução de consulta ajustando o intercâmbio entre o tempo de execução de filtro primário e secundário. Um número maior reduz o tempo de execução de filtro secundário, mas aumenta hora de filtro de execução primária e um número menor diminui tempo de execução de filtro primário, mas aumenta a execução de filtro secundária. Para dados espaciais mais densos, um número mais alto deve gerar um tempo de execução mais rápido dando uma aproximação melhor com o filtro primário e reduzindo o tempo de execução de filtro secundário. Para dados mais esparsos, um número inferior diminui o tempo de execução do filtro primário.

Essa opção funciona para mosaicos de grade manuais e automáticos.

TABLOCK

Especifica que o bloqueio adquirido seja aplicado no nível de tabela. O tipo de bloqueio que é adquirido depende da instrução que está sendo executada. Por exemplo, uma instrução SELECT pode adquirir um bloqueio compartilhado. Ao especificar TABLOCK, o bloqueio compartilhado é aplicado à tabela inteira e não no nível de linha ou página. Se HOLDLOCK também for especificado, o bloqueio de tabela será mantido até o final da transação.

Ao importar dados para um heap usando a instrução INSERT INTO <target_table> SELECT <columns> FROM <source_table>, você pode habilitar o registro em log mínimo e bloqueio ideal da instrução, especificando a dica TABLOCK para a tabela de destino. Além disso, o modelo de recuperação do banco de dados deve ser definido como simples ou bulk-logged. A dica TABLOCK também permite inserções paralelas para heaps ou índices columnstore clusterizados. Para saber mais, confira INSERT (Transact-SQL).

Quando usado com o provedor de conjuntos de linhas em massa OPENROWSET para importar dados em uma tabela, o TABLOCK permite que vários clientes carreguem dados simultaneamente na tabela de destino com o registro em log e o bloqueio otimizados. Para obter mais informações, confira Pré-requisitos para registro em log mínimo em importação em massa.

TABLOCKX

Especifica que um bloqueio exclusivo será usado na tabela.

UPDLOCK

Especifica que bloqueios de atualização serão usados e mantidos até que a transação seja concluída. O UPDLOCK utiliza bloqueios de atualização apenas em operações de leitura no nível de linha ou de página. Se UPDLOCK for combinado com TABLOCK, ou se um bloqueio em nível de tabela for usado por outro motivo, um bloqueio (X) exclusivo será usado.

Quando UPDLOCK é especificado, as dicas em nível de isolamento READCOMMITTED e READCOMMITTEDLOCK são ignoradas. Por exemplo, se o nível de isolamento da sessão for definido como SERIALIZABLE e uma consulta especificar (UPDLOCK e READCOMMITTED), a dica READCOMMITTED será ignorada e a transação será executada usando o nível de isolamento SERIALIZABLE.

XLOCK

Especifica que bloqueios exclusivos serão usados e mantidos até que a transação seja concluída. Se especificados com ROWLOCK, PAGLOCK ou TABLOCK, os bloqueios exclusivos serão aplicados ao nível adequado de granularidade.

Comentários

As dicas de tabela serão ignoradas se a tabela não for acessada pelo plano de consulta. Isso pode ser provocado porque o otimizador opta por não acessar a tabela ou porque uma exibição indexada é acessada. No último caso, o acesso a uma exibição indexada pode ser evitado pela dica de consulta OPTION (EXPAND VIEWS).

Todas as dicas de bloqueio são propagadas para todas as tabelas e exibições que são acessadas pelo plano de consulta, incluindo tabelas e exibições referenciadas em uma exibição. Além disso, o SQL Server executa os testes de consistência de bloqueio correspondentes.

Dicas de bloqueio ROWLOCK, UPDLOCK E XLOCK que adquirem bloqueios em nível de linha colocam bloqueios em chaves de índice em vez das linhas de dados reais. Por exemplo, se uma tabela tiver um índice não clusterizado e uma instrução SELECT que usa uma dica de bloqueio for tratada por um índice de cobertura, um bloqueio será adquirido na chave do índice de cobertura em vez de na linha de dados da tabela base.

Se a tabela contiver colunas computadas por expressões ou funções que acessam colunas em outras tabelas, as dicas de tabela não serão usadas nessas tabelas e não serão propagadas. Por exemplo, uma dica de tabela NOLOCK é especificada em uma tabela na consulta. Essa tabela contém colunas computadas que são computadas por uma combinação de expressões e funções que acessam colunas de outras tabelas. As tabelas referenciadas pelas expressões e funções não usam a dica de tabela NOLOCK quando acessadas.

O SQL Server não permite mais de uma dica de tabela de cada um dos seguintes grupos para cada tabela na cláusula FROM:

• Dicas de granularidade: PAGLOCK, NOLOCK, READCOMMITTEDLOCK, ROWLOCK, TABLOCK ou TABLOCKX.
• Dicas de nível de isolamento: HOLDLOCK, NOLOCK, READCOMMITTED, REPEATABLEREAD ou SERIALIZABLE.

Dicas de índice filtrado

Um índice filtrado pode ser usado como uma dica de tabela, mas fará com que o otimizador de consulta gere o erro 8622 se ele não cobrir todas as linhas selecionadas pela consulta. O seguinte exemplo é uma dica de índice filtrado inválida. O exemplo cria o índice filtrado FIBillOfMaterialsWithComponentID e, em seguida, usa-o como uma dica de índice para uma instrução SELECT. O predicado do índice filtrado inclui linhas de dados para ComponentIDs 533, 324 e 753. O predicado da consulta também inclui linhas de dados para os ComponentIDs 533, 324 e 753, mas estende o conjunto de resultados para incluir os ComponentIDs 855 e 924 que não estão no índice filtrado. Portanto, o otimizador de consulta não pode usar a dica de índice filtrado e gera o erro 8622. Para saber mais, confira Create Filtered Indexes.

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

O otimizador de consulta não vai considerar uma dica de índice se as opções SET não tiverem os valores necessários para índices filtrados. Para obter mais informações, veja CREATE INDEX (Transact-SQL).

Usar NOEXPAND

NOEXPAND se aplica apenas a exibições indexadas. Uma exibição indexada é uma exibição com um índice clusterizado exclusivo criado nela. Se uma consulta tiver referências a colunas presentes em uma exibição indexada e em tabelas base, e o otimizador de consulta determinar que o uso da exibição indexada oferece o melhor método para a execução da consulta, o otimizador de consulta usará o índice na exibição. Essa funcionalidade é chamada de correspondência de exibição indexada. Antes do SQL Server 2016 (13.x) Service Pack 1, o uso automático de uma exibição indexada pelo otimizador de consulta era compatível apenas em edições específicas do SQL Server. Desde então, todas as edições oferecem suporte ao uso automático de uma exibição indexada. O Banco de Dados SQL do Azure e a Instância Gerenciada de SQL do Azure também oferecem suporte ao uso automático de exibições indexadas sem especificar a dica NOEXPAND.

Para obter mais informações, consulte Guia da Arquitetura de Processamento de Consultas.

Para obter uma lista dos recursos compatíveis com as edições do SQL Server no Windows, consulte:

• Edições e recursos com suporte do SQL Server 2022
• Edições e recursos com suporte do SQL Server 2019
• Edições e recursos com suporte do SQL Server 2017
• Edições e recursos com suporte do SQL Server 2016

No entanto, para que o otimizador de consulta considere exibições indexadas para correspondência ou use uma exibição indexada que é referenciada com a dica NOEXPAND, as opções SET a seguir devem ser definidas como ON.

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

¹ ARITHABORT é definido implicitamente como ON quando ANSI_WARNINGS é definido como ON. Portanto, você não precisa ajustar essa configuração manualmente.

Além disso, a opção NUMERIC_ROUNDABORT deve ser definida como OFF.

Para forçar o otimizador de consulta a usar um índice em uma exibição indexada, especifique a opção NOEXPAND. Essa dica poderá ser usada apenas se a exibição também estiver nomeada na consulta. O SQL Server não fornece uma dica para forçar o uso de uma exibição indexada específica em uma consulta que não nomeia a exibição diretamente na cláusula FROM. No entanto, o otimizador de consulta considera o uso de exibições indexadas, mesmo que elas não sejam referenciadas diretamente na consulta. O Mecanismo de Banco de Dados do SQL Server apenas criará automaticamente as estatísticas em uma exibição indexada quando uma dica de tabela NOEXPAND for usada. A omissão dessa dica pode resultar em avisos de plano de execução sobre as estatísticas ausentes que não podem ser resolvidas com a criação manual de estatísticas.

Durante a otimização de consulta, o Mecanismo de Banco de Dados usa as estatísticas de exibição que foram criadas de maneira automática ou manual quando a consulta referenciar a exibição diretamente e a dica NOEXPAND for usada.

Usar uma dica de tabela como uma dica de consulta

As dicas de tabela também podem ser especificadas como dicas de consulta usando a cláusula OPTION (TABLE HINT). É recomendável usar uma dica de tabela como uma dica de consulta apenas no contexto de um guia de plano. Para consultas ad hoc, especifique essas dicas apenas como dicas de tabela. Para obter mais informações, veja Dicas de consulta (Transact-SQL).

Permissões

As dicas KEEPIDENTITY, IGNORE_CONSTRAINTS e IGNORE_TRIGGERS requerem permissões ALTER na tabela.

Exemplos

a. Usar a dica TABLOCK para especificar um método de bloqueio

O seguinte exemplo especifica que um bloqueio compartilhado será usado na tabela Production.Product no banco de dados AdventureWorks2022 e mantido até o término da instrução UPDATE.

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

B. Usar a dica FORCESEEK para especificar uma operação de busca de índice

O exemplo a seguir usa a dica FORCESEEK sem especificar um índice para forçar o otimizador de consulta a executar uma operação de busca de índice na tabela Sales.SalesOrderDetail do banco de dados 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

O exemplo a seguir usa a dica FORCESEEK com um índice para forçar o otimizador de consulta a executar uma operação de busca de índice no índice e na coluna 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. Usar a dica FORCESCAN para especificar uma operação de verificação de índice

O exemplo a seguir usa a dica FORCESCAN para forçar o otimizador de consultas a executar uma operação de verificação na tabela Sales.SalesOrderDetail do banco de dados 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);

Conteúdo relacionado

• OPENROWSET (Transact-SQL)
• dicas (Transact-SQL)
• Dicas (Transact-SQL) – Consulta