sp_tableoption (Transact-SQL)

Artikel
12/15/2008

Aktualisiert: 12. Dezember 2006

Legt Optionswerte für benutzerdefinierte Tabellen fest. sp_tableoption kann verwendet werden, um das Verhalten innerhalb von Tabellenzeilen mithilfe der Spalten varchar(max), nvarchar(max), varbinary(max), xml, text, ntext oder image zu steuern.

Wichtig:
Das Feature text in row wird in einer zukünftigen Version von SQL Server entfernt. Zum Speichern von Daten mit umfangreichen Werten wird die Verwendung der Datentypen varchar(max), nvarchar(max) und varbinary(max) empfohlen.

Themenlink (Symbol) Transact-SQL-Syntaxkonventionen

Syntax

sp_tableoption [ @TableNamePattern = ] 'table' 
          , [ @OptionName = ] 'option_name' 
          , [ @OptionValue = ] 'value'

Argumente

[ @TableNamePattern = ] 'table'
Der qualifizierte oder nicht qualifizierte Name einer benutzerdefinierten Datenbanktabelle. Bei Angabe eines vollqualifizierten Tabellennamens (einschließlich eines Datenbanknamens) muss der Datenbankname der Name der aktuellen Datenbank sein. Tabellenoptionen für mehrere Tabellen können nicht gleichzeitig festgelegt werden. table_pattern ist vom Datentyp nvarchar(776) und weist keinen Standardwert auf.

[ @OptionName =] 'option_name'
Dies ist ein Tabellenoptionsname. option_name ist vom Datentyp varchar(35), ohne den Standardwert NULL. Für option_name sind die folgenden Werte möglich:

Wert	Beschreibung
table lock on bulk load	Eine deaktivierte Option (Standard) führt dazu, dass der Massenladevorgang auf benutzerdefinierten Tabellen Zeilensperren erhält. Eine aktivierte Option führt dazu, dass die Massenladevorgänge auf benutzerdefinierten Tabellen eine Massenaktualisierungssperre erhalten.
insert row lock	Wird in SQL Server 2005 nicht unterstützt. Die Sperrstrategie von SQL Server ist das Sperren auf Zeilenebene mit einer möglichen Höherstufung auf Seiten- oder Tabellensperren. Diese Option wirkt sich nicht auf das Sperrverhalten von SQL Server aus und ist nur aus Gründen der Kompatibilität mit vorhandenen Skripts und Prozeduren enthalten.
text in row	Bei dem Wert OFF oder 0 (deaktivierte Option, Standard) wird das aktuelle Verhalten nicht geändert, und es gibt keine BLOBs in Zeilen. Sofern angegeben und wenn @OptionValue den Wert ON (aktiviert) oder einen ganzzahligen Wert von 24 bis 7000 aufweist, werden neue text-, ntext- oder image-Zeichenfolgen direkt in der Datenzeile gespeichert. Alle vorhandenen BLOB-Daten (text, ntext oder image) werden in das Format text in row geändert, wenn der BLOB-Wert aktualisiert wird. Weitere Informationen finden Sie in den Hinweisen.
LARGE VALUE TYPES OUT OF ROW	1 = Die Spalten varchar(max), nvarchar(max), varbinary(max) und xml in der Tabelle werden außerhalb von Zeilen mit einem 16-Byte-Zeiger auf den Stamm gespeichert. 0 = Die Werte varchar(max), nvarchar(max), varbinary(max) und xml werden bis zu einem Höchstwert von 8.000 Bytes direkt in der Datenzeile gespeichert, sofern der Wert die Größe des Datensatzes nicht überschreitet. Überschreitet der Wert die Größe des Datensatzes, wird ein Zeiger innerhalb der Zeilen gespeichert, während der Rest außerhalb der Zeilen im LOB-Speicherbereich gespeichert wird.
vardecimal-Speicherformat	Bei TRUE, ON oder 1 ist die festgelegte Tabelle für das vardecimal-Speicherformat aktiviert. Bei FALSE, OFF oder 0 ist die Tabelle für das vardecimal-Speicherformat nicht aktiviert. Das vardecimal-Speicherformat kann nur aktiviert werden, wenn die Datenbank mit sp_db_vardecimal_storage_format für das vardecimal-Speicherformat aktiviert wurde. Weitere Informationen zum vardecimal-Speicherformat finden Sie unter Speichern von Dezimaldaten als Daten variabler Länge. Für diese Option ist SQL Server 2005 Service Pack 2 erforderlich. Das vardecimal-Speicherformat ist nur in den SQL Server 2005 Enterprise, Developer und Evaluation Editions verfügbar.

[ @OptionValue = ] 'value'
Gibt an, ob option_name aktiviert (true, on oder 1) oder deaktiviert (false, off oder 0) ist. value ist vom Datentyp varchar(12) und weist keinen Standardwert auf. Bei value wird die Groß-/Kleinschreibung unterschieden.

Gültige Werte für die Option text in row sind: 0, on, off oder eine ganze Zahl zwischen 24 und 7000. Wenn value gleich on ist, beträgt der Standardgrenzwert 256 Bytes.

Rückgabecodewerte

0 (Erfolg) oder eine Fehlernummer (Fehler)

Hinweise

sp_tableoption kann nur verwendet werden, um die Optionswerte für benutzerdefinierte Tabellen festzulegen. Verwenden Sie OBJECTPROPERTY, um Tabelleneigenschaften anzuzeigen.

