sp_indexoption (Transact-SQL)

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.

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.

@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

• INDEXPROPERTY (Transact-SQL)
• Procedimentos armazenados do sistema (Transact-SQL)
• sys.indexes (Transact-SQL)