sp_tableoption (Transact-SQL)

  • Artikel

Gilt für: SQL Server Azure SQL-Datenbank Azure 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.

Wichtig

Das Textfeature in Zeile 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.

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.

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 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 Wenn OFF oder 0 (deaktiviert, der Standardwert), ändert es das aktuelle Verhalten nicht, und es gibt kein BLOB in Zeile.

Wenn angegeben und @OptionValue ist ON (aktiviert) oder ein ganzzahliger Wert aus 24 durch 7000, neuer Text, ntext oder Bildzeichenfolgen werden direkt in der Datenzeile gespeichert. Alle vorhandenen BLOB-Daten (binäres großes Objekt: Text, nText oder Bild) werden beim Aktualisieren des BLOB-Werts in Text im Zeilenformat geändert. 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 außerhalb der Zeile mit einem 16-Byte-Zeiger auf den Stamm gespeichert.

0 = varchar(max), nvarchar(max), varbinary(max), xml, and large UDT values are stored directly in the data row, up to a limit of 8.000 bytes and as long as the value can fit in the record. Wenn der Wert nicht in den Datensatz passt, wird ein Zeiger in Zeile gespeichert, und der Rest wird außerhalb der Zeile im LOB-Speicherplatz gespeichert. 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 TEXTIMAGE_ON Option CREATE TABLE , um einen Speicherort für die Speicherung großer Datentypen anzugeben.
vardecimal storage format Gilt für: SQL Server 2008 (10.0.x) und höher.

Wenn TRUEdie ONangegebene Tabelle für das Vardecimal-Speicherformat aktiviert ist, oder 1wenn die angegebene Tabelle aktiviert ist. OFFWenn FALSEdie Tabelle für das Vardecimal-Speicherformat nicht aktiviert ist, oder 0wenn die Tabelle nicht aktiviert ist. Vardecimal storage format can be enabled only when the database is enabled for vardecimal storage format by using sp_db_vardecimal_storage_format. In SQL Server 2008 (10.0.x) und höher ist das Vardecimal-Speicherformat veraltet. Verwenden Sie ROW stattdessen Komprimierung. Weitere Informationen finden Sie unter Datenkomprimierung. Der Standardwert ist 0 (null).

[ @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.

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 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. Durch das Ändern des Wiederherstellungsmodells wird die Protokollkette für Sicherungszwecke getrennt. Daher sollten Sie nach dem Entfernen des Vardecimal-Speicherformats aus einer Tabelle eine vollständige Datenbanksicherung erstellen.

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