Dicas de mesa (Transact-SQL)

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

As 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 pesquisa 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:

• EXCLUIR
• INSERIR
• SELECIONE
• ATUALIZAÇÃO
• MESCLAR

Transact-SQL convenções de sintaxe

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

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

Com algumas exceções, as dicas de tabela são suportadas na cláusula FROM somente quando as dicas são especificadas com a palavra-chave WITH. As dicas de tabela também devem ser especificadas entre 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, SNAPSHOTe NOEXPAND. Quando essas dicas de tabela são especificadas sem a palavra-chave WITH, as dicas devem ser especificadas sozinhas. Por exemplo:

FROM t (TABLOCK)

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

FROM t WITH (TABLOCK, INDEX(myindex))

Recomendamos o uso de vírgulas entre as dicas de tabela.

NÃOEXPANDIR

Especifica que as exibições indexadas não são expandidas para acessar tabelas subjacentes quando o otimizador de consulta processa a consulta. O otimizador de consulta trata a exibição como uma tabela com índice clusterizado. NOEXPAND aplica-se apenas a vistas indexadas. Para obter mais informações, consulte Usar NOEXPAND.

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

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

Se existir um índice clusterizado, INDEX(0) força uma verificação de índice clusterizado e INDEX(1) força uma verificação ou pesquisa de índice clusterizado. Se nenhum índice clusterizado existir, INDEX(0) força uma verificação de tabela e INDEX(1) é interpretado como um erro.

Se vários índices forem usados em uma única lista de dicas, as duplicatas serão ignoradas e o restante dos índices listados será usado para recuperar as linhas da tabela. A ordem dos índices na dica de índice é significativa. Uma dica de índice múltiplo também impõe o índice ANDing, e o otimizador de consulta aplica o maior número possível de condições em cada índice acessado. Se a coleção de índices sugeridos não incluir todas as colunas referenciadas 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.

MANTERIDENTIDADE

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 devem ser usados para a coluna de identidade. Se KEEPIDENTITY não for especificado, os valores de identidade para esta coluna serão verificados, mas não importados, e o otimizador de consulta atribuirá automaticamente valores exclusivos com base nos valores de semente 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 nulos ou valores padrão durante a importação em massa (SQL Server).

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

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

• index_value

  O nome do índice ou o valor da ID do índice. O ID de índice 0 (heap) não pode ser especificado. Para retornar o nome ou ID do índice, consulte o sys.indexes exibição de catálogo.

• 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. No entanto, você pode obter maior controle sobre o caminho de acesso usado pelo otimizador de consulta especificando o índice a ser procurado 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 clusterizadas além das colunas especificadas.

A dica FORCESEEK pode ser especificada das seguintes maneiras.

Sintaxe	Exemplo	Descrição
Sem um índice ou INDEX dica	FROM dbo.MyTable WITH (FORCESEEK)	O otimizador de consulta considera apenas operações de busca de índice para acessar a tabela ou exibição por meio de qualquer índice relevante.
Combinado com uma dica INDEX	FROM dbo.MyTable WITH (FORCESEEK, INDEX (MyIndex))	O otimizador de consulta considera apenas operações de busca de índice para acessar a tabela ou exibição por meio do índice especificado.
Parametrizado especificando um índice e colunas de índice	FROM dbo.MyTable WITH (FORCESEEK (MyIndex (col1, col2, col3)))	O otimizador de consulta considera apenas 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 de NOEXPAND também deve ser especificada.
• A dica pode ser aplicada no máximo uma vez por tabela ou visualizaçã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 resultar em nenhum plano sendo encontrado, o erro 8622 será retornado.

Quando FORCESEEK é especificado com parâmetros de índice, aplicam-se as seguintes diretrizes e 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 especificada em combinação com uma dica INDEX ou outra dica FORCESEEK.
• Pelo menos uma coluna deve ser especificada e deve ser a coluna de chave principal.
• Colunas de índice adicionais podem ser especificadas, no entanto, as colunas principais não podem ser ignoradas. Por exemplo, se o índice especificado contiver as colunas de chave a, be 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 dos nomes de colunas especificados na dica deve corresponder à ordem das colunas no índice referenciado.
• As colunas que não estão na definição de chave de índice não podem ser especificadas. Por exemplo, em um índice não clusterizado, somente 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 otimizado para memória xVelocity não pode ser especificado como um parâmetro index. 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 quaisquer í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 adicionada implicitamente pelo SQL Server não pode ser especificada na dica de FORCESEEK.

