Verwenden von Spalten mit geringer Dichte

Eigenschaften von Spalten mit geringer Dichte

Spalten mit geringer Dichte haben die folgenden Eigenschaften:

• SQL Server Database Engine (Datenbankmodul) verwendet das Schlüsselwort SPARSE in einer Spaltendefinition, um die Speicherung von Werten in dieser Spalte zu optimieren. Wenn der Spaltenwert in einer Zeile der Tabelle NULL ist, belegt der Wert keinen Speicherplatz.

• Katalogsichten für eine Tabelle, die Spalten mit geringer Dichte aufweist, entsprechen denen einer typischen Tabelle. Diese sys.columns-Katalogsicht enthält eine Zeile für jede Spalte in der Tabelle sowie einen Spaltensatz, sofern definiert.

• Spalten mit geringer Dichte sind eine Eigenschaft der Speicherebene und keine Eigenschaft der logischen Tabelle. Daher wird eine SELECT…INTO-Anweisung nicht über die Eigenschaft der Spalte mit geringer Dichte in eine neue Tabelle kopiert.

• Die COLUMNS_UPDATED-Funktion gibt einen varbinary-Wert zurück, mit dem alle Spalten, die während einer DML-Aktion aktualisiert wurden, gekennzeichnet werden. Die Bits, die von der COLUMNS_UPDATED-Funktion zurückgegeben werden, lauten folgendermaßen:

  • Wenn eine Spalte mit geringer Dichte explizit aktualisiert wird, wird das entsprechende Bit für diese Spalte mit geringer Dichte auf 1 festgelegt, und das Bit für den Spaltensatz wird auf 1 festgelegt.

  • Wenn ein Spaltensatz explizit aktualisiert wird, wird das Bit für den Spaltensatz auf 1 festgelegt, und die Bits für alle Spalten mit geringer Dichte in dieser Tabelle werden auf 1 festgelegt.

  • Für Einfügevorgänge werden alle Bits auf 1 festgelegt.

  Weitere Informationen zu Spaltensätzen finden Sie unter Verwenden von Spaltensätzen.

Die folgenden Datentypen können nicht als SPARSE festgelegt werden:

Geschätzte Speicherplatzeinsparungen nach Datentyp

Spalten mit geringer Dichte benötigen mehr Speicherplatz für Werte, die ungleich NULL sind, als für identische Daten benötigt wird, die nicht als SPARSE gekennzeichnet wurden. Die folgenden Tabellen geben die Speicherplatznutzung für jeden Datentyp an. Die Spalte NULL-Prozentsatz gibt an, wie viel Prozent der Daten NULL sein müssen, um Speicherplatzeinsparungen von 40 Prozent zu erzielen.

Datentypen fester Länge

Datentypen präzisionsabhängiger Länge

Datentypen datenabhängiger Länge

*Die Länge ist gleich dem Mittelwert der im Typ enthaltenen Daten, plus 2 oder 4 Bytes.

Mehr Verarbeitungsaufwand im Arbeitsspeicher beim Aktualisieren von Spalten mit geringer Dichte

Beachten Sie beim Entwerfen von Tabellen mit Spalten mit geringer Dichte, dass beim Aktualisieren von Zeilen für jede Spalte mit geringer Dichte in der Tabelle, die nicht NULL ist, 2 zusätzliche Bytes Verarbeitungsaufwand entstehen. Aufgrund dieses zusätzlichen Speicherbedarfs kann bei Updates Fehler 576 auftreten, wenn die Gesamtzeilengröße, einschließlich dieser Vergrößerung im Arbeitsspeicher, 8019 überschreitet und keine Spalten aus der Zeile geschoben werden können.

Angenommen, eine Tabelle enthält 600 Spalten mit geringer Dichte des Typs bigint. Wenn davon 571 Spalten nicht NULL sind, beträgt die Gesamtgröße auf dem Datenträger 571 * 12 = 6852 Bytes. Nachdem die zusätzliche Zeilengröße und der Header für Spalten mit geringer Dichte hinzugefügt wurden, erhöht sich dies auf etwa 6895 Bytes. Für die Seite sind immer noch etwa 1124 Bytes auf dem Datenträger verfügbar. Dadurch kann der Eindruck entstehen, dass zusätzliche Spalten erfolgreich aktualisiert werden können. Während des Updates entsteht im Arbeitsspeicher ein zusätzlicher Verarbeitungsaufwand von 2*(Anzahl der Spalten mit geringer Dichte, die nicht NULL sind). In diesem Beispiel erhöht der zusätzliche Verarbeitungsaufwand – 2 * 571 = 1142 Bytes – die Zeilengröße auf dem Datenträger auf etwa 8037 Bytes. Diese Größe überschreitet die maximal zulässige Größe von 8019 Bytes. Da alle Spalten Datentypen fester Länge aufweisen, können sie nicht von der Zeile geschoben werden. Als Ergebnis tritt beim Aktualisieren der Fehler 576 auf.

