Verwenden von Spalten mit geringer Dichte

Gilt für: SQL Server 2016 (13.x) und spätere Versionen Azure SQL-DatenbankAzure SQL Managed InstanceSQL database in Microsoft Fabric

Spalten mit geringer Dichte sind gewöhnliche Spalten, die einen optimierten Speicher für NULL-Werte haben. Spalten mit geringer Dichte reduzieren die Speicherplatzanforderungen von NULL-Werten auf Kosten eines erhöhten Aufwands, um Werte ungleich NULL abzurufen. Verwenden Sie Sparsespalten, wenn dadurch mindestens 20 Prozent bis 40 Prozent Speicherplatz eingespart werden. Sparse-Spalten und Spaltensätze werden mit den Anweisungen CREATE TABLE bzw. ALTER TABLE definiert.

Spalten mit geringer Dichte können mit Spaltensätzen und gefilterten Indizes verwendet werden:

• Spaltengruppen

  INSERT, UPDATE und DELETE-Anweisungen können über ihren Namen auf die Sparsespalten verweisen. Sie können jedoch auch alle Sparsespalten in einer Tabelle anzeigen und mit ihnen arbeiten, wenn sie zu einer einzelnen XML-Spalte zusammengeschlossen werden. Diese Spalte wird als Spaltensatz bezeichnet. Weitere Informationen zu Spaltensätzen finden Sie unter Verwenden von Spaltensätzen.

• Gefilterte Indizes

  Da Sparsespalten viele Zeilen mit NULL-Werten haben, sind sie besonders für gefilterte Indizes geeignet. Ein gefilterter Index auf einer Sparsespalte kann nur die Zeilen indizieren, die mit Werten belegt sind. Dadurch wird ein kleinerer und effizienterer Index erstellt. Weitere Informationen finden Sie unter erstellen gefilterter Indizes.

Sparsespalten und gefilterte Indizes ermöglichen es Anwendungen wie Windows SharePoint Services, mithilfe von SQL Server eine große Anzahl benutzerdefinierter Eigenschaften effizient zu speichern und darauf zuzugreifen.

Eigenschaften von Spalten mit geringer Dichte

Spalten mit geringer Dichte haben die folgenden Eigenschaften:

• Die SQL Server-Datenbank-Engine 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, belegen die Werte keinen Speicherplatz.

• Katalogsichten für eine Tabelle, die Sparsespalten aufweist, entsprechen denen einer typischen Tabelle. Diese sys.columns-Katalogsicht enthält eine Zeile für jede Spalte in der Tabelle und einen Spaltensatz, wenn einer definiert wurde.

• Spalten mit geringer Dichte sind eine Eigenschaft der Speicherebene und keine Eigenschaft der logischen Tabelle. Daher überträgt eine SELECT ... INTO-Anweisung die Sparsespalteneigenschaft nicht in eine neue Tabelle.

• 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 Sparsespalte explizit aktualisiert wird, wird das entsprechende Bit für diese Sparsespalte 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 Sparsespalten 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-Prozentwert gibt an, wie viel Prozent der Daten NULL sein müssen, um Speicherplatzeinsparungen von 40 Prozent zu erzielen.

Datentypen fester Länge

Datentypen mit von der Genauigkeit abhängiger Länge

Datentypen mit von den Daten abhängiger Länge

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

Mehr Verarbeitungsaufwand im Arbeitsspeicher bei Aktualisierung von Spalten mit geringer Dichte

Beachten Sie beim Entwerfen von Tabellen mit Sparsespalten, dass beim Aktualisieren von Zeilen für jede Sparsespalte in der Tabelle, die nicht NULL ist, 2 zusätzliche Bytes Verarbeitungsaufwand entstehen. Infolge dieses zusätzlichen Speicherbedarfs können Aktualisierungen unerwartet mit Fehler 576 fehlschlagen, wenn die Gesamtzeilengröße einschließlich dieses zusätzlichen Speicheraufwands 8019 überschreitet und keine Spalten aus der Zeile ausgelagert werden können.