FORÇASCAN

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

Especifica que o otimizador de consulta usa apenas uma operação de verificação de índice como o caminho de acesso à tabela ou exibição referenciada. A dica FORCESCAN pode ser útil para consultas nas quais o otimizador subestima 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 pequena 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 varredura através do índice especificado, ao acessar a tabela referenciada. FORCESCAN pode ser especificado com a dica de índice INDEX(0) 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 de predicados 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 quaisquer í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.

BLOQUEIO

Equivalente a SERIALIZABLE. Para obter mais informações, consulte SERIALIZABLE mais adiante neste artigo. HOLDLOCK aplica-se apenas à tabela ou vista para a qual foi especificado e apenas durante a duração da transação definida pela instrução em que é utilizada. 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 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 desativar UNIQUE, PRIMARY KEYou NOT NULL restrições.

Talvez você queira desabilitar as restrições de CHECK e FOREIGN KEY se os dados de entrada contiverem linhas que violam restrições. Ao desativar 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 de 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 momento, você deve verificar as restrições em toda a mesa. Se a tabela não estivesse vazia antes da operação de importação em massa, o custo de revalidação da restrição poderia exceder o custo de aplicar 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 todos os gatilhos definidos na tabela são ignorados pela operação de importação em massa. Por padrão, INSERT aplica gatilhos.

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

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 específica. A dica NOWAIT não funciona quando a dica TABLOCK também está incluída. Para encerrar uma consulta sem esperar ao usar a dica de TABLOCK, prefacie a consulta com SET LOCK_TIMEOUT 0; em vez disso.

PAGLOCK

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

READCOMMITTED

Especifica que as operações de leitura estão em conformidade com as regras para o 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 estiver OFF, o Mecanismo de Banco de Dados adquirirá bloqueios compartilhados à medida que os dados são lidos e liberará esses bloqueios quando a operação de leitura for concluída. Se a opção de banco de dados READ_COMMITTED_SNAPSHOT estiver 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 para o nível de isolamento READ COMMITTED usando bloqueio. 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, independentemente da configuração da opção READ_COMMITTED_SNAPSHOT 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, mas os bloqueios no nível da página não são ignorados. Ou seja, o Mecanismo de Banco de Dados ignora as linhas em vez de bloquear a transação atual até que os bloqueios sejam liberados. Por exemplo, suponha que a tabela T1 contém uma única coluna inteira com os valores de 1, 2, 3, 4, 5. Se a transação A alterar o valor de 3 para 8, mas ainda não tiver sido confirmada, SELECT * FROM T1 (READPAST) obterá 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 filas que usa READPAST ignora entradas de fila bloqueadas por outras transações para a próxima entrada de fila disponível, sem ter que esperar até que as outras transações liberem seus bloqueios.

READPAST pode ser especificado 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 devem ser atualizados, independentemente de onde na instrução ele é especificado. READPAST não pode ser especificado para tabelas na cláusula INTO de uma instrução INSERT. Atualize ou exclua operações que usam READPAST podem bloquear 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 especificado em transações que operam no nível de isolamento SNAPSHOT, READPAST deve ser combinado 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 de banco de dados READ_COMMITTED_SNAPSHOT está definida como ON e uma das seguintes condições for 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 de READPAST nesses casos, remova a dica de tabela READCOMMITTED, se 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 os 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 causar maior simultaneidade, mas ao custo da leitura de modificações de dados que sã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 vejam de todo).

