sp_tableoption (Transact-SQL)

Gilt für: SQL Server (alle unterstützten Versionen) Azure SQL Datenbank Azure SQL Managed Instance

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

Wichtig

Das Text in Zeilenfeature wird in einer zukünftigen Version von SQL Server entfernt. Um große Wertdaten zu speichern, empfiehlt es sich, die Datentypen varchar(max), nvarchar(max) und varbinary(max) zu verwenden.

ThemenlinksymbolTransact-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. die Tabelle ist nvarchar(776), ohne Standard.

[ @OptionName = ] 'option_name'
Der Name einer Tabellenoption. option_name ist varchar(35), ohne Standard von NULL. option_name kann eine 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 keinen Einfluss auf das Sperrverhalten von SQL Server und ist nur für die 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 aktiviert (aktiviert) oder ein ganzzahliger Wert von 24 bis 7000, neuer Text, ntext oder Bildzeichenfolgen direkt in der Datenzeile gespeichert werden. Alle vorhandenen BLOB (binär großes Objekt: Text, ntext oder Bilddaten ) 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 große benutzerdefinierte Typspalten (UDT) in der Tabelle werden in der Tabelle 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 und höher.

Verwenden Sie die TEXTIMAGE_ON Option "CREATE TABLE" , um einen Speicherort für die Speicherung großer Datentypen anzugeben.
vardecimal-Speicherformat Gilt für: SQL Server 2008 und höher.

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 Vardezimalspeicherformat kann nur aktiviert werden, wenn die Datenbank mit sp_db_vardecimal_storage_format für das Vardezimalspeicherformat aktiviert wurde. Im SQL Server 2008 und höher ist vardezimaler Speicherformat veraltet. Verwenden Sie stattdessen die ROW-Komprimierung. Weitere Informationen finden Sie unter Data Compression. Der Standardwert ist 0 (null).

[ @OptionValue =] 'Wert'
Ist, ob die option_name aktiviert ist (TRUE, ON oder 1) oder deaktiviert (FALSE, OFF oder 0). wert ist varchar(12), ohne Standard. der Wert ist groß- und kleinschreibungsfähig.

Gültige Werte für die text in row-Option sind: 0, ON, OFF oder eine Ganzzahl zwischen 24 und 7000. Wenn der Wert AKTIVIERT ist, wird der Grenzwert auf 256 Bytes festgelegt.

Rückgabecodewerte

0 (Erfolg) oder eine Fehlernummer (Fehler)

Hinweise

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

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 Text in Zeilenoption aktiviert ist, ermöglicht es @OptionValue Benutzern, die maximale Größe anzugeben, 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 Bildzeichenfolgen werden in der Datenzeile gespeichert, wenn die folgenden Bedingungen gelten:

  • text in row ist aktiviert.

  • Die Länge der Zeichenfolge ist kürzer als die 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 des Texts, des ntexts oder der Bildzeichenfolgen so schnell wie das Lesen oder Schreiben von Zeichen und binären Zeichenfolgen sein. SQL Server muss nicht auf separate Seiten zugreifen, um die BLOB-Zeichenfolge zu lesen oder zu schreiben.

Wenn eine Text-, ntext- oder Bildzeichenfolge größer als der angegebene Grenzwert oder der verfügbare Speicherplatz in der Zeile ist, werden Zeiger stattdessen 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 der 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 wird der Text in Zeilenoptionslimit erhöht, werden die Text-, ntext- oder Bildzeichenfolgen , die bereits in der Datenzeile vorhanden sind, nicht konvertiert, um den neuen Grenzwert einzuhalten, bis die Zeit, die 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 Bild) in klein- bis mittelgroße Werttypen (varchar(max), nvarchar(max) oder varbinary(max) ) konvertieren und die meisten Anweisungen nicht auf die großen Werttypspalten in Ihrer Umgebung verweisen, sollten Sie large_value_types_out_of_row auf 1 ändern, um 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. Für sofortige Ergebnisse erstellen Sie entweder eine Kopie der Daten, und füllen Sie dann die Tabelle nach dem Ändern der Einstellung large_value_types_out_of_row erneut aus, oder aktualisieren Sie jede Spalte mit mittleren großen Werttypen auf sich selbst, sodass der Speicher der Zeichenfolgen mit der Inkraftsetzungsoption der Tabelle 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 aus 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 Tabelle geändert, um den Production.WorkOrderRoutingdecimal 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 '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)