Dicas de tabela (Transact-SQL)

Dicas de tabela substituem o comportamento padrão do otimizador de consulta durante a instrução DML (linguagem de manipulação de dados) ao 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.

Cuidado
Como o otimizador de consultas do SQL Server normalmente seleciona o melhor plano de execução para uma consulta, é recomendável que os desenvolvedores e administradores de banco de dados experientes usem as dicas apenas como último recurso.

Aplica-se a:

DELETE

INSERT

SELECT

UPDATE

MERGE

Convenções de sintaxe Transact-SQL

Sintaxe

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 ]
  Com algumas exceções, há suporte para dicas de tabela somente quando elas são especificadas com a palavra-chave WITH. Os parênteses são necessários.

  Importante

  A omissão da palavra-chave WITH é um recurso preterido e será removido em uma versão futura do Microsoft SQL Server. Especifique sempre WITH em novos trabalhos de desenvolvimento e modifique os aplicativos que no momento omitem essa palavra-chave. A separação de dicas por espaços, e não por vírgulas, é um recurso preterido e será removido em uma versão futura do Microsoft SQL Server. Especifique sempre vírgulas em novos trabalhos de desenvolvimento e modifique os aplicativos que no momento omitem vírgulas.

  As seguintes dicas de tabela são permitidas com e sem a palavra-chave WITH: NOLOCK, READUNCOMMITTED, UPDLOCK, REPEATABLEREAD, SERIALIZABLE, READCOMMITTED, FASTFIRSTROW, TABLOCK, TABLOCKX, PAGLOCK, ROWLOCK, NOWAIT, READPAST, XLOCK e NOEXPAND. Quando essas dicas de tabela forem especificadas sem a palavra-chave WITH, as dicas deverão ser especificadas sozinhas. Por exemplo, FROM t WITH (TABLOCK).. Quando especificada com outra opção, a dica deve ser especificada com a palavra-chave WITH. Por exemplo, FROM t WITH (TABLOCK, INDEX(myindex)).

  As restrições serão aplicadas quando as dicas forem usadas em consultas em bancos de dados com o nível de compatibilidade 90 e superior.

• 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, consulte Comentários.

• INDEX (index_value [,... n ] ) | INDEX = (index_value)
  A sintaxe INDEX(index_value) especifica o nome ou a ID de um ou mais índices que serão usados pelo otimizador de consulta ao processar a instrução. A sintaxe alternativa INDEX = (index_value) permite apenas um único valor de índice.

  Se existir 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. Na ausência de índices clusterizados, INDEX(0) forçará uma verificação de tabela e INDEX(1) será interpretado como um erro.

  Se forem usados vários índices em uma única lista de dicas, quaisquer 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 entre os índices, e o otimizador de consulta aplica o máximo de condições possível em cada índice que é acessado. Se a coleção de índices com dica não incluir todas as colunas referenciadas pela consulta, será executada uma busca para recuperar as colunas restantes depois que o Mecanismo de banco de dados do SQL Server recuperar todas as colunas indexadas.

  Observação
  Quando uma dica de índice que faz referência a vários índices for usada na tabela de fatos em uma junção em estrela, o otimizador ignorará a dica de índice e retornará uma mensagem de aviso. Além disso, o uso de OR de índice não é permitido em uma tabela com uma dica de índice especificada.

  No máximo 250 índices não clusterizados podem ser especificados na dica de tabela.

• KEEPIDENTITY
  Aplicável apenas em uma instrução INSERT se a opção BULK for usada com OPENROWSET.

  Especifica que os valores de identidade no arquivo de dados importado devem ser usados 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.

  Importante

  Se o arquivo de dados não contiver valores para a coluna de identidade na tabela ou na exibição e a coluna de identidade não for a última coluna da tabela, a coluna de identidade deverá ser ignorada. Para obter mais informações, consulte Usando um arquivo de formato para ignorar um campo de dados. Se uma coluna de identidade for ignorada com êxito, o otimizador de consulta atribuíra automaticamente valores exclusivos para a coluna de identidade nas linhas da tabela importada.

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

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

