Freigeben über

  • Artikel

Daten in Zeilen

Kleine bis mittelgroße Werttypen (varchar(max), nvarchar(max), varbinary(max) und xml) sowie LOB-Datentypen (Large Object) (text, ntext und image) können in einer Datenzeile gespeichert werden.Dieses Verhalten wird durch Verwenden der beiden Optionen in der gespeicherten Systemprozedur sp_tableoption gesteuert: mit der large value types out of row-Option für große Werttypen und mit der text in row-Option für große Objekttypen. Diese Optionen eignen sich am besten für Tabellen, in denen Datenwerte eines dieser Datentypen gewöhnlich in einem Schritt gelesen oder geschrieben werden, und die meisten Anweisungen, die auf die Tabelle verweisen, beziehen sich auf diese Datentypen. In Abhängigkeit von den Nutzungs- oder Arbeitsauslastungsmerkmalen kann das Speichern von Daten in Zeilen mitunter nicht geeignet sein.

Wichtiger HinweisWichtig

Die text in row-Option wird in einer künftigen Version von SQL Server entfernt. Vermeiden Sie die Verwendung dieser Option beim Entwickeln neuer Anwendungen, und planen Sie die Änderung von Anwendungen, die die Option text in row zurzeit verwenden. Es wird empfohlen, große Daten mithilfe der Datentypen varchar(max), nvarchar(max) oder varbinary(max) zu speichern. Zum Steuern des Verhaltens dieser Datentypen in Zeilen (in-row) und außerhalb von Zeilen (out-of-row) die large value types out of row-Option.

Sofern die text in row-Option nicht auf ON oder auf einen bestimmten in-row-Grenzwert gesetzt wurde, gelten text-, ntext- oder image-Zeichenfolgen als große Zeichen- oder Binärzeichenfolgen (bis zu 2 GB), die außerhalb einer Datenzeile gespeichert werden. Die Datenzeile enthält nur einen 16-Byte-Textzeiger, der auf den Stammknoten einer Struktur zeigt, die aus internen Zeigern besteht. Diese Zeiger dienen der Zuordnung der Seiten, in denen die Zeichenfolgenfragmente gespeichert sind. Weitere Informationen zum Speichern von text-, ntext- oder image-Zeichenfolgen finden Sie unter Verwenden von text- und image-Daten.

Sie können eine text in row-Option für Tabellen festlegen, die LOB-Datentypspalten enthalten. Darüber hinaus können Sie für die text in row-Option einen Grenzwert zwischen 24 und 7.000 Byte angeben.

Wenn die Option large value types out of row nicht auf ON festgelegt ist, werden auch Spalten mit varchar(max), nvarchar(max), varbinary(max) und xml, wenn möglich, in der der Datenzeile gespeichert. Wenn das der Fall ist, versucht SQL Server Database Engine (Datenbankmodul), den jeweiligen Wert anzupassen, falls dies möglich ist, und verschiebt den Wert ansonsten außerhalb der Zeile.Wenn large value types out of row auf ON gesetzt wurde, werden die Werte außerhalb der Zeile (off-row) gespeichert, und es wird nur ein 16-Byte-Zeiger im Datensatz gespeichert.

HinweisHinweis

Der maximale Speicher in Zeilen für große Datenwerttypen ist auf 8.000 Byte gesetzt, wenn large value types out of row den Wert OFF hat. Anders als bei der text in row-Option können Sie den in-row-Grenzwert für Spalten in der Tabelle angeben.

Wenn eine Tabelle so konfiguriert ist, dass sie entweder große Werttypen oder große Objektdatentypen direkt in der Datenzeile speichert, befinden sich die eigentlichen Spaltenwerte in der Zeile (in-row), wenn eine der folgenden Bedingungen zutrifft:

  • Die Länge der Zeichenfolge unterschreitet den für Spalten mit text, ntext und image angegebenen Grenzwert.

  • In der Datenzeile steht ausreichend Speicherplatz für die Zeichenfolge zur Verfügung.

Wenn ein Spaltenwert vom Typ großer Werttyp oder großer Objektdatentyp in der Datenzeile gespeichert wird, muss Database Engine (Datenbankmodul) nicht auf eine separate Seite oder eine separate Gruppe von Seiten zugreifen, um die Zeichen- oder Binärzeichenfolge zu lesen oder zu schreiben. Dadurch ist das Lesen und Schreiben der Zeichenfolgen in Zeilen etwa so schnell wie das Lesen und Schreiben von varchar-, nvarchar- oder varbinary-Zeichenfolgen. Umgekehrt gilt: Wenn die Werte außerhalb von Zeilen (off-row) gespeichert werden, muss Database Engine (Datenbankmodul) einen zusätzlichen Lese- oder Schreibvorgang der Seite durchführen.

Falls bei großen Objektdatentypen die Zeichenfolge länger als der Grenzwert für die Option text in row oder der in der Zeile verfügbare Speicherplatz ist, werden die Zeiger, die ansonsten im Stammknoten der Zeigerstruktur gespeichert werden, in der Zeile gespeichert. Die Zeiger werden in der Zeile gespeichert, wenn eine der folgenden Bedingungen zutrifft:

  • Der zum Speichern der Zeiger benötigte Speicherplatz ist geringer als der für die Option text in row angegebene Grenzwert.

  • In der Datenzeile steht ausreichend Speicherplatz für die Zeiger zur Verfügung.

