Verwenden Sie Spaltensätze

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

Für Tabellen, die Sparsespalten aufweisen, können Sie einen Spaltensatz festlegen, der alle Sparsespalten in der Tabelle zurückgibt. Bei einem Spaltensatz handelt es sich um eine nicht typisierte XML-Darstellung, die alle Sparsespalten einer Tabelle in einer strukturierten Ausgabe kombiniert. Ein Spaltensatz ist insofern mit einer berechneten Spalte vergleichbar, als der Spaltensatz nicht physisch in der Tabelle gespeichert wird. Der Unterschied zwischen einem Spaltensatz und einer berechneten Spalte besteht darin, dass der Spaltensatz direkt aktualisiert werden kann.

Verwenden Sie Spaltensätze, wenn die Tabelle eine große Anzahl an Spalten enthält und es sehr aufwändig ist, jede Spalte einzeln zu verarbeiten. Außerdem verbessert sich die Anwendungsleistung, wenn zum Auswählen und Einfügen von Daten bei Tabellen mit sehr vielen Spalten Spaltensätze verwendet werden. Die Leistung von Spaltensätzen kann jedoch beeinträchtigt werden, wenn für die Spalten in der Tabelle sehr viele Indizes definiert werden. Das liegt daran, dass in diesem Fall für einen Ausführungsplan mehr Arbeitsspeicher erforderlich ist.

Verwenden Sie zum Definieren eines Spaltensatzes die Schlüsselwörter *<column_set_name>* FOR ALL_SPARSE_COLUMNS in den Anweisungen CREATE TABLE oder ALTER TABLE.

Richtlinien zum Verwenden von Spaltensätzen

Wenn Sie Spaltensätze verwenden, beachten Sie die folgenden Richtlinien:

• Spalten mit geringer Dichte und ein Spaltensatz können als Teil der gleichen Anweisung hinzugefügt werden.

• Sie können einer Tabelle, die bereits Sparsespalten enthält, keinen Spaltensatz hinzufügen.

• Die Spaltensatzspalte kann nicht geändert oder umbenannt werden. Um einen Spaltensatz zu ändern, müssen Sie die Sparse-Spalten und den Spaltensatz löschen und neu erstellen. Spalten mit dem SPARSE-Schlüsselwort können aus der Tabelle hinzugefügt und gelöscht werden.

• Einer Tabelle, die keine Sparse Spalten enthält, kann ein Spaltensatz hinzugefügt werden. Wenn Sie der Tabelle später Sparse-Spalten hinzufügen, werden diese im Spaltensatz angezeigt.

• Es ist nur ein Spaltensatz pro Tabelle zulässig.

• Ein Spaltensatz ist optional und für die Verwendung von Sparse-Spalten nicht erforderlich.

• Für einen Spaltensatz können keine Einschränkungen oder Standardwerte definiert werden.

• Berechnete Spalten können keine Spaltensatz-Spalten enthalten.

• Verteilte Abfragen werden von Tabellen mit Spaltensätzen nicht unterstützt.

• Die Replikation unterstützt Spaltensätze nicht.

• Change Data Capture unterstützt Spaltensätze nicht.

• Ein Spaltensatz darf nicht Teil irgendeiner Art von Index sein. Dies gilt auch für XML-Indizes, Volltextindizes und indizierte Sichten. Ein Spaltensatz kann einem Index nicht als eingeschlossene Spalte hinzugefügt werden.

• Ein Spaltensatz darf nicht im Filterausdruck eines gefilterten Indexes oder einer gefilterten Statistik verwendet werden.

• Wenn eine Sicht einen Spaltensatz enthält, wird der Spaltensatz in der Sicht als XML-Spalte angezeigt.

• Ein Spaltensatz darf nicht in einer indizierten Sichtdefinition enthalten sein.

• Partitionierte Sichten, die Tabellen mit Spaltensätzen enthalten, können aktualisiert werden, wenn die Sparsespalten in der partitionierten Sicht mit Namen angegeben sind. Eine partitionierte Sicht kann nicht aktualisiert werden, wenn sie lediglich einen Verweis auf den Spaltensatz enthält.

• Abfragebenachrichtigungen, die auf Spaltensätze verweisen, sind nicht zulässig.

