Condividi tramite

sp_indexoption (Transact-SQL)

  • Articolo

Imposta i valori dell'opzione di blocco per gli indici cluster e non cluster e per le tabelle senza indici cluster.

In Motore di database di SQL Server le opzioni per il blocco a livello di pagina, riga e tabella vengono configurate automaticamente. Non è necessario impostare queste opzioni manualmente. La stored procedure sp_indexoption deve essere eseguita solo da utenti esperti quando un tipo di blocco specifico risulta sempre adeguato.

Nota importanteImportante

Questa funzionalità verrà rimossa a partire dalla prossima versione di Microsoft SQL Server. Evitare di utilizzare questa funzionalità in un nuovo progetto di sviluppo e prevedere interventi di modifica nelle applicazioni in cui è attualmente implementata. Utilizzare ALTER INDEX (Transact-SQL) in alternativa.

Icona di collegamento a un argomento Convenzioni della sintassi Transact-SQL

Sintassi

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

Argomenti

  • [ @IndexNamePattern=] 'table_or_index_name'
    Nome completo o non qualificato di una tabella o un indice definito dall'utente. table_or_index_name è di tipo nvarchar(1035) e non prevede alcun valore predefinito. Se si specifica un nome qualificato di indice o tabella, le virgolette sono obbligatorie. Nel caso di un nome qualificato di tabella, ovvero contenente un nome di database, il nome del database deve corrispondere a quello del database corrente. Se un nome di tabella viene specificato senza alcun indice, il valore dell'opzione specificata viene impostato per tutti gli indici in tale tabella e nella tabella stessa se non esistono indici cluster.

  • [ @OptionName =] 'option_name'
    Nome di opzione di indice. option_name è di tipo varchar(35) e non prevede alcun valore predefinito. option_name può disporre di uno dei valori seguenti.

    Valore

    Descrizione

    AllowRowLocks

    Se è TRUE, i blocchi a livello di riga sono consentiti durante l'accesso all'indice. Motore di database determina quando utilizzare blocchi a livello di riga. Se è FALSE, i blocchi a livello di riga non vengono utilizzati. Il valore predefinito è TRUE.

    AllowPageLocks

    Se è TRUE, i blocchi a livello di pagina sono consentiti durante l'accesso all'indice. Motore di database determina quando utilizzare blocchi a livello di pagina. Se è FALSE, i blocchi a livello di pagina non vengono utilizzati. Il valore predefinito è TRUE.

    DisAllowRowLocks

    Se è TRUE, i blocchi a livello di riga non vengono utilizzati. Se è FALSE, i blocchi a livello di riga sono consentiti durante l'accesso all'indice. Motore di database determina quando utilizzare blocchi a livello di riga.

    DisAllowPageLocks

    Se è TRUE, i blocchi a livello di pagina non vengono utilizzati. Se è FALSE, i blocchi a livello di pagina sono consentiti durante l'accesso all'indice. Motore di database determina quando utilizzare blocchi a livello di pagina.

  • [ @OptionValue =] 'value'
    Specifica se l'impostazione di option_name è abilitata (TRUE, ON, yes o 1) o disabilitata (FALSE, OFF, no o 0). value è di tipo varchar(12) e non prevede alcun valore predefinito.

Valori restituiti

0 (esito positivo) o maggiore di 0 (esito negativo)

Osservazioni

Gli indici XML non sono supportati. Se si specifica un indice XML oppure un nome di tabella senza un nome di indice e la tabella include un indice XML, l'esecuzione dell'istruzione ha esito negativo. Per impostare queste opzioni, utilizzare l'istruzione ALTER INDEX in alternativa.

Per visualizzare le proprietà di blocco correnti a livello di riga e pagina, utilizzare la vista del catalogo INDEXPROPERTY o sys.indexes.

  • Sono consentiti i blocchi a livello di riga, pagina e tabella durante l'accesso all'indice se AllowRowLocks = TRUE oppure DisAllowRowLocks = FALSE e AllowPageLocks = TRUE o DisAllowPageLocks = FALSE. Motore di database sceglie il blocco appropriato e può eseguire un'escalation del blocco da un blocco di riga o di pagina a un blocco di tabella.

È consentito solo un blocco a livello di tabella durante l'accesso all'indice se AllowRowLocks = FALSE o DisAllowRowLocks = TRUE e AllowPageLocks = FALSE o DisAllowPageLocks = TRUE.

Se si specifica un nome di tabella senza alcun indice, le impostazioni vengono applicate a tutti gli indici in tale tabella. Se la tabella sottostante non include indici cluster, ovvero è un heap, le impostazioni vengono applicate nel modo descritto di seguito:

  • Se l'opzione AllowRowLocks o DisAllowRowLocks è impostata su TRUE o FALSE, l'impostazione viene applicata all'heap e a qualsiasi indice non cluster associato.

  • Se l'opzione AllowPageLocks è impostata su TRUE o l'opzione DisAllowPageLocks è impostata su FALSE, l'impostazione viene applicata all'heap e a qualsiasi indice non cluster associate.

  • Se l'opzione AllowPageLocks è impostata su FALSE oppure l'opzione DisAllowPageLocks è impostata su TRUE, l'impostazione viene applicata completamente agli indici non cluster, ovvero vengono disattivati tutti i blocchi a livello di pagina negli indici non cluster. Nell'heap sono disattivati solo i blocchi condivisi (S), i blocchi di aggiornamento (U) e i blocchi esclusivi (X) a livello di pagina. Motore di database può comunque acquisire un blocco preventivo a livello di pagina (IS, IU o IX) per scopi interni.

Autorizzazioni

È necessario disporre dell'autorizzazione ALTER per la tabella.

Esempi

A.Impostazione di un'opzione in un indice specifico

Nell'esempio seguente i blocchi a livello di pagina vengono disattivati nell'indice IX_Customer_TerritoryID della tabella Customer.

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

B.Impostazione di un'opzione in tutti gli indici di una tabella

Nell'esempio seguente i blocchi a livello di riga vengono disattivati in tutti gli indici associati alla tabella Product. Viene eseguita una query sulla vista del catalogo sys.indexes prima e dopo l'esecuzione della stored procedure sp_indexoption per visualizzare i risultati dell'istruzione.

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.Impostazione di un'opzione in una tabella senza indici cluster

Nell'esempio seguente i blocchi a livello di pagina vengono disattivati in una tabella senza indici cluster (heap). Viene eseguita una query sulla vista del catalogo sys.indexes prima e dopo l'esecuzione della stored procedure sp_indexoption per visualizzare i risultati dell'istruzione.

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

Vedere anche

Riferimento

INDEXPROPERTY (Transact-SQL)

Stored procedure di sistema (Transact-SQL)

sys.indexes (Transact-SQL)