Wenn Zeiger vom Stammknoten in die Zeile verschoben werden, muss Database Engine (Datenbankmodul) keinen Stammknoten verwenden. Dies kann einen Seitenzugriff eliminieren, wenn die Zeichenfolge gelesen oder geschrieben wird. Dadurch wird die Leistung verbessert.

Falls Stammknoten verwendet werden, werden diese als eines der Zeichenfolgenfragmente in einer LOB-Seite gespeichert und können maximal fünf interne Zeiger aufnehmen. Database Engine (Datenbankmodul) benötigt in der Zeile 72 Byte an Speicherplatz, um fünf Zeiger für eine Zeichenfolge in der Zeile zu speichern. Falls in der Zeile ausreichend Speicherplatz für die Zeiger vorhanden ist, wenn die text in row-Option auf ON oder die large value types out of row-Option auf OFF gesetzt ist, muss Database Engine (Datenbankmodul) möglicherweise eine 8-KB-Seite zum Speichern der Zeiger zuordnen. Wenn die Datenlänge des Werts 40.200 Byte übersteigt, sind mehr als fünf Zeiger in der Zeile erforderlich, wodurch nur 24 Byte in der Hauptzeile gespeichert werden und eine zusätzliche Datenseite im LOB-Speicherbereich zugeordnet wird.

Wenn große Zeichenfolgen in der Zeile gespeichert werden, werden sie ähnlich wie Zeichenfolgen variabler Länge gespeichert. Database Engine (Datenbankmodul) sortiert Spalten der Größe nach absteigend und verschiebt Werte außerhalb von Zeilen, bis die verbleibenden Spalten in die Datenseite (8 K) passen.

Aktivieren und Deaktivieren der Option "large value types out of row"

Sie können die large value types out of row-Option für eine Tabelle mit sp_tableoption wie folgt aktivieren:

sp_tableoption N'MyTable', 'large value types out of row', 'ON'

Wenn Sie OFF angeben, wird der in-row-Grenzwert für varchar(max)-, nvarchar(max)-, varbinary(max)- und xml-Spalten auf 8.000 Bytes festgelegt.In der Zeile wird nur ein 16-Byte-Stammzeiger gespeichert, und der Wert wird im LOB-Speicherbereich gespeichert. Es wird empfohlen, diese Option für Tabellen auf ON zu setzen, in denen die meisten Anweisungen nicht auf die Spalten mit großen Werttypen verweisen. Das Speichern dieser Spalten außerhalb der Zeile impliziert, dass mehr Zeilen in jede Seite passen, wodurch die Anzahl der zum Scannen der Tabelle benötigten E/A-Vorgänge reduziert wird.

Wenn der Wert dieser Option auf OFF gesetzt ist, können viele Zeichenfolgen letztendlich in der Zeile selbst gespeichert werden, was potenziell zur Reduzierung der Anzahl der Datenreihen führt, die in jede Seite passen. Wenn die meisten Anweisungen, die auf die Tabelle verweisen, nicht auf die varchar(max)-, nvarchar(max)-, varbinary(max)- und xml-Spalten zugreifen, kann durch das Reduzieren der Zeilen in einer Seite die Anzahl der Seiten zunehmen, die zum Verarbeiten von Abfragen gelesen werden müssen.Durch das Reduzieren der Zeilen pro Seite kann die Anzahl der Seiten, die gescannt werden müssen, falls der Abfrageoptimierer keinen entsprechenden Index findet, zunehmen.

Sie können sp_tableoption auch zum Deaktivieren der out-of-row-Option verwenden:

sp_tableoption N'MyTable', 'large value types out of row', 'OFF'

Wenn der Wert der Option large value types out of row geändert wird, werden vorhandene varchar(max)-, nvarchar(max)-, varbinary(max)- und xml-Werte nicht sofort konvertiert.Die Speicherung der Zeichenfolgen wird erst bei einer nachfolgenden Aktualisierung geändert. Alle neuen in eine Tabelle eingegebenen Werte werden entsprechend der festgelegten Tabellenoption gespeichert.

Wenn Sie den Wert der large value types out of row-Option für eine bestimmte Tabelle überprüfen möchten, führen Sie eine Abfrage der large_value_types_out_of_row-Spalte der sys.tables-Katalogsicht aus. Diese Spalte entspricht dem Wert 0, wenn für die Tabelle large value types out of row nicht aktiviert ist, und sie entspricht dem Wert 1, wenn Typen für hohe Werte außerhalb der Zeile gespeichert werden.

Aktivieren und Deaktivieren der Option "text in row"

Sie können die text in row-Option für eine Tabelle mit sp_tableoption wie folgt aktivieren:

sp_tableoption N'MyTable', 'text in row', 'ON'

