Compartilhar via

sp_indexoption (Transact-SQL)

  • Artigo

Aplica-se: SQL Server

Define os valores da opção de bloqueio para índices clusterizados e não clusterizados definidos pelo usuário ou tabelas sem índice clusterizado.

O Mecanismo de Banco de Dados do SQL Server faz escolhas automaticamente de bloqueio em nível de página, linha ou tabela. Você não precisa definir essas opções manualmente. sp_indexoption é fornecido para usuários experientes que sabem com certeza que um determinado tipo de bloqueio é sempre apropriado.

Importante

Esse recurso será removido em uma versão futura do SQL Server. Evite usar esse recurso em desenvolvimentos novos e planeje modificar os aplicativos que atualmente o utilizam. Em vez disso, use ALTER INDEX.

Convenções de sintaxe de Transact-SQL

Sintaxe

sp_indexoption
    [ @IndexNamePattern = ] N'IndexNamePattern'
    , [ @OptionName = ] 'OptionName'
    , [ @OptionValue = ] 'OptionValue'
[ ; ]

Argumentos

@IndexNamePattern [ = ] N'IndexNamePattern'

O nome qualificado ou não qualificado de uma tabela ou índice definido pelo usuário. @IndexNamePattern é nvarchar(1035), sem padrão. As aspas são necessárias somente se um nome de índice ou tabela qualificado for especificado. Se um nome de tabela totalmente qualificado, incluindo um nome de banco de dados, for fornecido, o nome de banco de dados deve ser o nome do banco de dados atual. Se um nome de tabela for especificado sem-índice, o valor de opção especificado será definido para todos os índices nessa tabela e para a própria tabela se não houver um índice clusterizado.

@OptionName [ = ] 'Nome da opção'

Um nome de opção de índice. @OptionName é varchar(35) e pode ser um dos seguintes valores.

Valor Descrição
AllowRowLocks Quando TRUE, bloqueios de linha são permitidos ao acessar o índice. O Mecanismo de Banco de Dados determina quando os bloqueios de linha são usados. Quando FALSE, os bloqueios de linha não são usados. O padrão é TRUE.
AllowPageLocks Quando TRUE, bloqueios de página são permitidos ao acessar o índice. O Mecanismo de Banco de Dados determina quando os bloqueios de página são usados. Quando FALSE, os bloqueios de página não são usados. O padrão é TRUE.
DisAllowRowLocks Quando TRUE, os bloqueios de linha não são usados. Quando FALSE, bloqueios de linha são permitidos ao acessar o índice. O Mecanismo de Banco de Dados determina quando os bloqueios de linha são usados.
DisAllowPageLocks Quando TRUE, os bloqueios de página não são usados. Quando FALSE, bloqueios de página são permitidos ao acessar o índice. O Mecanismo de Banco de Dados determina quando os bloqueios de página são usados.

@OptionValue [ = ] 'ValorDaOpção'

Especifica se a configuração @OptionName está habilitada (TRUE, ON, yes, ou 1) ou desabilitada (FALSE, OFF, no, ou 0). @OptionValue é varchar(12), sem padrão.

Valores do código de retorno

0 (sucesso) ou > 0 (falha).

Comentários

Não há suporte para índices XML. Se um índice XML for especificado, ou um nome de tabela for especificado sem nome de índice e a tabela tiver um índice XML, haverá falha na instrução. Para definir essas opções, use ALTER INDEX .

Para exibir as propriedades atuais de bloqueio de linha e página, use INDEXPROPERTY ou a exibição de catálogo sys.indexes .

  • Bloqueios em nível de linha, nível de página e nível de tabela são permitidos ao acessar o índice quando AllowRowLocks = TRUE ou DisAllowRowLocks = FALSE, e AllowPageLocks = TRUE ou DisAllowPageLocks = FALSE. O Mecanismo de Banco de Dados escolhe o bloqueio apropriado e pode escalar o bloqueio de uma linha ou página para um bloqueio de tabela.

Somente um bloqueio em nível de tabela é permitido ao acessar o índice when AllowRowLocks = FALSE or DisAllowRowLocks = TRUE e AllowPageLocks = FALSE or DisAllowPageLocks = TRUE.

Se um nome de tabela for especificado sem-índice, as configurações serão aplicadas a todos os índices nessa tabela. Quando a tabela subjacente não tem índice clusterizado (ou seja, é um heap), as configurações são aplicadas da seguinte maneira:

  • Quando AllowRowLocks or DisAllowRowLocks são definidos como TRUE ou FALSE, a configuração é aplicada ao heap e a todos os índices não clusterizados associados.

  • Quando AllowPageLocks option é definida como TRUE ou DisAllowPageLocks definida como FALSE, a configuração é aplicada ao heap e a todos os índices não clusterizados associados.

  • Quando AllowPageLocks option é definido FALSE ou DisAllowPageLocks definido como TRUE, a configuração é totalmente aplicada aos índices não clusterizados. Ou seja, nenhum bloqueio de página é permitido nos índices não clusterizados. No heap, somente os bloqueios compartilhados (S, shared), de atualização (U, update) e exclusivos (X, exclusive) de página não são permitidos. O Mecanismo de Banco de Dados ainda pode adquirir um bloqueio de página intencional (IS, IU ou IX) para fins internos.

Permissões

Requer a permissão ALTER na tabela.

Exemplos

R. Definir uma opção em um índice específico

O exemplo a seguir não permite bloqueios de página no IX_Customer_TerritoryID índice da Customer tabela.

USE AdventureWorks2022;
GO

EXEC sp_indexoption N'Sales.Customer.IX_Customer_TerritoryID',
    N'disallowpagelocks',
    TRUE;

B. Definir uma opção em todos os índices em uma tabela

O exemplo a seguir não permite bloqueios de linha em todos os índices associados com a tabela Product. A exibição do catálogo sys.indexes é consultada antes e depois da execução do procedimento sp_indexoption para mostrar os resultados da instrução.

USE AdventureWorks2022;
GO

--Display the current row and page lock options for all indexes on the table.
SELECT name,
    type_desc,
    allow_row_locks,
    allow_page_locks
FROM sys.indexes
WHERE object_id = OBJECT_ID(N'Production.Product');
GO

-- Set the disallowrowlocks option on the Product table.
EXEC sp_indexoption N'Production.Product',
    N'disallowrowlocks',
    TRUE;
GO

--Verify the row and page lock options for all indexes on the table.
SELECT name,
    type_desc,
    allow_row_locks,
    allow_page_locks
FROM sys.indexes
WHERE object_id = OBJECT_ID(N'Production.Product');
GO

C. Definir uma opção em uma tabela sem índice clusterizado

O exemplo a seguir não permite bloqueios de página em uma tabela sem índice clusterizado (um heap). A sys.indexes exibição do catálogo é consultada antes e depois da execução do sp_indexoption procedimento para mostrar os resultados da instrução.

USE AdventureWorks2022;
GO

--Display the current row and page lock options of the table.
SELECT OBJECT_NAME(object_id) AS [Table],
    type_desc,
    allow_row_locks,
    allow_page_locks
FROM sys.indexes
WHERE OBJECT_NAME(object_id) = N'DatabaseLog';
GO

-- Set the disallowpagelocks option on the table.
EXEC sp_indexoption DatabaseLog,
    N'disallowpagelocks',
    TRUE;
GO

--Verify the row and page lock settings of the table.
SELECT OBJECT_NAME(object_id) AS [Table],
    allow_row_locks,
    allow_page_locks
FROM sys.indexes
WHERE OBJECT_NAME(object_id) = N'DatabaseLog';
GO

Conteúdo relacionado