sp_indexoption (Transact-SQL)

Articolo
03/12/2024

Si applica a:SQL Server

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

SQL Server motore di database effettua automaticamente scelte di blocco a livello di pagina, riga o tabella. Non è necessario impostare queste opzioni manualmente. sp_indexoption viene fornito agli utenti esperti che conoscono con certezza che un particolare tipo di blocco è sempre appropriato.

Importante

Questa funzionalità verrà rimossa nelle versioni future di SQL Server. Evitare di usare questa funzionalità in un nuovo progetto di sviluppo e prevedere interventi di modifica nelle applicazioni in cui è attualmente implementata. Usare invece ALTER INDEX (Transact-SQL).

Convenzioni di sintassi Transact-SQL

Sintassi

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

Argomenti

[ @IndexNamePattern = ] 'table_or_index_name' Nome qualificato o non qualificato di una tabella o di un indice definito dall'utente. table_or_index_name è nvarchar(1035), senza impostazione predefinita. 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 dell'opzione di indice. option_name è varchar(35), senza impostazione predefinita. option_name può avere uno dei valori seguenti.

Valore	Descrizione
AllowRowLocks	Se è TRUE, i blocchi a livello di riga sono consentiti durante l'accesso all'indice. Il motore di database determina quando usare i blocchi 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. Il motore di database determina quando usare i blocchi 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. Il motore di database determina quando usare i blocchi 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. Il motore di database determina quando usare i blocchi di pagina.

[ @OptionValue = ] 'value'Specifica se l'impostazione option_name è abilitata (TRUE, ON, sì o 1) o disabilitata (FAL edizione Standard, OFF, no o 0). value è varchar(12), senza impostazione predefinita.

Valori del codice restituito

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 invece ALTER INDEX .

Per visualizzare le proprietà di blocco di righe e pagine correnti, usare INDEXPROPERTY o la vista del catalogo sys.indexes .

I blocchi a livello di riga, di pagina e di tabella sono consentiti quando accedono all'indice quando AllowRowLocks = TRUE o DisAllowRowLocks = FAL edizione Standard e AllowPageLocks = TRUE o DisAllowPageLocks = FAL edizione Standard. Il 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.

Quando si accede all'indice è consentito solo un blocco a livello di tabella quando AllowRowLocks = FAL edizione Standard o DisAllowRowLocks = TRUE e AllowPageLocks = FAL edizione Standard 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:

Quando AllowRowLocks o DisAllowRowLocks sono impostati su TRUE o FAL edizione Standard, l'impostazione viene applicata all'heap e agli indici non cluster associati.
Quando l'opzione AllowPageLocks è impostata su TRUE o DisAllowPageLocks è impostata su FAL edizione Standard, l'impostazione viene applicata all'heap e agli indici non cluster associati.
Quando l'opzione AllowPageLocks è impostata su FAL edizione Standard o 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. Il 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

R. Impostazione di un'opzione in un indice specifico

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

USE AdventureWorks2022;  
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 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. 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). La sys.indexes vista del catalogo viene eseguita una query prima e dopo l'esecuzione della sp_indexoption routine per visualizzare i risultati dell'istruzione.

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

Vedi anche

INDEXPROPERTY (Transact-SQL)
Stored procedure di sistema (Transact-SQL)
sys.indexes (Transact-SQL)