sp_tableoption (Transact-SQL)

Artikel
03/11/2014

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

Wichtig

Die Funktion 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.

Gilt für: SQL Server (SQL Server 2008 bis aktuelle Version), Windows Azure SQL-Datenbank (Ursprüngliche Version bis aktuelle Version).

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 ist vom Datentyp nvarchar(776) und hat keinen Standardwert.

[ @OptionName = ] 'option_name'
Der Name einer Tabellenoption. option_name ist vom Datentyp varchar(35) und hat keinen Standardwert NULL. Für option_name sind die folgenden Werte möglich.

table lock on bulk load

Eine deaktivierte Option (Standard) führt dazu, dass der Massenladevorgang auf benutzerdefinierten Tabellen Zeilensperren erhält. Wenn diese Option aktiviert ist, erhalten die Massenladevorgänge auf benutzerdefinierten Tabellen eine Massenupdatesperre.

insert row lock

Nicht mehr unterstützt.

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

Beim 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-Zeichenfolgen, ntext-Zeichenfolgen oder image-Zeichenfolgen direkt in der Datenzeile gespeichert. Alle vorhandenen BLOB-Daten (Binary Large Object: 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 = varchar(max), nvarchar(max), varbinary(max), xml und große benutzerdefinierte Typspalten (UDTs) der Tabelle werden außerhalb von Zeilen mit einem 16-Byte-Zeiger auf den Stamm gespeichert.

0 = varchar(max), nvarchar(max), varbinary(max), xml und große UDT-Werte 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. Der Standardwert lautet 0.

Großer benutzerdefinierter Typ (UDT) gilt für: SQL Server 2008 bis SQL Server 2014.

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. In SQL Server 2008 und höheren Versionen ist das vardecimal-Speicherformat als veraltet markiert. Verwenden Sie stattdessen die ROW-Komprimierung. Weitere Informationen finden Sie unter Datenkomprimierung. Der Standardwert lautet 0.

Gilt für: SQL Server 2008 bis SQL Server 2014.

[ @OptionValue =] 'value'
Gibt an, ob option_name aktiviert (TRUE, ON oder 1) oder deaktiviert ist (FALSE, OFF oder 0). value ist vom Datentyp varchar(12) und hat keinen Standardwert. value wird die Groß-/Kleinschreibung nicht unterschieden.

Gültige Werte für die text in row-Option sind: 0, ON, OFF oder eine Ganzzahl zwischen 24 und 7000. Wenn value auf ON festgelegt 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.

Wenn BLOB-Zeichenfolgen in der Datenzeile gespeichert werden, können text-, ntext- oder image-Zeichenfolgen mit der gleichen Geschwindigkeit wie Zeichenfolgen und Binärzeichenfolgen gelesen und geschrieben werden. SQL Server kann nicht auf separate 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 trotzdem gültig: Für die Zeiger muss genügend Speicherplatz in der Datenzeile vorhanden sein.

BLOB-Zeichenfolgen und -Zeiger, die in der Zeile einer Tabelle gespeichert werden, werden ähnlich wie Zeichenfolgen mit variabler Länge behandelt. SQL Server verwendet nur so viele Bytes, wie erforderlich sind, um die Zeichenfolge oder den Zeiger zu speichern.

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 Grenzwerts für die text in row-Option die bereits in der Datenzeile befindlichen text-, ntext- oder image-Zeichenfolgen erst zur Einhaltung des neuen Grenzwerts konvertiert, wenn sie aktualisiert werden.

Hinweis

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

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

Die Option 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.

Wenn Sie eine Tabelle vom vardecimal-Speicherformat zurück in das normale decimal-Speicherformat konvertieren möchten, muss sich die Datenbank im SIMPLE-Wiederherstellungsmodus befinden. Durch das Ändern des Wiederherstellungsmodus wird die Protokollkette für Sicherungszwecke unterbrochen. 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 AdventureWorks2012;
GO
EXEC sp_tableoption 'HumanResources.JobCandidate', 'large value types out of row', 1;

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

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

Gilt für: SQL Server 2008 bis SQL Server 2014.

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 'AdventureWorks2012', 'ON';
GO
USE AdventureWorks2012;
GO
EXEC sp_tableoption 'Production.WorkOrderRouting', 
   'vardecimal storage format', 'ON';