Freigeben über

  • Artikel

Verwenden von Spalten mit geringer Dichte

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 Spalten mit geringer Dichte, wenn dadurch mindestens 20 Prozent bis 40 Prozent Speicherplatz eingespart werden. Spalten mit geringer Dichte und Spaltensätze werden mit der CREATE TABLE-Anweisung oder der ALTER TABLE-Anweisung definiert.

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

  • Spaltensätze

    Die Anweisungen INSERT, UPDATE und DELETE können anhand des Namens auf die Spalten mit geringer Dichte verweisen. Sie können jedoch auch alle Spalten mit geringer Dichte 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 Spalten mit geringer Dichte viele Zeilen mit NULL-Werten haben, sind sie besonders für gefilterte Indizes geeignet. Ein gefilterter Index für eine Spalte mit geringer Dichte kann nur die Zeilen indizieren, die Werte enthalten. Dadurch wird ein kleinerer und effizienterer Index erstellt. Weitere Informationen finden Sie unter Richtlinien für den Entwurf gefilterter Indizes.

Mithilfe von Spalten mit geringer Dichte und von gefilterten Indizes können Anwendungen wie Windows SharePoint Services große Mengen an benutzerdefinierten Eigenschaften mit SQL Server 2008 speichern und darauf zugreifen.

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, belegen die Werte keinen Speicherplatz.

  • Katalogsichten für eine Tabelle, die Spalten mit geringer Dichte aufweist, entsprechen denen einer typischen Tabelle. Die Katalogsicht sys.columns enthält eine Zeile für jede Spalte in der Tabelle und einen Spaltensatz, wenn dieser definiert wurde.

  • Spalten mit geringer Dichte sind eine Eigenschaft der Speicherebene und nicht der logischen Tabelle. Die Anweisung SELECT…INTO kann diese Eigenschaft daher nicht in eine neue Tabelle kopieren.

  • 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:

geography

text

geometry

timestamp

image

user-defined data types

ntext

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

Datentyp

Bytes ohne geringe Dichte

Bytes mit geringer Dichte

NULL Prozent

bit

0.125

4.125

98%

tinyint

1

5

86%

smallint

2

6

76%

int

4

8

64%

bigint

8

12

52%

real

4

8

64%

float

8

12

52%

smallmoney

4

8

64%

money

8

12

52%

smalldatetime

4

8

64%

datetime

8

12

52%

uniqueidentifier

16

20

43%

date

3

7

69%

Datentypen präzisionsabhängiger Länge

Datentyp

Bytes ohne geringe Dichte

Bytes mit geringer Dichte

NULL Prozent

datetime2(0)

6

10

57%

datetime2(7)

8

12

52%

time(0)

3

7

69%

time(7)

5

9

60%

datetimetoffset(0)

8

12

52%

datetimetoffset (7)

10

14

49%

decimal/numeric(1,s)

5

9

60%

decimal/numeric(38,s)

17

21

42%

vardecimal(p,s)

Verwenden Sie den decimal-Typ als konservative Schätzung.

Datentypen datenabhängiger Länge

Datentyp

Bytes ohne geringe Dichte

Bytes mit geringer Dichte

NULL Prozent

sql_variant

Ändert sich mit dem zugrunde liegenden Datentyp

varchar oder char

2*

4*

60%

nvarchar oder nchar

2*

4*+

60%

varbinary oder binary

2*

4*

60%

xml

2*

4*

60%

hierarchyid

2*

4*

60%

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

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 Index 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 Index oder eines Heaps verwendet werden. Eine Spalte mit geringer Dichte kann jedoch als Partitionsschlüssel eines nicht gruppierten Index verwendet werden.

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

  • Datenkomprimierung und Spalten mit geringer Dichte sind nicht kompatibel. Spalten mit geringer Dichte können daher komprimierten Tabellen nicht hinzugefügt werden, und Tabellen, die Spalten mit geringer Dichte enthalten, können nicht komprimiert werden.

  • Um eine Spalte mit geringer Dichte in eine Spalte ohne geringe Dichte zu ändern oder umgekehrt, muss das Speicherformat der Spalte geändert werden. Das SQL Server-Datenbankmodul 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, um den von der alten Spalte belegten Platz freizugeben.

    HinweisHinweis

    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.

Beispiele:

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.

HinweisHinweis

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 AdventureWorks
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 ;

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

Änderungsverlauf

Aktualisierter Inhalt

Die Datengrößen, die in der Tabelle der datenabhängigen Typen aufgelistet sind, wurden festgelegt.