Wahlweise können Sie einen Höchstwert von 24 bis 7.000 Byte für die Länge einer text-, ntext- und image-Zeichenfolge angeben, die in einer Datenzeile gespeichert werden kann:

sp_tableoption N'MyTable', 'text in row', '1000'

Wenn Sie ON anstelle eines bestimmten Grenzwerts angeben, wird standardmäßig ein Grenzwert von 256 Byte verwendet.Dieser Standardwert ermöglicht die meisten Leistungsvorteile, die die Option text in row bietet. Zwar sollten Sie den Wert im Allgemeinen nicht auf eine Einstellung, die niedriger als 72 ist, festlegen, die Einstellung darf jedoch auch nicht zu hoch festgelegt werden. Dies gilt speziell für Tabellen, in denen die meisten Anweisungen nicht auf die text-, ntext- und image-Spalten verweisen oder in denen es mehrere text-, ntext- und image-Spalten gibt.

Wenn Sie einen hohen Wert für text in row festlegen und viele Zeichenfolgen in der Zeile selbst gespeichert werden, kann die Anzahl der Datenzeilen, die in jede Seite passen, deutlich reduziert werden. Wenn die meisten Anweisungen, die auf die Tabelle verweisen, nicht auf die text-, ntext- oder image-Spalten zugreifen, kann durch das Reduzieren der Zeilen in einer Seite die Anzahl der Seiten zunehmen, die zum Verarbeiten von Abfragen gelesen werden müssen. Durch das Reduzieren der Zeilen pro Seite kann die Größe von Indizes und der Seiten, die gescannt werden müssen, falls der Abfrageoptimierer keinen entsprechenden Index findet, zunehmen. Der Standardwert von 256 für text in row ist hoch genug, um sicherzustellen, dass kleine Zeichenfolgen und die Stammknotenzeiger in den Zeilen gespeichert werden, jedoch nicht so hoch, dass die Anzahl der Zeilen pro Seite so stark reduziert wird, dass die Leistung beeinträchtigt wird.

Die text in row-Option wird für Variablen mit einem Tabellendatentyp und für Tabellen auf 256 festgelegt, die durch benutzerdefinierte Funktionen zurückgegeben werden und einen table-Wert zurückgeben. Diese Einstellung kann nicht geändert werden.

Mit sp_tableoption können Sie diese Option auch deaktivieren, indem Sie als Wert OFF oder 0 angeben:

sp_tableoption N'MyTable', 'text in row', 'OFF'

Um den Wert der text in row-Option für eine bestimmte Tabelle zu überprüfen, führen Sie eine Abfrage der text_in_row_limit-Spalte der sys.tables-Katalogsicht durch. Diese Spalte hat den Wert 0, wenn für die Tabelle die Option text in row nicht aktiviert ist, oder einen Wert größer als 0, wenn der in-row-Grenzwert festgelegt wurde.

Auswirkungen der Option text in row

Die text in row-Option hat die folgenden Auswirkungen:

  • Nachdem Sie die Option text in row aktiviert haben, können Sie mit den READTEXT-, UPDATETEXT- oder WRITETEXT-Anweisungen nicht Teile von text-, ntext- oder image-Werten ändern, die in der Tabelle gespeichert sind. In SELECT-Anweisungen können Sie eine vollständige text-, ntext- oder image-Zeichenfolge lesen oder mit der SUBSTRING-Funktion Teile der Zeichenfolge lesen. Alle INSERT- oder UPDATE-Anweisungen, die auf die Tabelle verweisen, müssen vollständige Zeichenfolgen angeben und können nicht nur einen Teil von text-, ntext- oder image-Zeichenfolgen ändern.

  • Wenn die Option text in row aktiviert ist, werden vorhandene text-, ntext- oder image-Zeichenfolgen nicht sofort in Zeichenfolgen konvertiert, die in der Zeile gespeichert sind. Die Zeichenfolgen werden nur konvertiert, wenn sie anschließend aktualisiert werden. Jede text-, ntext- oder image-Zeichenfolge, die nach dem Aktivieren der text in row-Option eingefügt wird, wird als Zeichenfolge in die Zeile eingefügt.

  • Das Deaktivieren der Option text in row kann ein langwieriger, protokollierter Vorgang sein. Die Tabelle wird gesperrt und alle in der Zeile gespeicherten text-, ntext- und image-Zeichenfolgen werden in reguläre text-, ntext- und image-Zeichenfolgen konvertiert. Wie lange der Befehl ausgeführt wird und wie viele Daten geändert werden, hängt davon ab, wie viele text-, ntext- und image-Zeichenfolgen von in der Zeile gespeicherten Zeichenfolgen in reguläre Zeichenfolgen konvertiert werden müssen.

  • Die text in row-Option hat auf die Funktion des SQL Server Native Client-OLE DB-Anbieters oder des SQL Server Native Client-ODBC-Treibers keine anderen Auswirkungen, als dass der Zugriff auf die text-, ntext- und image-Daten beschleunigt wird.

  • Die Text- und Bildfunktionen der DB-Library, wie z. B. dbreadtext und dbwritetext, können nicht in einer Tabelle verwendet werden, nachdem die Option text in row aktiviert wurde.