READUNCOMMITTED e NOLOCK dicas aplicam-se apenas a bloqueios de dados. Todas as consultas, incluindo consultas com dicas de READUNCOMMITTED e NOLOCK, adquirem bloqueios de Sch-S (estabilidade de esquema) durante a compilação e execução. Por isso, as consultas são bloqueadas quando uma transação simultânea mantém um bloqueio de Sch-M (modificação de esquema) na tabela. Por exemplo, uma operação DDL (linguagem de definição de dados) adquire um bloqueio de Sch-M antes de modificar as informações de esquema da tabela. Todas as consultas simultâneas, incluindo consultas executadas com READUNCOMMITTED ou NOLOCK dicas, são bloqueadas ao tentar adquirir um bloqueio Sch-S. Por outro lado, uma consulta que contém um bloqueio de Sch-S bloqueia uma transação simultânea que tenta adquirir um bloqueio de 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 enquanto protege as transações contra leituras sujas de modificações de dados não confirmadas usando 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 SNAPSHOT nível de isolamento.

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

LEITURA REPETÍVEL

Especifica que uma verificação é executada com a mesma semântica de bloqueio de 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 os bloqueios de linha são feitos quando os bloqueios de página ou tabela são normalmente feitos. Quando especificado em transações que operam no nível de isolamento SNAPSHOT, os bloqueios de linha não são aceitos, a menos que ROWLOCK seja combinado com outras dicas de tabela que exijam 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 erro 651 para o aplicativo.

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

SERIALIZÁVEL

Equivalente a HOLDLOCK. Torna os bloqueios compartilhados mais restritivos, mantendo-os até que uma transação seja concluída, em vez de liberar o bloqueio compartilhado assim que a tabela ou página de dados necessária não for mais necessária, independentemente de a transação ter sido concluída ou não. A verificação é realizada com a mesma semântica de uma transação em execução no nível de isolamento SERIALIZABLE. Para obter mais informações sobre níveis de isolamento, consulte SET TRANSACTION ISOLATION LEVEL.

INSTANTÂNEO

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

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

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 a serem usadas para tesselar um objeto de geometria ou geografia. <integer_value> é um valor entre 1 e 8192.

Essa opção permite o ajuste fino do tempo de execução da consulta ajustando a compensação entre o tempo de execução do filtro primário e secundário. Um número maior reduz o tempo de execução do filtro secundário, mas aumenta o tempo do filtro de execução primária e um número menor diminui o tempo de execução do filtro primário, mas aumenta a execução do filtro secundário. Para dados espaciais mais densos, um número maior deve produzir um tempo de execução mais rápido, proporcionando uma melhor aproximação com o filtro primário e reduzindo o tempo de execução do filtro secundário. Para dados mais esparsos, um número menor diminui o tempo de execução do filtro primário.

Esta opção funciona tanto para tesselagens de grelha manuais como automáticas.

TABLOCK

Especifica que o bloqueio adquirido é aplicado no nível da tabela. O tipo de bloqueio 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 a toda a tabela em vez de no nível de linha ou página. Se HOLDLOCK também for especificado, o bloqueio da tabela será mantido até o final da transação.

Ao importar dados para uma pilha usando a instrução INSERT INTO <target_table> SELECT <columns> FROM <source_table>, você pode habilitar o log mínimo e o bloqueio ideal para a instrução especificando a dica de TABLOCK para a tabela de destino. Além disso, o modelo de recuperação do banco de dados deve ser definido como simples ou registrado em massa. A dica TABLOCK também permite inserções paralelas para heaps ou índices columnstore clusterizados. Para obter mais informações, consulte INSERIR.

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

TABLOCKX

Especifica que um bloqueio exclusivo é colocado na mesa.

UPDLOCK

Especifica que os bloqueios de atualização devem ser tomados e mantidos até que a transação seja concluída. UPDLOCK usa bloqueios de atualização para operações de leitura somente no nível da linha ou da página. Se UPDLOCK for combinado com TABLOCK, ou um bloqueio no nível da tabela for tomado por algum outro motivo, um bloqueio exclusivo (X) será tomado em vez disso.

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

XLOCK

