sp_tableoption (Transact-SQL)

Gilt für:SQL ServerAzure SQL-DatenbankAzure SQL Managed Instance

Legt Optionswerte für benutzerdefinierte Tabellen fest. sp_tableoptionkann verwendet werden, um das In-Row-Verhalten von Tabellen mit varchar(max), nvarchar(max), varbinary(max), xml, text, ntext, image oder großen benutzerdefinierten Typspalten zu steuern.

Transact-SQL-Syntaxkonventionen

Syntax

sp_tableoption
    [ @TableNamePattern = ] N'TableNamePattern'
    , [ @OptionName = ] 'OptionName'
    , [ @OptionValue = ] 'OptionValue'
[ ; ]

Argumente

[ @TableNamePattern = ] N'TableNamePattern'

Der qualifizierte oder nicht qualifizierte Name einer benutzerdefinierten Datenbanktabelle. @TableNamePattern ist nvarchar(776), ohne Standard. 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.

[ @OptionName = ] 'OptionName'

Ein Tabellenoptionsname. @OptionName ist varchar(35) und kann einer der folgenden Werte sein.

[ @OptionValue = ] 'OptionValue'

Gibt an, ob die @OptionName aktiviert ist (TRUE, ONoder ) oder 1deaktiviert (FALSE, OFFoder 0). @OptionValue ist varchar(12) ohne Standard. @OptionValue wird die Groß-/Kleinschreibung nicht beachtet.

Für den Text in der Zeilenoption sind 0gültige Optionswerte , , ON, OFFoder eine ganze Zahl von 24 bis 7000. Wenn @OptionValue ist ON, beträgt der Grenzwert standardmäßig 256 Byte.

Rückgabecodewerte

0 (Erfolg) oder Fehlernummer (Fehler).

Hinweise

sp_tableoption kann nur zum Festlegen von Optionswerten für benutzerdefinierte Tabellen verwendet werden. Verwenden Sie ZUM Anzeigen von Tabelleneigenschaften OBJECTPROPERTY oder Abfrage sys.tables.

Die Option "Text in Zeile" 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 parameter @OptionValue 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 Bildzeichenfolgen werden in der Datenzeile gespeichert, wenn die folgenden Bedingungen gelten:

• Text in Zeile ist aktiviert.
• Die Länge der Zeichenfolge ist kürzer als der in @OptionValue angegebene Grenzwert.
• In der Datenzeile steht genügend Speicherplatz zur Verfügung.

Wenn BLOB-Zeichenfolgen in der Datenzeile gespeichert werden, können das Lesen und Schreiben des Texts, des nTexts oder der Bildzeichenfolgen so schnell wie das Lesen oder Schreiben von Zeichen und binären Zeichenfolgen erfolgen. 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 Platz in der Zeile ist, werden die 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 Zeile zuerst aktiviert ist. Die Zeichenfolgen werden erst konvertiert, wenn sie aktualisiert werden. Ebenso werden die Text-, ntext- oder Bildzeichenfolgen, die bereits in der Datenzeile enthalten sind, nicht konvertiert, um den neuen Grenzwert einzuhalten, bis sie aktualisiert werden.

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 Text in Zeilenoption 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.

Um eine Tabelle von vardecimal storage format zurück in das normale Dezimalspeicherformat zu ändern, muss sich die Datenbank im SIMPLE-Wiederherstellungsmodell befinden. Wenn Sie den Wiederherstellungsmodus ändern, wird die Protokollkette für Sicherungszwecke getrennt. 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 Spalten des großen Werttyps in Ihrer Umgebung verweisen, sollten Sie large_value_types_out_of_row ändern, um eine optimale Leistung zu 1 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 Speicher der Zeichenfolgen wird geändert, sobald sie später 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 die Tabelle dann erneut, nachdem Sie die Einstellung large_value_types_out_of_row geändert haben, oder aktualisieren Sie jede Spalte mit kleinen bis mittleren Großen Werttypen auf sich selbst, sodass der Speicher der Zeichenfolgen mit der Tabellenoption geändert wird. Erstellen Sie die Indizes für die Tabelle nach der Aktualisierung oder Neuauffüllung neu, um die Tabelle zu komprimieren.

Berechtigungen

Zum Ausführen sp_tableoption ist die Berechtigung für die Tabelle erforderlich ALTER .

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

Zugehöriger Inhalt

• sys.tables (Transact-SQL)
• OBJECTPROPERTY (Transact-SQL)
• Gespeicherte Systemprozeduren (Transact-SQL)
• Datenbank-Engine gespeicherten Prozeduren (Transact-SQL)