Freigeben über

sp_indexoption (Transact-SQL)

  • Artikel

Gilt für: SQL Server

Legt Werte für Sperrenoptionen für benutzerdefinierte gruppierte und nicht gruppierte Indizes oder Tabellen ohne gruppierten Index fest.

Die SQL Server-Datenbank-Engine wählt automatisch die Sperrung auf Seiten-, Zeilen- oder Tabellenebene aus. Sie müssen diese Optionen nicht manuell festlegen. sp_indexoption wird für erfahrene Benutzer bereitgestellt, die mit Sicherheit wissen, dass eine bestimmte Art von Sperre immer angemessen ist.

Wichtig

Diese Funktion wird in einer zukünftigen Version von SQL Serverentfernt. Nutzen Sie diese Funktionen bei Neuentwicklungen nicht mehr, und planen Sie die Änderung von Anwendungen, die diese Funktion zurzeit verwenden. Verwenden Sie stattdessen ALTER INDEX.

Transact-SQL-Syntaxkonventionen

Syntax

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

Argumente

[ @IndexNamePattern = ] N'IndexNamePattern'

Der qualifizierte oder nicht qualifizierte Name einer benutzerdefinierten Tabelle oder eines benutzerdefinierten Indexes. @IndexNamePattern ist nvarchar(1035), ohne Standard. Anführungszeichen sind nur erforderlich, wenn ein qualifizierter Index- oder Tabellenname angegeben wird. Bei Angabe eines voll gekennzeichneten Tabellennamens (einschließlich eines Datenbanknamens) muss der Datenbankname der Name der aktuellen Datenbank sein. Wenn ein Tabellenname ohne Index angegeben wird, wird der angegebene Optionswert für alle Indizes dieser Tabelle und für die Tabelle selbst, falls kein gruppierter Index vorhanden ist, festgelegt.

[ @OptionName = ] 'OptionName'

Ein Indexoptionsname. @OptionName ist varchar(35) und kann einer der folgenden Werte sein.

Wert Beschreibung
AllowRowLocks Wenn TRUE, sind Zeilensperren beim Zugriff auf den Index zulässig. Das Datenbank-Engine bestimmt, wann Zeilensperren verwendet werden. Wenn FALSE, Zeilensperren werden nicht verwendet. Der Standardwert ist TRUE.
AllowPageLocks Wenn TRUE, Seitensperren sind beim Zugriff auf den Index zulässig. Das Datenbank-Engine bestimmt, wann Seitensperren verwendet werden. Wenn FALSE, Seitensperren werden nicht verwendet. Der Standardwert ist TRUE.
DisAllowRowLocks Wenn TRUE, Zeilensperren werden nicht verwendet. Wenn FALSE, sind Zeilensperren beim Zugriff auf den Index zulässig. Das Datenbank-Engine bestimmt, wann Zeilensperren verwendet werden.
DisAllowPageLocks Wenn TRUE, Seitensperren werden nicht verwendet. Wenn FALSE, Seitensperren sind beim Zugriff auf den Index zulässig. Das Datenbank-Engine bestimmt, wann Seitensperren verwendet werden.

[ @OptionValue = ] 'OptionValue'

Gibt an, ob die @OptionName Einstellung aktiviert ist (TRUE, ON, yesoder ) oder 1deaktiviert (FALSE, OFF, , nooder 0). @OptionValue ist varchar(12) ohne Standard.

Rückgabecodewerte

0 (erfolgreich) oder > 0 Fehler.

Hinweise

XML-Indizes werden nicht unterstützt. Wenn Sie einen XML-Index angeben oder einen Tabellennamen ohne Indexnamen angeben und die Tabelle einen XML-Index enthält, wird für die Anweisung ein Fehler gemeldet. Um diese Optionen festzulegen, verwenden Sie stattdessen ALTER INDEX .

Verwenden Sie INDEXPROPERTY oder die Sys.indexes-Katalogansicht , um die aktuellen Zeilen- und Seitensperreigenschaften anzuzeigen.

  • Sperren auf Zeilenebene, Seitenebene und Tabellenebene sind beim Zugriff auf den Index zulässig, wenn AllowRowLocks = TRUE oder DisAllowRowLocks = FALSEoder AllowPageLocks = TRUE .DisAllowPageLocks = FALSE Das Datenbank-Engine wählt die geeignete Sperre aus und kann die Sperre von einer Zeilen- oder Seitensperre auf eine Tabellensperre ausweiten.

Nur eine Sperre auf Tabellenebene ist beim Zugriff auf den Index zulässig, wenn AllowRowLocks = FALSE oder DisAllowRowLocks = TRUE AllowPageLocks = FALSE oder .DisAllowPageLocks = TRUE

Wenn ein Tabellenname ohne Index angegeben wird, werden die Einstellungen auf alle Indizes dieser Tabelle angewendet. Wenn die zugrunde liegende Tabelle keinen gruppierten Index aufweist (d. h. ein Heap), werden die Einstellungen wie folgt angewendet:

  • Wenn AllowRowLocks die Einstellung auf TRUE den Heap und alle zugeordneten nicht gruppierten Indizes angewendet wird oder DisAllowRowLocks FALSEauf diese festgelegt wird.

  • Wenn AllowPageLocks die Option auf " TRUE DisAllowPageLocks Oder auf" festgelegt FALSEist, wird die Einstellung auf den Heap und alle zugeordneten nicht gruppierten Indizes angewendet.

  • Wenn AllowPageLocks die Option festgelegt FALSE oder DisAllowPageLocks auf diese festgelegt TRUEist, wird die Einstellung vollständig auf die nicht gruppierten Indizes angewendet. Das heißt, alle Seitensperren sind für die nicht gruppierten Indizes nicht zulässig. Auf dem Heap sind nur freigegebene Sperren (S), Updatesperren (U) und exklusive Sperren (X) für die Seite nicht zulässig. Das Datenbank-Engine kann weiterhin eine beabsichtigte Seitensperre (IS, IU oder IX) für interne Zwecke abrufen.

Berechtigungen

Erfordert die ALTER-Berechtigung für die Tabelle.

Beispiele

A. Festlegen einer Option für einen bestimmten Index

Im folgenden Beispiel werden Seitensperren für den IX_Customer_TerritoryID Index in der Customer Tabelle nicht zulässig.

USE AdventureWorks2022;
GO

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

B. Festlegen einer Option für alle Indizes einer Tabelle

Im folgenden Beispiel werden Zeilensperren für alle Indizes der Product-Tabelle nicht zugelassen. Die sys.indexes-Katalogsicht wird vor und nach dem Ausführen der gespeicherten Prozedur sp_indexoption abgefragt, um die Ergebnisse der Anweisung anzuzeigen.

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. Festlegen einer Option für eine Tabelle ohne gruppierten Index

Im folgenden Beispiel werden Seitensperren für eine Tabelle ohne gruppierten Index (ein Heap) nicht zugelassen. Die sys.indexes Katalogansicht wird vor und nach der Ausführung der sp_indexoption Prozedur abgefragt, um die Ergebnisse der Anweisung anzuzeigen.

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

Zugehöriger Inhalt