Condividi tramite

sp_indexoption (Transact-SQL)

  • Articolo

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 manualmente queste opzioni. sp_indexoption viene fornito per utenti esperti che 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.

Convenzioni relative alla sintassi Transact-SQL

Sintassi

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

Argomenti

[ @IndexNamePattern = ] N'IndexNamePattern'

Nome qualificato o non qualificato di una tabella o di un indice definito dall'utente. @IndexNamePattern è 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 = ] 'OptionName'

Nome dell'opzione di indice. @OptionName è varchar(35)e può essere uno dei valori seguenti.

valore Descrizione
AllowRowLocks Quando TRUE, i blocchi di riga sono consentiti quando si accede all'indice. Il motore di database determina quando usare i blocchi di riga. Quando FALSE, i blocchi di riga non vengono usati. Il valore predefinito è TRUE.
AllowPageLocks Quando TRUE, i blocchi di pagina sono consentiti durante l'accesso all'indice. Il motore di database determina quando usare i blocchi di pagina. Quando FALSE, i blocchi di pagina non vengono usati. Il valore predefinito è TRUE.
DisAllowRowLocks Quando TRUE, i blocchi di riga non vengono usati. Quando FALSE, i blocchi di riga sono consentiti quando si accede all'indice. Il motore di database determina quando usare i blocchi di riga.
DisAllowPageLocks Quando TRUE, i blocchi di pagina non vengono usati. Quando FALSE, i blocchi di pagina sono consentiti durante l'accesso all'indice. Il motore di database determina quando usare i blocchi di pagina.

[ @OptionValue = ] 'OptionValue'

Specifica se l'impostazione @OptionName è abilitata (TRUE, ON, yeso 1) o disabilitata (FALSE, OFF, noo 0). @OptionValue è varchar(12), senza impostazione predefinita.

Valori del codice restituito

0 (esito positivo) o > 0 (errore).

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, a livello di pagina e a livello di tabella sono consentiti quando accedono all'indice quando AllowRowLocks = TRUE o DisAllowRowLocks = FALSEe AllowPageLocks = TRUE o DisAllowPageLocks = FALSE. 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.

Solo un blocco a livello di tabella è consentito quando si accede all'indice quando AllowRowLocks = FALSE o DisAllowRowLocks = TRUE o 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. Quando la tabella sottostante non ha un indice cluster(ovvero, si tratta di un heap) le impostazioni vengono applicate come segue:

  • Quando AllowRowLocks o DisAllowRowLocks sono impostati su TRUE o FALSE, l'impostazione viene applicata all'heap e agli indici non cluster associati.

  • Quando AllowPageLocks l'opzione è impostata su o DisAllowPageLocks è impostata TRUE su FALSE, l'impostazione viene applicata all'heap e agli indici non cluster associati.

  • Quando AllowPageLocks l'opzione è impostata o DisAllowPageLocks è impostata FALSE 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

È richiesta l'autorizzazione ALTER per la tabella.

Esempi

R. Impostare un'opzione su 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. Impostare un'opzione su 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. Impostare un'opzione in una tabella senza indice 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

Contenuto correlato