Betrachten Sie das Beispiel einer Tabelle mit 600 Sparsespalten 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 Sparsespalten 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 der Aktualisierung entsteht jedoch im Arbeitsspeicher ein zusätzlicher Verarbeitungsaufwand von 2*(Anzahl der Sparsespalten, 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. Infolgedessen schlägt das Update mit dem Fehler 576 fehl.

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

Spärliche Spalten können einen beliebigen SQL Server-Datentyp aufweisen und verhalten sich wie alle anderen Spalten, mit folgenden Einschränkungen:

• Eine geringe Spalte muss nullwertig sein und darf nicht über die ROWGUIDCOL- oder IDENTITY -Eigenschaften verfügen. Eine Sparsespalte darf nicht die folgenden Datentypen annehmen: text, ntext, image, timestamp, benutzerdefinierter Datentyp, geometryoder geography; sie darf auch nicht über das FILESTREAM-Attribut verfügen.

• Eine Sparsespalte kann keinen Standardwert haben.

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

• Obwohl eine berechnete Spalte eine Sparse-Spalte enthalten kann, kann eine berechnete Spalte nicht als SPARSE markiert werden.

• Eine Datenmaske kann für eine Sparsespalte definiert werden, jedoch nicht für eine Sparsespalte, die Teil eines Spaltensatzes ist.

• Eine Spalte mit geringer Dichte kann nicht Teil eines Clustered Index oder eines eindeutigen Primärschlüsselindex sein. Allerdings können sowohl persistierte als auch nicht persistierte berechnete Spalten, die auf Sparsespalten basieren, Teil eines gruppierten Schlüssels sein.

• Eine Sparsespalte kann nicht als Partitionsschlüssel eines gruppierten Indexes oder eines Heaps verwendet werden. Eine Sparsespalte kann jedoch als Partitionsschlüssel eines nicht gruppierten Indexes verwendet werden.

• Eine Sparsespalte 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, Sparsespalten komprimierten Tabellen hinzuzufügen, und Tabellen mit Sparsespalten können nicht komprimiert werden.

• Das Ändern einer Spalte von SPARSE zu Nicht-SPARSE oder von Nicht-SPARSE zu SPARSE erfordert eine Änderung des Speicherformats der Spalte. Die SQL Server-Datenbank-Engine verwendet die folgende Prozedur, um diese Änderung auszuführen:

  1. Fügt der Tabelle eine neue Spalte in der neuen Speichergröße und dem neuen Format hinzu.

  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 (wenn es keinen gruppierten Index gibt) oder erstellt den gruppierten Index neu, um den von der alten Spalte verwendeten Speicherplatz freizugeben.

  Note

  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 Sparsespalten enthalten, oder 8018 Bytes für Tabellen, die Sparsespalten enthalten. Dieser Fehler kann auftreten, auch wenn alle in Frage kommenden Spalten aus den Zeilen verschoben wurden.

• Wenn Sie eine nicht als SPARSE definierte Spalte in eine SPARSE-Spalte umwandeln, belegt die SPARSE-Spalte für Nicht-NULL-Werte mehr Speicherplatz. 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 Sparsespalten in den folgenden SQL Server-Technologien unterstützt werden:

• Transaktionsreplikation

  Die Transaktionsreplikation unterstützt Sparsespalten, unterstützt jedoch keine Spaltensätze, die zusammen mit Sparsespalten verwendet werden können. 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 Sparse-Spalten. Wenn Sie Daten für eine frühere Version replizieren müssen, legen Sie fest, dass das SPARSE-Attribut nicht repliziert werden sollte.

  Für veröffentlichte Tabellen können Sie keine neuen SPARSE-Spalten hinzufügen und die SPARSE-Eigenschaft einer vorhandenen Spalte nicht ändern. Wenn ein solcher Vorgang erforderlich ist, löschen Sie die Veröffentlichung, und erstellen Sie sie erneut.

• Mergereplikation

  Die Mergereplikation unterstützt keine Sparse-Spalten oder Spaltensätze.

• Änderungsnachverfolgung

  Die Nachverfolgung von Änderungen unterstützt dünnbesetzte Spalten und Spaltensätze. Wenn ein Spaltensatz in einer Tabelle aktualisiert wird, behandelt die Änderungsnachverfolgung diese als Update für die gesamte Zeile. Es steht keine detaillierte Änderungsnachverfolgung zur Verfügung, um die genaue Menge der Sparse-Spalten zu ermitteln, die über den Aktualisierungsvorgang des Spaltensatzes aktualisiert werden. Wenn die Sparse-Spalten explizit durch eine DML-Anweisung aktualisiert werden, funktioniert die Änderungsnachverfolgung wie gewohnt und kann die genaue Menge der geänderten Spalten identifizieren.

• Erfassung geänderter Daten

  Change Data Capture unterstützt Sparse Columns, aber keine Spaltenmengen.

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

Examples

In diesem Beispiel enthält eine Dokumenttabelle eine gemeinsame Menge, die die Spalten DocID und Title enthält. 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. Der Code in diesem Beispiel erstellt eine Tabelle, die Sparse-Spalten verwendet, fügt zwei Zeilen in die Tabelle ein und wählt anschließend Daten aus der Tabelle aus.

USE AdventureWorks2022;  
GO  
  
CREATE TABLE 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 DocumentStore(DocID, Title, ProductionSpecification, ProductionLocation)  
VALUES (1, 'Tire Spec 1', 'AXZZ217', 27);  
GO  
  
INSERT DocumentStore(DocID, Title, MarketingSurveyGroup)  
VALUES (2, 'Survey 2142', 'Men 25 - 35');  
GO  

Wenn Sie alle Spalten in der Tabelle auswählen, wird ein herkömmlicher Ergebnissatz zurückgegeben.

SELECT * FROM DocumentStore ;  

Hier sehen Sie das Ergebnis.

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 ;  

Hier sehen Sie das Ergebnis.

DocID Title ProductionSpecification ProductionLocation

1 Tire Spec 1 AXZZ217 27

Siehe auch

• Verwenden von Spaltensätzen
• CREATE TABLE (Transact-SQL)
• ALTER TABLE (Transact-SQL)
• sys.columns (Transact-SQL)