Einschränkungen für die Verwendung von Spalten mit geringer Dichte

Spalten mit geringer Dichte können jeden SQL Server-Datentyp annehmen und sich wie andere Spalten mit den folgenden Einschränkungen verhalten:

• Eine Spalte mit geringer Dichte muss auf NULL festlegbar sein und darf nicht über die ROWGUIDCOL-Eigenschaft oder die IDENTITY-Eigenschaft verfügen.

• Eine Spalte mit geringer Dichte darf nicht die folgenden Datentypen annehmen: text, ntext, image, timestamp, benutzerdefinierter Datentyp, geometry oder geography; sie darf auch nicht über das FILESTREAM-Attribut verfügen.

• Eine Spalte mit geringer Dichte kann keinen Standardwert haben.

• Eine Spalte mit geringer Dichte kann nicht an eine Regel gebunden sein.

• Obwohl eine berechnete Spalte eine Spalte mit geringer Dichte enthalten kann, kann eine berechnete Spalte nicht als SPARSE markiert werden.

• Eine Spalte mit geringer Dichte kann nicht Teil eines gruppierten Indexes oder eines eindeutigen Primärschlüsselindexes sein. Sowohl permanente als auch nicht permanente berechnete Spalten, die für Spalten mit geringer Dichte definiert wurden, können Teil eines gruppierten Schlüssels sein.

• Eine Spalte mit geringer Dichte kann nicht als Partitionsschlüssel eines gruppierten Indexes oder eines Heaps verwendet werden. Eine Spalte mit geringer Dichte kann jedoch als Partitionsschlüssel eines nicht gruppierten Indexes verwendet werden.

• Eine Spalte mit geringer Dichte kann nicht Teil eines benutzerdefinierten Tabellentyps sein, der in Tabellenvariablen und Tabellenwertparametern verwendet wird.

• Spalten mit geringer Dichte sind inkompatibel mit der Datenkomprimierung. Daher ist es nicht möglich, Spalten mit geringer Dichte zu komprimierten Tabellen hinzuzufügen, und Tabellen mit Spalten mit geringer Dichte können nicht komprimiert werden.

• Um eine Spalte mit geringer Dichte in eine Spalte ohne geringe Dichte zu ändern oder um eine Spalte ohne geringe Dichte in eine Spalte mit geringer Dichte zu ändern, muss das Speicherformat der Spalte geändert werden. SQL Server Database Engine (Datenbankmodul) ändert das Format mithilfe des folgenden Verfahrens:

  1. Der Tabelle wird eine neue Spalte in der neuen Speichergröße und dem neuen Format hinzugefügt.

  2. Der in der alten Spalte gespeicherte Wert wird für jede Zeile in der Tabelle in die neue Spalte aktualisiert und kopiert.

  3. Entfernt die alte Spalte aus dem Tabellenschema.

  4. Erstellt die Tabelle neu, um den von der alten Spalte belegten Platz freizugeben.

  Hinweis

  Schritt 2 kann fehlschlagen, wenn die Größe der Daten in der Zeile die maximal zulässige Zeilengröße überschreitet. Diese Größe enthält die Größe der in der alten Spalte gespeicherten Daten und der in der neuen Spalte gespeicherten aktualisierten Daten. Diese Grenze beträgt 8060 Bytes für Tabellen, die keine Spalten mit geringer Dichte enthalten, oder 8018 Bytes für Tabellen, die Spalten mit geringer Dichte enthalten. Dieser Fehler kann auftreten, auch wenn alle in Frage kommenden Spalten aus den Zeilen verschoben wurden. Weitere Informationen finden Sie unter Zeilenüberlauf bei Daten über 8 KB.

• Wenn Sie eine Spalte ohne geringe Dichte in eine Spalte mit geringer Dichte ändern, belegt die Spalte mit geringer Dichte mehr Speicherplatz für Werte ungleich NULL. Wenn eine Zeile die maximale Zeilengrößenbeschränkung fast erreicht hat, kann der Vorgang fehlschlagen.

SQL Server-Technologien, die Spalten mit geringer Dichte unterstützen

In diesem Abschnitt wird beschrieben, wie Spalten mit geringer Dichte in den folgenden SQL Server-Technologien unterstützt werden:

