sp_indexoption (Transact-SQL)

Artigo
06/18/2013

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 automaticamente escolhas de bloqueio no nível de página, linha ou tabela. Você não precisa definir essas opções manualmente. sp_indexoption é fornecido para usuários especialistas que sabem com certeza que um tipo de bloqueio específico é sempre adequado.

Importante
Esse recurso será removido na próxima versão do Microsoft SQL Server. Evite usar esse recurso em desenvolvimentos novos e planeje modificar os aplicativos que atualmente o utilizam. Em vez disso, use ALTER INDEX (Transact-SQL).

Ícone de vínculo de tópico Convenções da sintaxe Transact-SQL

Sintaxe

sp_indexoption [ @IndexNamePattern = ] 'table_or_index_name' 
    , [ @OptionName = ] 'option_name' 
    , [ @OptionValue = ] 'value'

Argumentos

[ @IndexNamePattern=] 'table_or_index_name'
É o nome qualificado ou não qualificado de uma tabela ou índice definido pelo usuário. table_or_index_name é 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 =] 'option_name'
É um nome de opção de índice. option_name é varchar(35), sem padrão. option_name pode ter um dos seguintes valores:

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

[ @OptionValue =] 'value'
Especifica se a definição de option_name está habilitada (TRUE, ON, sim ou 1) ou desabilitada (FALSE, OFF, não ou 0). value é varchar(12), sem padrão.

Valores de código de retorno

0 (êxito) ou superior a 0 (falha)

Comentários

Não é oferecido suporte a í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 de bloqueio da linha e da página atuais, use INDEXPROPERTY ou a exibição do catálogo sys.indexes.

Os bloqueios no nível de linha, página e 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 no nível de tabela é permitido ao acessar o índice quando AllowRowLocks = FALSE ou DisAllowRowLocks = TRUE e AllowPageLocks = FALSE ou 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 tiver índice clusterizado (ou seja, é um heap), as configurações serão aplicadas da seguinte forma:

Quando AllowRowLocks ou DisAllowRowLocks forem definidos como TRUE ou FALSE, a configuração será aplicada ao heap e a quaisquer índices não clusterizados associados.
Quando a opção AllowPageLocks for definida como TRUE ou a opção DisAllowPageLocks for definida como FALSE, a configuração será aplicada ao heap e a quaisquer índices não clusterizados associados.
Quando a opção AllowPageLocks for definida como FALSE ou a opção DisAllowPageLocks for definida como TRUE, a configuração será totalmente aplicada a í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

A.Definindo uma opção em um índice específico

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

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

B.Definindo uma opção em todos os índices de 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 AdventureWorks2012;
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.Definindo 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 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 AdventureWorks2012;
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

Consulte também

Referência

INDEXPROPERTY (Transact-SQL)

Procedimentos armazenados do sistema (Transact-SQL)

sys.indexes (Transact-SQL)

Compartilhar via