Especifica que bloqueios exclusivos devem ser tomados e mantidos até que a transação seja concluída. Se especificado com ROWLOCK, PAGLOCKou TABLOCK, as fechaduras exclusivas aplicam-se 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 optar por não acessar a tabela ou porque uma exibição indexada é acessada. Neste último caso, o acesso a uma vista indexada pode ser impedido utilizando a dica de consulta OPTION (EXPAND VIEWS).

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

As dicas de bloqueio ROWLOCK, UPDLOCKe XLOCK que adquirem bloqueios no nível da 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 manipulada por um índice de cobertura, um bloqueio será adquirido na chave de índice no índice de cobertura em vez de na linha de dados na tabela base.

Se uma tabela contiver colunas computadas que são 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. Esta tabela tem colunas computadas que são calculadas por uma combinação de expressões e funções que acessam colunas em outra tabela. 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, TABLOCKou TABLOCKX.
• Dicas de nível de isolamento: HOLDLOCK, NOLOCK, READCOMMITTED, REPEATABLEREAD, SERIALIZABLE.

Dicas de índice filtradas

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 cobrir todas as linhas selecionadas pela consulta. A seguir está um exemplo de uma dica de índice filtrada inválida. O exemplo cria a FIBillOfMaterialsWithComponentID de índice filtrada e, em seguida, usa-a como uma dica de índice para uma instrução SELECT. O predicado de índice filtrado inclui linhas de dados para ComponentIDs 533, 324 e 753. O predicado de consulta também inclui linhas de dados para ComponentIDs 533, 324 e 753, mas estende o conjunto de resultados para incluir 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 filtrada 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.

Use NOEXPAND

NOEXPAND aplica-se apenas a vistas indexadas. Um modo de exibição indexado é um modo de exibição com um índice clusterizado exclusivo criado nele. Se uma consulta contiver referências a colunas que estão presentes em um modo de exibição indexado e tabelas base, e o otimizador de consulta determinar que o uso do modo de exibição indexado fornece o melhor método para executar a consulta, o otimizador de consulta usará o índice no modo de exibição. Essa funcionalidade é chamada de exibição indexada correspondente. Antes do SQL Server 2016 (13.x) com Service Pack 1, o uso automático de um modo de exibição indexado pelo otimizador de consulta era suportado apenas em edições específicas do SQL Server. No SQL Server 2016 (13.x) com Service Pack 1 e versões posteriores, todas as edições oferecem suporte ao uso automático de um modo de exibição indexado. O Banco de Dados SQL do Azure e a Instância Gerenciada SQL do Azure também oferecem suporte ao uso automático de modos de exibição indexados sem especificar a dica NOEXPAND.

Para obter mais informações, consulte Guia de arquitetura de processamento de consultas.

Para obter uma lista de recursos suportados pelas 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 modos de exibição indexados para correspondência ou use um modo de exibição indexado referenciado com a dica de NOEXPAND, as seguintes opções de SET 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 está definido como ON. Portanto, não é necessário ajustar manualmente essa configuração.

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

Para forçar o otimizador de consulta a usar um índice para uma exibição indexada, especifique a opção NOEXPAND. Essa dica só pode ser usada se o modo de exibição também for nomeado na consulta. O SQL Server não fornece uma dica para forçar um modo de exibição indexado específico a ser usado em uma consulta que não nomeia o modo de 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 automaticamente estatísticas em um modo de exibição indexado quando uma dica de tabela NOEXPAND é usada. Omitir essa dica pode levar a avisos do plano de execução sobre estatísticas ausentes que não podem ser resolvidas criando estatísticas manualmente.

Durante a otimização da consulta, o Mecanismo de Banco de Dados usa estatísticas de exibição que foram criadas automática ou manualmente quando a consulta faz referência à exibição diretamente e a dica de NOEXPAND é 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). Recomendamos usar uma dica de tabela como uma dica de consulta somente no contexto de um guia de plano de . 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 de KEEPIDENTITY, IGNORE_CONSTRAINTSe IGNORE_TRIGGERS exigem permissões de ALTER na tabela.

Exemplos

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

O exemplo a seguir especifica que um bloqueio compartilhado é tomado 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. Use a dica FORCESEEK para especificar uma operação de busca de índice

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

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. Use 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 no 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)