Dicas de tabela (Transact-SQL)

Sintaxe

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

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

<table_hint_limited> ::=
{
    KEEPIDENTITY 
  | KEEPDEFAULTS 
  | FASTFIRSTROW 

  | HOLDLOCK 
  | IGNORE_CONSTRAINTS 
  | IGNORE_TRIGGERS 
  | NOWAIT
  | PAGLOCK 
  | READCOMMITTED 
  | READCOMMITTEDLOCK 
  | READPAST 
  | REPEATABLEREAD 
  | ROWLOCK 
  | SERIALIZABLE 
  | TABLOCK 
  | TABLOCKX 
  | UPDLOCK 
  | XLOCK 
} 

Argumentos

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

  Importante
  A omissão da palavra-chave WITH é um recurso preterido: 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.

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

  Importante
  A separação de dicas com espaços em vez de vírgulas não é mais um recurso aceito: Esse recurso será removido em uma versão futura de Microsoft SQL Server. Não utilize esse recurso em desenvolvimentos novos e planeje modificar, assim que possível, os aplicativos que atualmente o utilizam.

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

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

  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.

  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 se a opção BULK for 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.

  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 * 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
  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 referenciada na consulta.

  FORCESEEK aplica-se a operações de busca de índice clusterizado e não clusterizado. Pode ser especificada para qualquer tabela ou exibição da cláusula FROM de uma instrução SELECT e da cláusula FROM <table_source> de uma instrução UPDATE, MERGE ou DELETE.

  FORCESEEK pode ser especificada com ou sem uma dica INDEX. Combinado com uma dica de índice, o otimizador de consulta considera apenas os caminhos de acesso de busca através do índice especificado. Se FORCESEEK impedir a localização de um plano, o erro 8622 será retornado. Para obter mais informações, consulte Usando a dica de tabela 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).

• READPAST
  Especifica que o Mecanismo de Banco de Dados não lê linhas bloqueadas por outras transações. Na maioria das circunstâncias, isso também é válido para páginas. Quando READPAST for especificado, os bloqueios em nível de linha e de página serão ignorados. Ou seja, o Mecanismo de Banco de Dados ignorará as linhas ou páginas 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 um bloqueio compartilhado será usado na tabela mantida até o término da instrução. Se HOLDLOCK também estiver especificado, o bloqueio de tabela compartilhado será mantido até o término 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.

• 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, ROWLOCK, TABLOCK ou TABLOCKX.

• Dicas de nível de isolamento: HOLDLOCK, NOLOCK, READCOMMITTED, REPEATABLEREAD, SERIALIZABLE.

Dicas de índice filtrado

USE AdventureWorks;
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

Usando NOEXPAND

Usando uma dica de tabela como uma dica de consulta

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

USE AdventureWorks;
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

USE AdventureWorks;
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

Consulte também

Referência

Conceitos