• Für XML-Daten gilt eine Größenbeschränkung von 2 GB. Wenn die kombinierte Datenmenge aller Sparse-Spalten, die in einer Zeile nicht NULL sind, diese Grenze überschreitet, führt die Abfrage oder der DML-Vorgang zu einem Fehler.

• Informationen zu den Daten, die von der Funktion COLUMNS_UPDATED zurückgegeben werden, finden Sie unter Verwenden von Spalten mit geringer Dichte.

Richtlinien zum Auswählen von Daten aus einem Spaltensatz

Beim Auswählen von Daten aus einem Spaltensatz sind die folgenden Richtlinien zu beachten:

• Grundsätzlich handelt es sich bei einem Spaltensatz um eine aktualisierbare, berechnete XML-Spalte, die einen Satz zugrunde liegender relationaler Spalten in einer XML-Darstellung zusammenfasst. Der Spaltensatz unterstützt nur die ALL_SPARSE_COLUMNS-Eigenschaft. Diese Eigenschaft wird verwendet, um für eine bestimmte Zeile alle Nicht-NULL-Werte aus allen Sparse-Spalten zu aggregieren.

• Im Tabellen-Editor von SQL Server Management Studio werden Spaltensätze als bearbeitbare XML-Felder angezeigt. Definieren Sie Spaltensätze im folgenden Format:

  <column_name_1>value1</column_name_1><column_name_2>value2</column_name_2>...  
  

  Beispiele für Spaltensatzwerte:

  <sparseProp1>10</sparseProp1><sparseProp3>20</sparseProp3>  
  

  <DocTitle>Bicycle Parts List</DocTitle><Region>West</Region>
  

• Spalten mit geringer Dichte, die NULL-Werte enthalten, werden in der XML-Darstellung des Spaltensatzes nicht berücksichtigt.

Einfügen oder Ändern von Daten in einem Spaltensatz

Zur Änderung von Daten in einem Spaltensatz verwenden Sie die Namen der einzelnen Spalten. Sie können auch auf den Namen des Spaltensatzes verweisen und die Werte des Spaltensatzes angeben, indem Sie das XML-Format des Spaltensatzes verwenden. Spalten mit geringer Dichte können in der XML-Spalte in beliebiger Reihenfolge angezeigt werden.

Wenn Werte in Sparsespalten unter Verwendung des XML-Spaltensatzes eingefügt oder aktualisiert werden, werden die in die zugrunde liegenden Sparsespalten eingefügten Werte implizit aus dem Datentyp xml konvertiert. Bei den meisten numerischen Datentypen, einschließlich bigint, int, smallint, tinyint, bit, float und real, wird ein leerer Wert im XML-Code für die Spalte in eine leere Zeichenfolge konvertiert. Hierdurch wird der Wert 0 in die Spalte eingefügt, wie im folgenden Beispiel gezeigt. Die Ersetzung durch 0 gilt jedoch nicht für die numerischen und dezimalen Datentypen. Diese Werte müssen angegeben werden, sonst ergibt sich ein Konvertierungsfehler.

CREATE TABLE t (i int SPARSE, cs xml column_set FOR ALL_SPARSE_COLUMNS);  
GO  
INSERT t(cs) VALUES ('<i/>');  
GO  
SELECT i FROM t;  
GO  

In diesem Beispiel wurde kein Wert für die Spalte iangegeben, und der Wert 0 wurde eingefügt.

Verwenden des sql_variant-Datentyps

Der sql_variant -Datentyp kann mehrere unterschiedliche Datentypen speichern, z.B. int, charund date. Spaltensätze geben die Datentypinformationen wie Dezimalstellen, Genauigkeit und Gebietsschemainformationen, die einem sql_variant-Wert zugeordnet sind, als Attribute in der generierten XML-Spalte zurück. Wenn Sie versuchen, diese Attribute in einer benutzerdefinierten XML-Anweisung als Eingabe für einen INSERT- oder UPDATE-Vorgang für einen Spaltensatz bereitzustellen, sind einige dieser Attribute obligatorisch, und anderen wird ein Standardwert zugewiesen. In der folgenden Tabelle sind die Datentypen und die Standardwerte aufgeführt, die vom Server generiert werden, wenn der Wert nicht angegeben wird.

* localeID -1 bedeutet das Standard-Gebietsschema. Das englischsprachige Gebietsschema ist 1033.