Die text in row-Option von sp_tableoption kann nur für Tabellen aktiviert oder deaktiviert werden, die Textspalten enthalten. Wenn die Tabelle nicht über eine Textspalte verfügt, löst SQL Server einen Fehler aus.

Wenn die text in row-Option aktiviert ist, können Benutzer mithilfe des @OptionValue-Parameters die für ein BLOB in einer Zeile zu speichernde Maximalgröße angeben. Der Standardwert ist 256 Bytes. Gültige Werte sind 24 bis 7000 Bytes.

text-, ntext- oder image-Zeichenfolgen werden in der Datenzeile gespeichert, wenn die folgenden Bedingungen erfüllt sind:

text in row ist aktiviert.
Die Länge der Zeichenfolge unterschreitet den in @OptionValue angegebenen Grenzwert.
Es steht genügend Speicherplatz in der Datenzeile zur Verfügung.

Werden BLOB-Zeichenfolgen in der Datenzeile gespeichert, können text-, ntext- oder image-Zeichenfolgen genauso schnell wie Zeichenfolgen und binäre Zeichenfolgen gelesen und geschrieben werden. SQL Server muss nicht auf gesonderte Seiten zugreifen, um die BLOB-Zeichenfolge zu lesen oder zu schreiben.

Wenn eine text-, ntext- oder image-Zeichenfolge den angegebenen Grenzwert oder den verfügbaren Speicherplatz in der Zeile überschreitet, werden anstelle der Zeichenfolge Zeiger in der Zeile gespeichert. Die Bedingungen zum Speichern der BLOB-Zeichenfolgen sind jedoch weiterhin gültig: für die Zeiger muss genügend Speicherplatz in der Datenzeile vorhanden sein.

In einer Tabellenzeile gespeicherte BLOB-Zeichenfolgen und Zeiger werden wie Zeichenfolgen mit variabler Länge behandelt. SQL Server verwendet nur so viele Bytes, wie zum Speichern der Zeichenfolge oder des Zeigers benötigt werden.

Vorhandene BLOB-Zeichenfolgen werden nicht sofort konvertiert, wenn text in row aktiviert ist. Die Zeichenfolgen werden erst konvertiert, wenn sie aktualisiert werden. Ebenso werden nach einer Erhöhung des Grenzwertes für die text in row-Option die bereits in der Datenzeile befindlichen text-, ntext- oder image-Zeichenfolgen erst zur Einhaltung des neuen Grenzwertes konvertiert, wenn sie aktualisiert werden.

Hinweis:
Wenn die text in row-Option deaktiviert wird oder der Grenzwert für diese Option verringert wird, müssen alle BLOBs konvertiert werden. Dieser Vorgang kann viel Zeit in Anspruch nehmen, je nach der Anzahl der zu konvertierenden BLOB-Zeichenfolgen. Während des Konvertierungsvorgangs ist die Tabelle gesperrt.

Für eine Variable vom Datentyp table sowie eine Funktion, die eine table-Variable zurückgibt, ist die text in row-Option automatisch mit dem Standardwert von 256 für inline limit aktiviert. Diese Option kann nicht geändert werden.

text in row unterstützt die Funktionen TEXTPTR, WRITETEXT, UPDATETEXT und READTEXT. Benutzer können Teile eines BLOBs mit der SUBSTRING()-Funktion lesen, sollten jedoch berücksichtigen, dass Textzeiger in Zeilen andere Grenzwerte für Dauer und Anzahl haben als andere Textzeiger. Weitere Informationen finden Sie unter Verwalten von ntext-, text- und image-Daten.

Wenn Sie eine Tabelle vom vardecimal-Speicherformat zurück in das decimal-Speicherformat konvertieren möchten, muss sich die Datenbank im SIMPLE-Wiederherstellungsmodus befinden. Durch das Ändern des Wiederherstellungsmodus wird die Protokollkette für Sicherungszwecke durchbrochen. Daher sollten Sie eine vollständige Datenbanksicherung erstellen, nachdem Sie das vardecimal-Speicherformat aus einer Tabelle entfernt haben.

Berechtigungen

Für die Ausführung von sp_tableoption ist die ALTER-Berechtigung für die Tabelle erforderlich.

Beispiele

A. Speichern von XML-Daten außerhalb der Zeile

Im folgenden Beispiel wird angegeben, dass die xml-Daten in der HumanResources.JobCandidate-Tabelle außerhalb von Zeilen gespeichert werden.

USE AdventureWorks;
GO
EXEC sp_tableoption 'HumanResources.JobCandidate', 'large value types out of row', 1;

B. Zulassen des vardecimal-Speicherformats für eine Tabelle

Im folgenden Beispiel wird die Production.WorkOrderRouting-Tabelle geändert, um den decimal-Datentyp im vardecimal-Speicherformat zu speichern.

USE master;
GO
-- The database must be enabled for vardecimal storage format
-- before a table can be enabled for vardecimal storage format.
EXEC sp_db_vardecimal_storage_format 'AdventureWorks', 'ON';
GO
USE AdventureWorks;
GO
EXEC sp_tableoption 'Production.WorkOrderRouting', 
   'vardecimal storage format', 'ON';

Siehe auch

Änderungsverlauf

Version	Verlauf
12. Dezember 2006	Neuer Inhalt: Informationen zu der in SQL Server 2005 Service Pack 2 verfügbaren Option für das vardecimal-Speicherformat wurden hinzugefügt.