sp_tableoption (Transact-SQL)

Artikel
05/23/2023

Gilt für:SQL Server Azure SQL-Datenbank Azure SQL Managed Instance

Legt Optionswerte für benutzerdefinierte Tabellen fest. sp_tableoption können verwendet werden, um das Zeileninterne Verhalten von Tabellen mit spalten varchar(max), nvarchar(max), varbinary(max), xml, text, ntext, image oder großen benutzerdefinierten Typspalten zu steuern.

Wichtig

Das Text-in-Zeilen-Feature wird in einer zukünftigen Version von SQL Server entfernt. Zum Speichern von Daten mit großen Werten wird empfohlen, die Datentypen varchar(max), nvarchar(max) und varbinary(max) zu verwenden.

Transact-SQL-Syntaxkonventionen

Syntax

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

Argumente

[ @TableNamePattern =] 'tabelle'
Der qualifizierte oder nicht qualifizierte Name einer benutzerdefinierten Datenbanktabelle. Bei Angabe eines voll gekennzeichneten 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 nvarchar(776), ohne Standardwert.

[ @OptionName = ] 'option_name'
Der Name einer Tabellenoption. option_name ist varchar(35), ohne dass der Standardwert NULL ist. option_name kann einer der folgenden Werte sein.

Wert	BESCHREIBUNG
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	Wird nicht mehr unterstützt. Diese Option hat keine Auswirkungen auf das Sperrverhalten von SQL Server und ist nur zur Kompatibilität vorhandener 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. Wenn angegeben und @OptionValue auf ON (aktiviert) oder ein ganzzahliger Wert von 24 bis 7000 festgelegt ist, werden neue Text-, ntext- oder Bildzeichenfolgen direkt in der Datenzeile gespeichert. Alle vorhandenen BLOB-Objekte (binary large object: text, ntext oder image data) werden in Text im Zeilenformat 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 udT-Spalten (Large User-Defined Type) in der Tabelle werden außerhalb der Zeile mit einem 16-Byte-Zeiger auf den Stamm gespeichert. 0 = varchar(max), nvarchar(max), varbinary(max), xml und große UDT-Werte werden direkt in der Datenzeile gespeichert, bis zu einem Grenzwert von 8000 Bytes und solange der Wert in den Datensatz passen kann. Ü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 ist 0 (null). Großer benutzerdefinierter Typ (UdT) gilt für: SQL Server 2008 (10.0.x) und höher. Verwenden Sie die option TEXTIMAGE_ON von CREATE TABLE , um einen Speicherort für die Speicherung großer Datentypen anzugeben.
vardecimal-Speicherformat	Gilt für: SQL Server 2008 (10.0.x) und später 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 mithilfe von sp_db_vardecimal_storage_format für das vardecimal-Speicherformat aktiviert wurde. In SQL Server 2008 (10.0.x) und höher ist das vardecimal-Speicherformat veraltet. Verwenden Sie stattdessen die ROW-Komprimierung. Weitere Informationen finden Sie unter Data Compression. Der Standardwert ist 0 (null).

[ @OptionValue =] 'value'
Gibt an, ob die option_name aktiviert (TRUE, ON oder 1) oder deaktiviert ist (FALSE, OFF oder 0). value ist varchar(12), ohne Standardwert. beim Wert wird die Groß-/Kleinschreibung nicht beachtet.

Gültige Werte für die text in row-Option sind: 0, ON, OFF oder eine Ganzzahl zwischen 24 und 7000. Wenn der Wert ON ist, beträgt der Grenzwert standardmäßig 256 Byte.

Rückgabecodewerte

0 (Erfolg) oder eine Fehlernummer (Fehler)

Bemerkungen

sp_tableoption kann nur verwendet werden, um die Optionswerte für benutzerdefinierte Tabellen festzulegen. Um Tabelleneigenschaften anzuzeigen, verwenden Sie OBJECTPROPERTY, oder fragen Sie sys.tables ab.

Die text in row-Option von sp_tableoption kann nur für Tabellen aktiviert oder deaktiviert werden, die Textspalten enthalten. Wenn die Tabelle keine Textspalte enthält, löst SQL Server einen Fehler aus.

Wenn die Option Text in Zeile aktiviert ist, können Benutzer mit dem @OptionValue Parameter die maximale Größe angeben, die in einer Zeile für ein BLOB gespeichert werden soll. 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 zutreffen:

text in row ist aktiviert.
Die Länge der Zeichenfolge ist kürzer als der in @OptionValue
Es steht genügend Speicherplatz in der Datenzeile zur Verfügung.

Wenn BLOB-Zeichenfolgen in der Datenzeile gespeichert werden, kann das Lesen und Schreiben der Text-, ntext- oder Bildzeichenfolgen so schnell sein, wie das Lesen oder Schreiben von Zeichen- und Binärzeichenfolgen. SQL Server muss nicht auf separate Seiten zugreifen, um die BLOB-Zeichenfolge zu lesen oder zu schreiben.

Wenn eine Text-, ntext- oder Bildzeichenfolge den angegebenen Grenzwert oder den verfügbaren Platz in der Zeile überschreitet, werden stattdessen 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 die Anzahl von Bytes, die zum Speichern der Zeichenfolge oder des Zeigers erforderlich sind.

Vorhandene BLOB-Zeichenfolgen werden nicht sofort konvertiert, wenn text in row aktiviert ist. Die Zeichenfolgen werden erst konvertiert, wenn sie aktualisiert werden. Ebenso werden die text-,ntext- oder image-Zeichenfolgen, die bereits in der Datenzeile enthalten sind, erst dann so konvertiert, dass sie den neuen Grenzwert einhalten, bis 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.

Wenn Sie eine vorhandene LOB-Datentypspalte (text, ntext oder image) in klein bis mittelgroße Werttypen (varchar(max), nvarchar(max) oder varbinary(max)) konvertieren und die meisten Anweisungen nicht auf die Spalten vom Typ "große Werte" in Ihrer Umgebung verweisen, sollten Sie large_value_types_out_of_row in 1 ändern, um eine optimale Leistung zu erzielen. Wenn der large_value_types_out_of_row Optionswert geändert wird, werden vorhandene varchar(max), nvarchar(max), varbinary(max) und xml-Werte nicht sofort konvertiert. Der Speicherung der Zeichenfolgen ändert sich, wenn diese anschließend aktualisiert werden. Alle neuen Werte, die in eine Tabelle eingefügt werden, werden gemäß der aktivierten Tabellenoption gespeichert. Um sofortige Ergebnisse zu erzielen, erstellen Sie entweder eine Kopie der Daten, und füllen Sie dann die Tabelle nach dem Ändern der large_value_types_out_of_row-Einstellung erneut auf, oder aktualisieren Sie jede Spalte mit kleinen bis mittleren großen Werttypen auf sich selbst, sodass der Speicher der Zeichenfolgen mit der option table geändert wird. Erstellen Sie die Indizes für die Tabelle nach der Aktualisierung oder Neuauffüllung neu, um die Tabelle zu komprimieren.

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 der Zeile 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 so geändert, dass der decimal Datentyp im vardecimal Speicherformat gespeichert wird.

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

Weitere Informationen

sys.tables (Transact-SQL)
OBJECTPROPERTY (Transact-SQL)
Gespeicherte Systemprozeduren (Transact-SQL)
Gespeicherte Prozeduren für die Datenbank-Engine (Transact-SQL)