** Nicht zutreffend = Bei einem SELECT-Vorgang für den Spaltensatz werden für diese Attribute keine Werte ausgegeben. Gibt eine Fehlermeldung zurück, wenn für dieses Attribut von dem Aufrufer in der für einen Spaltensatz in einem INSERT- oder UPDATE-Vorgang bereitgestellten XML-Darstellung ein Wert angegeben wird.

Security

Das Sicherheitsmodell für Spaltensätze funktioniert ähnlich wie das Sicherheitsmodell zwischen Tabellen und Spalten. Spaltensätze können als Minitabellen visualisiert werden, und die SELECT-Vorgänge entsprechen den SELECT *-Vorgängen für diese Minitabellen. Bei der Beziehung zwischen Spaltensatz und Sparsespalten handelt es sich jedoch nicht um einen Container, sondern um eine Gruppenbeziehung. Das Sicherheitsmodell überprüft die Sicherheit in der Spaltensatzspalte und berücksichtigt die DENY Vorgänge in den zugrunde liegenden geringen Spalten. Für das Sicherheitsmodell gelten die folgenden zusätzlichen Eigenschaften:

• Sicherheitsberechtigungen können in der Spaltensatzspalte gewährt und widerrufen werden, ähnlich wie bei jeder anderen Spalte in der Tabelle.

• Ein GRANT oder REVOKE der SELECT-, INSERT-, UPDATE-, DELETE- und REFERENCES-Berechtigung für eine Spaltensatzspalte wird nicht an die zugrunde liegenden Mitgliedsspalten dieses Satzes weitergegeben. Dies gilt nur für die Verwendung der Spaltensatzspalte. DENY Die Berechtigung für einen Spaltensatz wird an die zugrunde liegenden geringen Spalten der Tabelle weitergegeben.

• Das Ausführen von SELECT-, INSERT-, UPDATE- und DELETE-Anweisungen für die Spaltensatzspalte setzt voraus, dass der Benutzer über die entsprechenden Berechtigungen für die Spaltensatzspalte sowie über die entsprechende Berechtigung für alle Sparse-Spalten in der Tabelle verfügt. Da der Spaltensatz alle dünn besetzten Spalten in der Tabelle darstellt, müssen Sie für alle dünn besetzten Spalten über Berechtigungen verfügen; dies schließt auch dünn besetzte Spalten ein, die Sie möglicherweise nicht ändern.

• Beim Ausführen einer REVOKE-Anweisung für eine Sparse-Spalte oder einen Spaltensatz werden die Sicherheitseinstellungen standardmäßig vom jeweiligen übergeordneten Objekt übernommen.

Examples

In den folgenden Beispielen enthält eine Dokumenttabelle die gemeinsamen Spalten DocID und Title. 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.

A. Erstellen Sie eine Tabelle, die einen Spaltensatz enthält.

Im folgenden Beispiel wird die Tabelle erstellt, die Sparse Columns verwendet und den Spaltensatz SpecialPurposeColumns einschließt. Im Beispiel werden zwei Zeilen in die Tabelle eingefügt, und anschließend werden Daten aus der Tabelle ausgewählt.

USE AdventureWorks2022;  
GO  
  
CREATE TABLE DocumentStoreWithColumnSet  
    (DocID int PRIMARY KEY,  
     Title varchar(200) NOT NULL,  
     ProductionSpecification varchar(20) SPARSE NULL,  
     ProductionLocation smallint SPARSE NULL,  
     MarketingSurveyGroup varchar(20) SPARSE NULL,  
     MarketingProgramID int SPARSE NULL,  
     SpecialPurposeColumns XML COLUMN_SET FOR ALL_SPARSE_COLUMNS);  
GO  

B. Daten mithilfe der Namen der Sparse-Spalten in eine Tabelle einfügen

In den folgenden Beispielen werden zwei Zeilen in die in Beispiel A erstellte Tabelle eingefügt. Hierzu werden die Namen der Sparsespalten verwendet, und auf den Spaltensatz wird nicht verwiesen.

INSERT DocumentStoreWithColumnSet (DocID, Title, ProductionSpecification, ProductionLocation)  
VALUES (1, 'Tire Spec 1', 'AXZZ217', 27);  
GO  
  
INSERT DocumentStoreWithColumnSet (DocID, Title, MarketingSurveyGroup)  
VALUES (2, 'Survey 2142', 'Men 25 - 35');  
GO  

