• Artikel

sp_tableoption (Transact-SQL)

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.

Wichtiger HinweisWichtig

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

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

    Wert

    Beschreibung

    table lock on bulk load

    Wenn diese Option deaktiviert ist (Standardeinstellung), erhält der Massenladevorgang auf benutzerdefinierten Tabellen Zeilensperren. Wenn diese Option aktiviert ist, erhalten die Massenladevorgänge auf benutzerdefinierten Tabellen eine Massenaktualisierungssperre.

    insert row lock

    Wird in SQL Server 2008 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

    Beim Wert OFF oder 0 (deaktivierte Option, Standardeinstellung) 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 Zeichenfolgen vom Typ text, ntext oder image direkt in der Datenzeile gespeichert. Alle vorhandenen BLOB-Daten (Binary Large Object: text-, ntext- oder image-Daten) 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.

    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 nicht für das vardecimal-Speicherformat 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 Editionen SQL Server Enterprise, Developer und Evaluation verfügbar. In SQL Server 2008 und höheren Versionen ist für alle Benutzerdatenbanken das vardecimal-Speicherformat aktiviert. 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 Erstellen komprimierter Tabellen und Indizes. Der Standardwert lautet 0.

  • [ @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 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 beträgt 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 dennoch 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 Grenzwerts für die text in row-Option die bereits in der Datenzeile befindlichen text-, ntext- oder image-Zeichenfolgen erst unter Berücksichtigung des neuen Grenzwerts konvertiert, wenn sie aktualisiert werden.

HinweisHinweis

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.

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 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 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 vardecimalstorage format 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';