• KEEPDEFAULTS
  Aplicável apenas em uma instrução INSERT se a opção BULK for 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 <columns> FROM OPENROWSET(BULK...), consulte Mantendo valores nulos ou usando valores padrão durante a importação em massa.

• FASTFIRSTROW
  É equivalente à OPTION (FAST 1). Para obter mais informações, consulte dicas de consulta (Transact-SQL).

  Importante
  Esse recurso será removido na próxima versão do Microsoft SQL Server. Não utilize esse recurso em desenvolvimentos novos e modifique, assim que possível, os aplicativos que atualmente o utilizam.

• 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. A partir do SQL Server 2008 R2 SP1, parâmetros de índice também podem ser especificados. 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.

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

  • index_column_name
    É o nome da coluna de índice a ser incluída na operação de busca. O otimizador de consulta considera apenas as operações de busca de índice através do índice especificado, usando pelo menos as colunas de índice especificadas. 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 formas a seguir.

  Sintaxe	Exemplo	Descrição
  Sem um índice ou 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 diretrizes a seguir.

  • 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, consulte 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 remotos. 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 diretrizes e restrições a seguir se aplicarão.

  • 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 inclui 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.

  • A modificação da definição de índice (por exemplo, adicionando ou removendo colunas) talvez exija modificações nas consultas que referenciam 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. Para obter mais informações, consulte Diretrizes especiais para índices particionados.

  Cuidado

  A especificação de FORCESEEK com parâmetros de índice limita mais o número de planos que podem ser considerados pelo otimizador do que a especificação de FORCESEEK sem parâmetros. Isso pode resultar em um erro "Não é possível gerar o plano" em mais casos. Em uma versão futura, as modificações internas no otimizador talvez permitam a consideração de mais planos. Para obter mais informações, consulte Usando a dica de tabela FORCESEEK.

• FORCESCAN
  Apresentada no SQL Server 2008 R2 SP1, esta dica especifica que o otimizador de consulta use apenas uma operação de verificação de índice como o caminho de acesso para a 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 através 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 de 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 restrições a seguir.

  • 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 considere índices espaciais ou XML na tabela.

  • A dica não pode ser especificada para uma fonte de dados remotos.

  • 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 tópico. HOLDLOCK aplica-se 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 usada em uma instrução SELECT que inclua a opção FOR BROWSE.

• IGNORE_CONSTRAINTS
  Aplicável apenas em uma instrução INSERT se a opção BULK for usada com OPENROWSET.

  Especifica que qualquer restrição da tabela é ignorada pela operação de importação em massa. Por padrão, INSERT verifica as restrições CHECK e FOREIGN KEY. 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 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 do 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 se a opção BULK for 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 tópico.

  Observação
  Para instruções UPDATE ou DELETE: Esse recurso será removido em uma versão futura do Microsoft SQL Server. Evite usar esse recurso em desenvolvimentos novos e planeje modificar os aplicativos que atualmente o utilizam.

• 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 específica.

• 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 irá adquirir bloqueios compartilhados conforme os dados forem lidos e 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 adquirirá bloqueios e usará controle de versão de linha. Para obter mais informações sobre níveis de isolamento, consulte SET TRANSACTION ISOLATION LEVEL (Transact-SQL).

  Observação
  Para instruções UPDATE ou DELETE: Esse recurso será removido em uma versão futura do Microsoft SQL Server. Evite usar esse recurso em desenvolvimentos novos e planeje modificar os aplicativos que atualmente o utilizam.

• 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 níveis de isolamento, consulte SET TRANSACTION ISOLATION LEVEL (Transact-SQL). Esta dica não pode ser especificada na tabela de destino de uma instrução INSERT; o erro 4140 é retornado.

