Nesse post vou tentar explicar de uma forma simples os diversos hints que podem ser utilizados em tabelas.
Lembrando que a sua utilização deve ser avaliada com extremo cuidado.
O que são table hints ?
Hints ou Dicas são comandos utilizados para alterar o comportamento padrão da engine do SQL Server durante a execução de comandos DML (Data Manipulation Language).
Hints podem ser utilizados para especificar a utilização de um determinado índice ou para a alteração do tipo lock que será realizado durante a execução de scripts.
| INDEX = ( index_value )
- Força a utilização de um índice especifico existente na tabela.
USE WideWorldImporters SELECT * FROM [Purchasing].[PurchaseOrders] PO INNER JOIN [Purchasing].[PurchaseOrderLines] POL WITH (INDEX = FK_Purchasing_PurchaseOrderLines_PurchaseOrderID) ON PO.PurchaseOrderID = POL.PurchaseOrderID
| INDEX ( index_value [ ,…n ] )
- Se um índice clusterizado existir, o hint WITH (INDEX(0)) irá forçar um CLUSTERED INDEX SCAN e o hint WITH (INDEX(1)) forçará um CLUSTERED INDEX SCAN OR SEEK.
- Se não existir um índice clusterizado, o hint WITH (INDEX(0)) irá forçar um TABLE SCAN e o hint WITH (INDEX(1)) irá causar erro.
USE WideWorldImporters SELECT * FROM [Purchasing].[PurchaseOrders] PO INNER JOIN [Purchasing].[PurchaseOrderLines] POL WITH (INDEX(0)) ON PO.PurchaseOrderID = POL.PurchaseOrderID USE WideWorldImporters SELECT * FROM [Purchasing].[PurchaseOrders] PO INNER JOIN [Purchasing].[PurchaseOrderLines] POL WITH (INDEX(1)) ON PO.PurchaseOrderID = POL.PurchaseOrderID
| FORCESEEK
- Força o padrão de busca a ser utilizado pelo query optimizer ser feito apenas por busca de índices para retornar os dados. Nesse caso o índice mais relevante da tabela será considerado.
USE WideWorldImporters SELECT * FROM [Purchasing].[PurchaseOrders] PO INNER JOIN [Purchasing].[PurchaseOrderLines] POL WITH (FORCESEEK) ON PO.PurchaseOrderID = POL.PurchaseOrderID
| FORCESEEK [( index_value ( index_column_name [ ,… ] ) ) ]
- Especifica para o query optimizer que a operação de busca será realizada apenas de um única forma, através do índice especificado no hint.
USE WideWorldImporters SELECT * FROM [Purchasing].[PurchaseOrders] PO WITH (FORCESEEK, INDEX(PK_Purchasing_PurchaseOrders)) INNER JOIN [Purchasing].[PurchaseOrderLines] POL ON PO.PurchaseOrderID = POL.PurchaseOrderID
| FORCESCAN
- Especifica que o query optimizer usará apenas uma operação de verificação de índice como o caminho de acesso na tabela.
USE WideWorldImporters SELECT * FROM [Purchasing].[PurchaseOrders] PO INNER JOIN [Purchasing].[PurchaseOrderLines] POL WITH (FORCESCAN) ON PO.PurchaseOrderID = POL.PurchaseOrderID <br>
| HOLDLOCK
- Previne que uma operação DML, altere a tabela que em questão.
USE WideWorldImporters BEGIN TRAN UPDATE POL SET POL.ExpectedUnitPricePerOuter = POL.ExpectedUnitPricePerOuter * 0.10 FROM [Purchasing].[PurchaseOrders] PO INNER JOIN [Purchasing].[PurchaseOrderLines] POL WITH (HOLDLOCK) ON PO.PurchaseOrderID = POL.PurchaseOrderID WHERE POL.PurchaseOrderID = 4 --COMMIT --ROLLBACK
| NOLOCK
- Evita o bloqueio da tabela durante a execução da query, semelhante ao nível de isolamento READ UMCOMMITTED. Deve ser utilizado com cautela pois pode retornar dados sujos.
USE WideWorldImporters SELECT * FROM [Purchasing].[PurchaseOrders] PO WITH (NOLOCK) INNER JOIN [Purchasing].[PurchaseOrderLines] POL ON PO.PurchaseOrderID = POL.PurchaseOrderID
| NOWAIT
- Instrui o database engine a retornar uma mensagem de erro assim que um lock é encontrado em uma tabela.
Semelhante ao comando SET LOCK_TIMEOUT 0;
USE WideWorldImporters BEGIN TRAN UPDATE POL SET POL.ExpectedUnitPricePerOuter = POL.ExpectedUnitPricePerOuter * 0.10 FROM [Purchasing].[PurchaseOrders] PO INNER JOIN [Purchasing].[PurchaseOrderLines] POL WITH (NOWAIT) ON PO.PurchaseOrderID = POL.PurchaseOrderID WHERE POL.PurchaseOrderID = 2 --COMMIT --ROLLBACK
| PAGLOCK
- Realiza bloqueio ao nível de página.
USE WideWorldImporters SELECT * FROM [Purchasing].[PurchaseOrders] PO INNER JOIN [Purchasing].[PurchaseOrderLines] POL WITH (PAGLOCK) ON PO.PurchaseOrderID = POL.PurchaseOrderID WHERE POL.PurchaseOrderID = 4
| READCOMMITTED
- Especifica que a consulta irá obedecer aos mesmos princípios do nível de isolamento READ COMMITTED, esse nível utiliza bloqueios ou versionamento de linhas para a leitura dos dados, afim de evitar dados que não tenham sido comitados.
USE WideWorldImporters SELECT * FROM [Purchasing].[PurchaseOrders] PO INNER JOIN [Purchasing].[PurchaseOrderLines] POL WITH (READCOMMITTED) ON PO.PurchaseOrderID = POL.PurchaseOrderID WHERE POL.PurchaseOrderID = 4
| READCOMMITTEDLOCK
- Especifica que a consulta irá obedecer aos mesmos princípios do nível de isolamento READ COMMITTED, esse nível utiliza bloqueios para a leitura dos dados, afim de evitar dados que não tenham sido comitados.
USE WideWorldImporters SELECT * FROM [Purchasing].[PurchaseOrders] PO INNER JOIN [Purchasing].[PurchaseOrderLines] POL WITH (READCOMMITTEDLOCK) ON PO.PurchaseOrderID = POL.PurchaseOrderID WHERE POL.PurchaseOrderID = 4
| READPAST
- Esse hint permite que sejam ‘puladas’ as linhas locadas durante o retorno dos dados, ou seja, o seu resultado pode vir com linhas faltando.
USE WideWorldImporters SELECT * FROM [Purchasing].[PurchaseOrders] PO INNER JOIN [Purchasing].[PurchaseOrderLines] POL WITH (READPAST) ON PO.PurchaseOrderID = POL.PurchaseOrderID WHERE POL.PurchaseOrderID = 4
| READUNCOMMITTED
- Sua atuação é igual ao hint NOLOCK.
USE WideWorldImporters SELECT * FROM [Purchasing].[PurchaseOrders] PO INNER JOIN [Purchasing].[PurchaseOrderLines] POL WITH (READUNCOMMITTED) ON PO.PurchaseOrderID = POL.PurchaseOrderID WHERE POL.PurchaseOrderID = 4
| REPEATABLEREAD
- Esse hint causa um lock em todos os registros envolvidos na transação, impedindo a sua atualização, porém pode retornar leituras fantasmas durante operações de Inserção de dados.
USE WideWorldImporters SELECT * FROM [Purchasing].[PurchaseOrders] PO INNER JOIN [Purchasing].[PurchaseOrderLines] POL WITH (REPEATABLEREAD) ON PO.PurchaseOrderID = POL.PurchaseOrderID WHERE POL.PurchaseOrderID = 4
| ROWLOCK
- Especifica que o lock será realizado por linha.
USE WideWorldImporters BEGIN TRAN UPDATE POL SET POL.ExpectedUnitPricePerOuter = POL.ExpectedUnitPricePerOuter * 0.10 FROM [Purchasing].[PurchaseOrders] PO INNER JOIN [Purchasing].[PurchaseOrderLines] POL WITH (ROWLOCK) ON PO.PurchaseOrderID = POL.PurchaseOrderID WHERE POL.PurchaseOrderID = 2 --COMMIT --ROLLBACK
| SERIALIZABLE
- Semelhante ao HOLDLOCK, porém torna os locks mais restritivos ao longo da transação. Previne a leitura de dados sujos.
USE WideWorldImporters SELECT * FROM [Purchasing].[PurchaseOrders] PO INNER JOIN [Purchasing].[PurchaseOrderLines] POL WITH (SERIALIZABLE) ON PO.PurchaseOrderID = POL.PurchaseOrderID WHERE POL.PurchaseOrderID = 4
| SNAPSHOT
- Esse hint só funciona com memory optimized tables e a sua utilização está atrelada ao Transaction Initiation Mode [ Explicit Mode ].
| SPATIAL_WINDOW_MAX_CELLS = integer
- Utilizado para especificar o número máximo de células em um mosaico geométrico.
| TABLOCK
- Determina que o tipo de lock a ser realizado será aplicado para toda a tabela, porém existem casos em que instruções selects podem adquirir um bloqueio compartilhado.
USE WideWorldImporters SELECT * FROM [Purchasing].[PurchaseOrders] PO INNER JOIN [Purchasing].[PurchaseOrderLines] POL WITH (TABLOCK) ON PO.PurchaseOrderID = POL.PurchaseOrderID WHERE POL.PurchaseOrderID = 4
| TABLOCKX
- Determina que o tipo de lock a ser realizado será Exclusivo do início ao fim da transação.
USE WideWorldImporters SELECT * FROM [Purchasing].[PurchaseOrders] PO INNER JOIN [Purchasing].[PurchaseOrderLines] POL WITH (TABLOCKX) ON PO.PurchaseOrderID = POL.PurchaseOrderID WHERE POL.PurchaseOrderID = 4
| UPDLOCK
- Especifica que bloqueios de atualização serão mantidos até que o processo seja concluído.
USE WideWorldImporters BEGIN TRAN UPDATE POL SET POL.ExpectedUnitPricePerOuter = POL.ExpectedUnitPricePerOuter * 0.10 FROM [Purchasing].[PurchaseOrders] PO INNER JOIN [Purchasing].[PurchaseOrderLines] POL WITH (UPDLOCK) ON PO.PurchaseOrderID = POL.PurchaseOrderID WHERE POL.PurchaseOrderID = 2 --COMMIT --ROLLBACK
| XLOCK
- Especifica que bloqueios exclusivos serão usados e mantidos até que a transação seja concluída.
USE WideWorldImporters SELECT * FROM [Purchasing].[PurchaseOrders] PO INNER JOIN [Purchasing].[PurchaseOrderLines] POL WITH (XLOCK) ON PO.PurchaseOrderID = POL.PurchaseOrderID WHERE POL.PurchaseOrderID = 4