Dicas de tabela (Transact-SQL)

Aplica-se a:SQL ServerBanco de Dados SQL do AzureInstância Gerenciada de SQL do AzureBanco de Dados SQL no Microsoft Fabric

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 de 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 as dicas 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 a dica é especificada com outra opção, a dica deve 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, consulte Use 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 somente 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 KEEPIDENTITY não for especificado, os valores de identidade desta coluna serão verificados, mas não importados, e o otimizador de consulta atribuirá automaticamente valores exclusivos com base nos valores de propagação e 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...), consulte 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, consulte DBCC CHECKIDENT.

KEEPDEFAULTS

Aplicável somente em uma instrução INSERT quando a opção BULK é usada com OPENROWSET.

Especifica a inserção do valor padrão de uma coluna de tabela, se houver, em vez de NULL quando o registro de dados não tem um valor para a coluna.

Para obter um exemplo que usa essa dica em uma instrução INSERT ... SELECT * FROM OPENROWSET(BULK...), consulte Manter valores nulos ou 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 mais colunas, se 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, 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 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 não resultar em nenhum plano encontrado, 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, UPDATEou 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 clusterizado que são automaticamente incluídas 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.
• Modificar a definição de índice (por exemplo, adicionando ou removendo colunas) pode exigir 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 verificar caminhos de acesso 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 depois que as partições são eliminadas por meio 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, UPDATEou 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 posteriormente neste artigo. HOLDLOCK se aplica somente à tabela ou exibição na qual ela é especificada e somente pela duração da transação definida pela instrução na qual ela é usada. HOLDLOCK não pode ser usado em uma instrução SELECT que inclua a opção FOR BROWSE.

IGNORE_CONSTRAINTS

Aplicável somente em uma instrução INSERT quando a opção BULK é usada com OPENROWSET.

Especifica que a operação de importação em massa ignora quaisquer restrições na tabela. Por padrão, INSERT verifica restrições exclusivas e verifica as restrições e restrições de chave primária e estrangeira. Quando IGNORE_CONSTRAINTS é especificado para uma operação de importação em massa, INSERT deve ignorar essas restrições em uma tabela de destino. Não é possível desabilitar restrições UNIQUE, PRIMARY KEYou NOT NULL.

Talvez você queira desabilitar restrições de CHECK e FOREIGN KEY se os dados de entrada contiver linhas que violam restrições. Ao desabilitar as restrições de CHECK e FOREIGN KEY, você pode importar os dados e, em seguida, usar instruções Transact-SQL para limpar os dados.

No entanto, quando as restrições CHECK e FOREIGN KEY 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 de aplicação de restrições de CHECK e FOREIGN KEY aos dados incrementais.

IGNORE_TRIGGERS

Aplicável somente 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 somente se o aplicativo não depender de nenhum gatilho e maximizar o desempenho for importante.

NOLOCK

Equivalente a READUNCOMMITTED. Para obter mais informações, consulte READUNCOMMITTED posteriormente 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 estão em conformidade com as regras do nível de isolamento READ COMMITTED usando bloqueio ou controle de versão de linha. Se a opção de banco de dados READ_COMMITTED_SNAPSHOT for OFF, o Mecanismo de Banco de Dados adquire bloqueios compartilhados à medida que os dados são lidos e libera esses bloqueios quando a operação de leitura é concluída. Se a opção de banco de dados READ_COMMITTED_SNAPSHOT for ON, o Mecanismo de Banco de Dados não adquirirá bloqueios e usará o controle de versão de linha. Para obter mais informações sobre níveis de isolamento, consulte SET TRANSACTION ISOLATION LEVEL.

READCOMMITTEDLOCK

Especifica que as operações de leitura estão em conformidade com as regras do nível de isolamento READ COMMITTED usando o 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. Essa 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 é 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 especificada para qualquer tabela referenciada em uma instrução UPDATE ou DELETE e qualquer tabela referenciada em uma cláusula FROM. Quando especificado em uma instrução UPDATE, READPAST é aplicado somente ao ler dados para identificar quais registros atualizar, independentemente de onde na instrução é especificada. READPAST não pode ser especificado para tabelas na cláusula INTO de uma instrução INSERT. As operações de atualização ou exclusão que usam READPAST podem ser bloqueadas ao ler chaves estrangeiras ou exibições indexadas ou ao modificar índices secundários.

READPAST só pode ser especificado 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 READ_COMMITTED_SNAPSHOT banco de dados é definida como ON e qualquer uma das seguintes condições é verdadeira:

• 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 duas vezes (ou não).

As sicas READUNCOMMITTED e NOLOCK se aplicam apenas a bloqueios de dados. Todas as consultas, incluindo consultas com dicas de READUNCOMMITTED e NOLOCK, adquirem bloqueios de Sch-S (estabilidade do 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, incluindo consultas em execução com dicas de READUNCOMMITTED ou NOLOCK, são bloqueadas ao tentar adquirir um bloqueio de 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 de READUNCOMMITTED e NOLOCK na cláusula FROM que se aplicam à tabela de destino de uma instrução UPDATE ou DELETE.

Você pode minimizar a contenção de bloqueio ao proteger as transações contra leituras sujas de modificações de dados não confirmadas usando qualquer uma das seguintes opções:

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

Para obter mais informações sobre níveis de isolamento, consulte SET TRANSACTION ISOLATION LEVEL.

REPEATABLEREAD

Especifica que uma verificação é executada com a mesma semântica de bloqueio que uma transação em execução em REPEATABLE READ nível de isolamento. Para obter mais informações sobre níveis de isolamento, consulte SET TRANSACTION ISOLATION LEVEL.

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

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 OUTER 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 obter mais informações, consulte INSERT.

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, consulte pré-requisitos para registro em log mínimo emde 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 causado pelo otimizador que opta por não acessar a tabela ou porque uma exibição indexada é acessada. Neste último caso, o acesso a uma exibição indexada pode ser evitado usando a 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, UPDLOCKe XLOCK que adquirem bloqueios no nível de linha podem colocar 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 usando uma dica de bloqueio for tratada por um índice de cobertura, um bloqueio será adquirido na chave de índice no índice de cobertura e não na linha de dados na 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 faz com que o otimizador de consulta gere o erro 8622 se ele não abrange todas as linhas selecionadas pela consulta. O seguinte exemplo é uma dica de índice filtrado inválida. O exemplo cria o índice filtrado FIBillOfMaterialsWithComponentID e o usa 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 Criar í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

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

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) com o Service Pack 1, o uso automático de uma exibição indexada pelo otimizador de consulta só tem suporte em edições específicas do SQL Server. No SQL Server 2016 (13.x) com o Service Pack 1 e versões posteriores, todas as edições dão 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 referenciada com a dica NOEXPAND, as opções de SET a seguir devem ser definidas como ON.

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

¹ARITHABORT é implicitamente definido 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 uma exibição indexada específica a ser usada 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. O Mecanismo de Banco de Dados do SQL Server só cria estatísticas automaticamente em uma exibição indexada quando uma dica de tabela NOEXPAND é 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

Dicas de tabela também podem ser especificadas como uma dica 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, consulte dicas de consulta.

Permissões

As dicas KEEPIDENTITY, IGNORE_CONSTRAINTSe IGNORE_TRIGGERS exigem permissões ALTER na tabela.

Exemplos

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

O exemplo a seguir especifica que um bloqueio compartilhado é feito na tabela Production.Product no banco de dados AdventureWorks2022 e é mantido até o final 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 de consulta (Transact-SQL)