• READPAST
  Especifica que o Mecanismo de Banco de Dados não lê linhas bloqueadas por outras transações. Quando READPAST for especificado, os bloqueios em nível de linha serão ignorados. 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, 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 trabalhos que usa uma tabela do SQL Server. Um queue reader usando READPAST ignora entradas da fila bloqueadas por outras transações, passando para a próxima entrada 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 leitura que usam READPAST não são bloqueadas. 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 especificado em transações que operam no nível de isolamento SNAPSHOT, READPAST deverá ser combinado com outras dicas de tabela que requerem 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 condições a seguir 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). Para obter mais informações sobre leituras sujas, leituras não repetíveis e leituras fantasmas, consulte Efeitos de simultaneidade.

  Dicas de READUNCOMMITTED e de NOLOCK aplicam-se apenas a bloqueios de dados. Todas as consultas, inclusive aquelas com dicas de 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 de 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. Para obter mais informações sobre o comportamento dos bloqueios, consulte Compatibilidade de bloqueios (Mecanismo de Banco de Dados).

  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 de READUNCOMMITTED e NOLOCK na cláusula FROM que se aplicam à tabela de destino de uma instrução UPDATE ou DELETE.

  Observação
  O suporte ao uso de dicas de READUNCOMMITTED e NOLOCK na cláusula FROM que se aplicam à tabela de destino de uma instrução UPDATE ou DELETE será eliminado em uma versão futura do SQL Server. Evite usar essas dicas nesse contexto em desenvolvimentos novos e planeje modificar aplicativos que as usam atualmente.

  É 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 níveis de isolamento, consulte SET TRANSACTION ISOLATION LEVEL (Transact-SQL).

  Observação
  Se você receber a mensagem de erro 601 ao especificar READUNCOMMITTED, resolva-a como se fosse um erro de deadlock (1205) e envie novamente a instrução.

• 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 níveis de isolamento, consulte 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 SNAPSHOT, os bloqueios de linha não serão usados a menos que ROWLOCK seja combinado com outras dicas de tabela que requerem bloqueios, como UPDLOCK e HOLDLOCK.

• 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 níveis de isolamento, consulte SET TRANSACTION ISOLATION LEVEL (Transact-SQL).

• 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 <tabela_destino> SELECT <colunas> FROM <tabela_origem>, você pode habilitar o log otimizado e o bloqueio da instrução, especificando a dica TABLOCK da tabela de destino. Além disso, o modelo de recuperação do banco de dados deve ser definido como simples ou bulk-logged. Para obter mais informações, consulte INSERT (Transact-SQL).

  Quando usado com o provedor de conjuntos de linhas em massa OPENROWSET para importar dados para uma tabela, TABLOCK permitirá que vários clientes carreguem dados simultaneamente na tabela de destino com log otimizado e bloqueio. Para obter mais informações, consulte Pré-requisitos para 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. 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, 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 e essas colunas forem computadas por expressões ou funções que acessam colunas em outras tabelas, as dicas de tabela não serão usadas nessas tabelas. Isso significa que as dicas de tabela não sã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, SERIALIZABLE.

Dicas de índice filtrado

Um índice filtrado pode ser usado como 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 obter mais informações, consulte Diretrizes de criação 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

O otimizador de consulta não 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, consulte CREATE INDEX (Transact-SQL).

Usando NOEXPAND

NOEXPAND aplica-se 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 função é chamada de correspondência de exibição indexada e é suportada apenas pelas edições SQL Server Enterprise e Developer.

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

ANSI_NULLS	ANSI_WARNINGS	CONCAT_NULL_YIELDS_NULL
ANSI_PADDING	ARITHABORT1	QUOTED_IDENTIFIERS

1 ARITHABORT será definido implicitamente como ON quando ANSI_WARNINGS for 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 a usar um índice para 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 nomeie 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.

Para obter mais informações, consulte Resolvendo índices em exibições.

Usando uma dica de tabela como uma dica de consulta

Dicas de tabela também podem ser especificadas como dica de consulta usando a cláusula OPTION (TABLE HINT). Recomendamos usar uma dica de tabela como 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, consulte dicas de consulta (Transact-SQL).

Permissões

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

Exemplos

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

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

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

B. Usando 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.

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

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.

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. Usando 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 consulta a executar uma operação de verificação na tabela Sales.SalesOrderDetail.

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

Consulte também

Referência

OPENROWSET (Transact-SQL)

dicas (Transact-SQL)

dicas de consulta (Transact-SQL)

Conceitos

Dicas de bloqueio

Resolução de exibição