C. Daten in eine Tabelle unter Verwendung des Namens des Spaltensatzes einfügen

Im folgenden Beispiel wird eine dritte Zeile in die in Beispiel A erstellte Tabelle eingefügt. Diesmal werden die Namen der Sparsespalten nicht verwendet. Stattdessen wird der Name des Spaltensatzes verwendet, und die INSERT-Anweisung gibt die Werte für zwei der vier verteilten Spalten im XML-Format an.

INSERT DocumentStoreWithColumnSet (DocID, Title, SpecialPurposeColumns)  
VALUES (3, 'Tire Spec 2', '<ProductionSpecification>AXW9R411</ProductionSpecification><ProductionLocation>38</ProductionLocation>');  
GO  

D. Prüfen der Ergebnisse eines Spaltensatzes bei Verwendung von SELECT *

Im folgenden Beispiel werden alle Spalten der Tabelle ausgewählt, die einen Spaltensatz enthält. Es wird eine XML-Spalte mit den kombinierten Werten der verteilten Spalten zurückgegeben. Die Sparsespalten werden nicht einzeln zurückgegeben.

SELECT DocID, Title, SpecialPurposeColumns FROM DocumentStoreWithColumnSet ;  

Hier sehen Sie das Ergebnis.

DocID  Title        SpecialPurposeColumns  
1      Tire Spec 1  <ProductionSpecification>AXZZ217</ProductionSpecification><ProductionLocation>27</ProductionLocation>  
2      Survey 2142  <MarketingSurveyGroup>Men 25 - 35</MarketingSurveyGroup>  
3      Tire Spec 2  <ProductionSpecification>AXW9R411</ProductionSpecification><ProductionLocation>38</ProductionLocation> 

E. Beobachten Sie die Ergebnisse der Auswahl des Spaltensatzes anhand des Namens

Da die Produktionsabteilung nicht an den Marketingdaten interessiert ist, wird in diesem Beispiel eine WHERE -Klausel zur Einschränkung der Ausgabe hinzugefügt. In diesem Beispiel wird der Name des Spaltensatzes verwendet.

SELECT DocID, Title, SpecialPurposeColumns  
FROM DocumentStoreWithColumnSet  
WHERE ProductionSpecification IS NOT NULL ;  

Hier sehen Sie das Ergebnis.

DocID  Title        SpecialPurposeColumns  
1      Tire Spec 1  <ProductionSpecification>AXZZ217</ProductionSpecification><ProductionLocation>27</ProductionLocation>  
3      Tire Spec 2  <ProductionSpecification>AXW9R411</ProductionSpecification><ProductionLocation>38</ProductionLocation>  

F. Beobachten Sie die Ergebnisse der Auswahl dünn besetzter Spalten anhand des Namens

Auch wenn eine Tabelle einen Spaltensatz enthält, können Sie eine Abfrage anhand der einzelnen Spaltennamen durchführen, wie im folgenden Beispiel gezeigt.

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

Hier sehen Sie das Ergebnis.

DocID  Title        ProductionSpecification ProductionLocation`  
1      Tire Spec 1  AXZZ217                 27`  
3      Tire Spec 2  AXW9R411                38`  

G. Aktualisieren einer Tabelle mithilfe eines Spaltensatzes

Im folgenden Beispiel wird der dritte Datensatz mit neuen Werten für beide Sparse-Spalten aktualisiert, die in dieser Zeile verwendet werden.

UPDATE DocumentStoreWithColumnSet  
SET SpecialPurposeColumns = '<ProductionSpecification>ZZ285W</ProductionSpecification><ProductionLocation>38</ProductionLocation>'  
WHERE DocID = 3 ;  
GO  

Das folgende Beispiel aktualisiert den dritten Datensatz, gibt jedoch nur den Wert für eine der beiden belegten Spalten an. Die zweite Spalte ProductionLocation ist nicht in der UPDATE-Anweisung enthalten und wird auf NULL aktualisiert.

UPDATE DocumentStoreWithColumnSet  
SET SpecialPurposeColumns = '<ProductionSpecification>ZZ285W</ProductionSpecification>'  
WHERE DocID = 3 ;  
GO  

Nächste Schritte

• Verwenden von Spalten mit geringer Dichte