• Transaktionsreplikation

  Transaktionsreplikation unterstützt die Spalten mit geringer Dichte. Spaltensätze, die zusammen mit Spalten mit geringer Dichte verwendet werden können, werden jedoch nicht unterstützt. Weitere Informationen zu Spaltensätzen finden Sie unter Verwenden von Spaltensätzen.

  Die Replikation des SPARSE-Attributs wird durch eine Schemaoption bestimmt, die mit sp_addarticle oder über das Dialogfeld Artikeleigenschaften in SQL Server Management Studio festgelegt wird. Frühere Versionen von SQL Server unterstützen keine Spalten mit geringer Dichte. Wenn Sie Daten für eine frühere Version replizieren müssen, legen Sie fest, dass das SPARSE-Attribut nicht repliziert werden sollte.

  Veröffentlichten Tabellen können Sie keine neuen Spalten mit geringer Dichte hinzufügen; Sie können auch nicht die SPARSE-Eigenschaft einer vorhandenen Spalte ändern. Wenn ein solcher Vorgang erforderlich ist, löschen Sie die Veröffentlichung, und erstellen Sie sie erneut.

• Mergereplikation

  Mergereplikation unterstützt keine Spalten mit geringer Dichte und keine Spaltensätze.

• Änderungsnachverfolgung

  Änderungsnachverfolgung unterstützt Spalten mit geringer Dichte und Spaltensätze. Wenn ein Spaltensatz in einer Tabelle aktualisiert wird, behandelt die Änderungsnachverfolgung diese als Aktualisierung für die gesamte Zeile. Es steht keine detaillierte Änderungsnachverfolgung zur Verfügung, um den exakten Satz an Spalten mit geringer Dichte abzurufen, die über den Spaltensatzaktualisierungsvorgang aktualisiert werden. Wenn die Spalten mit geringer Dichte explizit über eine DML-Anweisung aktualisiert werden, arbeitet die Änderungsnachverfolgung wie gewohnt und identifiziert den exakten Satz an geänderten Spalten.

• Change Data Capture

  Change Data Capture unterstützt Spalten mit geringer Dichte, aber keine Spaltensätze.

• Die SPARSE-Eigenschaft einer Spalte wird beim Kopieren der Tabelle nicht beibehalten.

Beispiel

In diesem Beispiel enthält eine Dokumenttabelle einen allgemeinen Satz mit der DocID-Spalte und der Title-Spalte. Die Produktionsgruppe möchte eine ProductionSpecification-Spalte und eine ProductionLocation-Spalte für alle Produktionsdokumente. Die Marketinggruppe möchte eine MarketingSurveyGroup-Spalte für Marketingdokumente. Mit dem Code in diesem Beispiel wird eine Tabelle ausgegeben, in der Spalten mit geringer Dichte verwendet werden. Es werden Zeilen in die Tabelle eingefügt und Daten aus der Tabelle ausgewählt.

Hinweis
Diese Tabelle hat nur fünf Spalten, um die Anzeige und das Lesen zu erleichtern. Sie können optional die Spalten mit geringer Dichte so deklarieren, dass NULL-Werte zulässig sind, wenn die ANSI_NULL_DFLT_ON-Option festgelegt wurde.

USE AdventureWorks2008R2;
GO

CREATE TABLE dbo.DocumentStore
(DocID int PRIMARY KEY,
Title varchar(200) NOT NULL,
ProductionSpecification varchar(20) SPARSE NULL,
ProductionLocation smallint SPARSE NULL,
MarketingSurveyGroup varchar(20) SPARSE NULL ) ;
GO

INSERT dbo.DocumentStore(DocID, Title, ProductionSpecification, ProductionLocation)
VALUES (1, 'Tire Spec 1', 'AXZZ217', 27);
GO

INSERT dbo.DocumentStore(DocID, Title, MarketingSurveyGroup)
VALUES (2, 'Survey 2142', 'Men 25 - 35');
GO

Bei Auswählen aller Spalten in der Tabelle wird ein herkömmliches Resultset zurückgegeben.

SELECT DocID, Title, ProductionSpecification, ProductionLocation, MarketingSurveyGroup
FROM DocumentStore ;

Dies ist das Resultset.

DocID  Title        ProductionSpecification  ProductionLocation  MarketingSurveyGroup

------ ------------ ----------------------- ------------------- ---------------------

1      Tire Spec 1  AXZZ217                  27                  NULL

2      Survey 2142  NULL                     NULL                Men 25-35

Da die Produktionsabteilung nicht an den Marketingdaten interessiert ist, möchte sie eine Spaltenliste verwenden, die nur die für sie wichtigen Spalten zurückgibt, wie in der folgenden Abfrage gezeigt.

SELECT DocID, Title, ProductionSpecification, ProductionLocation 
FROM DocumentStore 
WHERE ProductionSpecification IS NOT NULL ;

Dies ist das Resultset.

DocID  Title        ProductionSpecification  ProductionLocation

----- ------------ ----------------------- ------------------

1      Tire Spec 1  AXZZ217                  27

Siehe auch

Verweis

Konzepte