Delen via

Kolomsets gebruiken

  • Artikel

Van toepassing op: SQL Server 2016 (13.x) en latere versies Azure SQL DatabaseAzure SQL Managed InstanceSQL-database in Microsoft Fabric

Tabellen die gebruikmaken van sparsekolommen kunnen een kolomset aanwijzen om alle sparsekolommen in de tabel te retourneren. Een kolomset is een niet-getypte XML-weergave die alle parseerde kolommen van een tabel combineert in een gestructureerde uitvoer. Een kolomset lijkt op een berekende kolom omdat de kolomset niet fysiek is opgeslagen in de tabel. Een kolomset verschilt van een berekende kolom omdat de kolomset direct kan worden bijgewerkt.

U moet overwegen om kolomsets te gebruiken wanneer het aantal kolommen in een tabel groot is en het afzonderlijk gebruiken van kolommen lastig is. Toepassingen kunnen prestatieverbeteringen zien wanneer ze gegevens selecteren en invoegen met behulp van kolomsets in tabellen met veel kolommen. De prestaties van kolomsets kunnen echter worden verminderd wanneer veel indexen zijn gedefinieerd voor de kolommen in de tabel. Dit komt doordat de hoeveelheid geheugen die nodig is voor een uitvoeringsplan toeneemt.

Als u een kolomset wilt definiëren, gebruikt u de *<column_set_name>* FOR ALL_SPARSE_COLUMNS trefwoorden in de instructies CREATE TABLE of ALTER TABLE.

Richtlijnen voor het gebruik van kolomsets

Houd rekening met de volgende richtlijnen wanneer u kolomsets gebruikt:

  • Sparse kolommen en een kolomset kunnen worden toegevoegd als onderdeel van dezelfde verklaring.

  • Een kolomset kan niet worden toegevoegd aan een tabel als die tabel al sparse kolommen bevat.

  • De kolom met de kolomset kan niet worden gewijzigd of de naam ervan kan niet worden gewijzigd. Als u een kolomset wilt wijzigen, moet u de parseringskolommen en de kolomset verwijderen en opnieuw maken. Kolommen met het trefwoord SPARSE kunnen worden toegevoegd en verwijderd uit de tabel.

  • Een kolomset kan worden toegevoegd aan een tabel die geen sparse kolommen bevat. Als er later sparse-kolommen aan de tabel worden toegevoegd, komen deze in de kolomset terecht.

  • Er is slechts één kolomset per tabel toegestaan.

  • Een kolomset is optioneel en is niet vereist voor het gebruik van spaarse kolommen.

  • Beperkingen of standaardwaarden kunnen niet worden gedefinieerd voor een kolomset.

  • Berekende kolommen kunnen geen kolommensetkolommen bevatten.

  • Gedistribueerde query's worden niet ondersteund voor tabellen die kolomsets bevatten.

  • Replicatie biedt geen ondersteuning voor kolomsets.

  • Wijzigingsgegevens vastleggen biedt geen ondersteuning voor kolomsets.

  • Een kolomset kan geen deel uitmaken van een soort index. Dit omvat XML-indexen, indexen in volledige tekst en geïndexeerde weergaven. Een kolomset kan niet worden toegevoegd als een opgenomen kolom in een index.

  • Een kolomset kan niet worden gebruikt in de filterexpressie van een gefilterde index of gefilterde statistieken.

  • Wanneer een weergave een kolomset bevat, wordt de kolomset weergegeven in de weergave als een XML-kolom.

  • Een kolomset kan niet worden opgenomen in een geïndexeerde weergavedefinitie.

  • Gepartitioneerde weergaven die tabellen met kolomsets bevatten, kunnen worden bijgewerkt wanneer in de gepartitioneerde weergave de sparsiekolommen op naam worden opgegeven. Een gepartitioneerde weergave kan niet worden bijgewerkt wanneer deze verwijst naar de kolomset.

  • Querymeldingen die verwijzen naar kolomsets zijn niet toegestaan.

  • XML-gegevens hebben een grootte van 2 GB. Als de gecombineerde gegevens van alle niet-NULL-parseringskolommen in een rij deze limiet overschrijden, veroorzaakt de query- of DML-bewerking een fout.

  • Voor informatie over de gegevens die worden geretourneerd door de functie COLUMNS_UPDATED, zie Sparse Columnsgebruiken.

Richtlijnen voor het selecteren van gegevens in een kolomset

Houd rekening met de volgende richtlijnen voor het selecteren van gegevens uit een kolomset:

  • Conceptueel gezien is een kolomset een type bij te werken, berekende XML-kolom die een set onderliggende relationele kolommen samenvoegt tot één XML-representatie. De kolomset ondersteunt alleen de eigenschap ALL_SPARSE_COLUMNS. Deze eigenschap wordt gebruikt om alle niet-NULL-waarden uit alle parseringskolommen voor een bepaalde rij samen te voegen.

  • In de tabeleditor van SQL Server Management Studio worden kolomsets weergegeven als een bewerkbaar XML-veld. Kolomsets definiëren in het formaat:

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

    Voorbeelden van waarden voor kolomsets zijn als volgt:

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

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

  • Sparse-kolommen die null-waarden bevatten, worden weggelaten uit de XML-weergave voor de kolomset.

Waarschuwing

Door een kolomset toe te voegen, wordt het gedrag van SELECT * query's gewijzigd. De query retourneert de kolomset als een XML-kolom en retourneert niet de afzonderlijke spaarzame kolommen. Schemaontwerpers en softwareontwikkelaars moeten ervoor zorgen dat bestaande toepassingen niet worden verbroken. Afzonderlijke sparse kolommen kunnen nog steeds bij naam worden opgevraagd in een SELECT-instructie.

Gegevens invoegen of wijzigen in een kolomset

Gegevensmanipulatie van een sparse-kolom kan worden uitgevoerd met behulp van de naam van de afzonderlijke kolommen of door te verwijzen naar de naam van de kolomset en de waarden van de kolomset op te geven met behulp van de XML-indeling van de kolomset. Sparse-kolommen kunnen in elke volgorde in de XML-kolom worden weergegeven.

Wanneer sparse-kolomwaarden worden ingevoegd of bijgewerkt met behulp van de XML-kolomset, worden de waarden die in de onderliggende sparse-kolommen worden ingevoegd, impliciet geconverteerd van het xml- gegevenstype. In het geval van de meeste numerieke gegevenstypen, waaronder bigint, int, smallint, kleine, bit, zwevendeen echte, wordt een lege waarde in de XML voor de kolom omgezet in een lege tekenreeks. Dit zorgt ervoor dat een nul wordt ingevoegd in de kolom, zoals wordt weergegeven in het volgende voorbeeld. De vervanging op 0 is echter niet van toepassing op de numerieke en decimaal gegevenstypen, deze waarden moeten worden opgegeven of veroorzaken een conversiefout.

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 dit voorbeeld is er geen waarde opgegeven voor de kolom i, maar de waarde 0 is ingevoegd.

Het gegevenstype sql_variant gebruiken

Het datumtype sql_variant kan meerdere verschillende gegevenstypen opslaan, zoals int, tekenen datum. Kolomsets voeren de gegevens van het gegevenstype uit, zoals informatie over schaal, precisie en landinstellingen die zijn gekoppeld aan een sql_variant-waarde als kenmerken in de gegenereerde XML-kolom. Als u deze kenmerken in een aangepast gegenereerde XML-instructie probeert op te geven als invoer voor een invoeg- of bijwerkbewerking in een kolomset, zijn sommige van deze kenmerken vereist en worden sommige hiervan een standaardwaarde toegewezen. De volgende tabel bevat de gegevenstypen en de standaardwaarden die de server genereert wanneer de waarde niet wordt opgegeven.

Gegevenstype localeID* sqlVergelijkOpties sqlCollationVersion SqlSortId Maximumlengte Precisie Schaal
teken, varchar, binaire -1 'Standaard' 0 0 8000 Niet van toepassing** Niet van toepassing
nvarchar- -1 'Standaard' 0 0 4000 Niet van toepassing Niet van toepassing
decimale, zwevende Niet van toepassing Niet van toepassing Niet van toepassing Niet van toepassing Niet van toepassing 18 0
geheel getal, bigint, kleineint, kleineint Niet van toepassing Niet van toepassing Niet van toepassing Niet van toepassing Niet van toepassing Niet van toepassing Niet van toepassing
datetime2 Niet van toepassing Niet van toepassing Niet van toepassing Niet van toepassing Niet van toepassing Niet van toepassing 7
datum/tijd-verschuiving Niet van toepassing Niet van toepassing Niet van toepassing Niet van toepassing Niet van toepassing Niet van toepassing 7
datum/tijd, datum, smalldatetime Niet van toepassing Niet van toepassing Niet van toepassing Niet van toepassing Niet van toepassing Niet van toepassing Niet van toepassing
geld, klein bedrag Niet van toepassing Niet van toepassing Niet van toepassing Niet van toepassing Niet van toepassing Niet van toepassing Niet van toepassing
tijd Niet van toepassing Niet van toepassing Niet van toepassing Niet van toepassing Niet van toepassing Niet van toepassing 7

* localeID -1 betekent de standaardlandinstelling. De Engelse taalinstelling is 1033.

** Niet van toepassing = Er worden geen waarden voor deze kenmerken uitgevoerd tijdens een selectiebewerking in de kolomset. Hiermee wordt een fout gegenereerd wanneer een waarde voor dit kenmerk wordt opgegeven door de aanroeper in de XML-weergave die is opgegeven voor een kolomset in een invoeg- of bijwerkbewerking.

Veiligheid

Het beveiligingsmodel voor een kolomset werkt vergelijkbaar met het beveiligingsmodel dat bestaat tussen tabel en kolommen. Kolomsets kunnen worden weergegeven als een minitabel en een selectiebewerking is vergelijkbaar met een SELECT *-bewerking op deze minitabel. Maar de relatie tussen een kolom die is ingesteld op spaarzame kolommen, is een groeperingsrelatie in plaats van strikt een container. Het beveiligingsmodel controleert de beveiliging op de kolomset en respecteert de DENY-bewerkingen op de onderliggende sparse kolommen. Aanvullende kenmerken van het beveiligingsmodel zijn als volgt:

  • Beveiligingsmachtigingen kunnen worden verleend en ingetrokken vanuit de kolomsetkolom, vergelijkbaar met elke andere kolom in de tabel.

  • Een toelating of herroeping van SELECT-, INSERT-, UPDATE-, DELETE- en REFERENTIErechten voor een kolomsetkolom wordt niet doorgegeven aan de onderliggende lidkolommen van die set. Dit geldt alleen voor het gebruik van de kolomsetkolom. De machtiging WEIGEREN voor een kolomset wordt doorgegeven aan de onderliggende spaarzame kolommen van de tabel.

  • Voor het uitvoeren van SELECT-, INSERT-, UPDATE- en DELETE-instructies voor de kolomsetkolom moet de gebruiker over overeenkomstige machtigingen beschikken voor de kolomsetkolom en ook de bijbehorende machtiging voor alle parserende kolommen in de tabel. Omdat de kolomset alle sparsekolommen in de tabel vertegenwoordigt, moet u machtigingen hebben voor alle parserende kolommen en dit omvat sparsekolommen die u mogelijk niet wijzigt.

  • Als u een REVOKE-instructie uitvoert op een sparse-kolom of -kolomset, wordt de beveiliging standaard ingesteld op het bovenliggende object.

Voorbeelden

In de volgende voorbeelden bevat een documenttabel de algemene set kolommen DocID en Title. De productiegroep wil een kolom ProductionSpecification en ProductionLocation voor alle productiedocumenten. De groep Marketing wil een MarketingSurveyGroup kolom voor marketingdocumenten.

Een. Een tabel maken met een kolomset

In het volgende voorbeeld wordt de tabel gemaakt die sparsekolommen gebruikt en de kolomset SpecialPurposeColumnsbevat. In het voorbeeld worden twee rijen in de tabel ingevoegd en vervolgens gegevens uit de tabel geselecteerd.

Notitie

Deze tabel heeft slechts vijf kolommen, zodat u deze gemakkelijker kunt weergeven en lezen.

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. Gegevens invoegen in een tabel met behulp van de namen van de parseringskolommen

In de volgende voorbeelden worden twee rijen ingevoegd in de tabel die is gemaakt in voorbeeld A. In de voorbeelden worden de namen van de parseringskolommen gebruikt en wordt niet verwezen naar de kolomset.

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. Gegevens invoegen in een tabel met behulp van de naam van de kolomset

In het volgende voorbeeld wordt een derde rij ingevoegd in de tabel die is gemaakt in voorbeeld A. Deze keer worden de namen van de parseringskolommen niet gebruikt. In plaats daarvan wordt de naam van de kolomset gebruikt en de invoeging bevat de waarden voor twee van de vier spaarzame kolommen in XML-indeling.

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

D. Bekijk de resultaten van een kolomset wanneer SELECT * wordt gebruikt

In het volgende voorbeeld worden alle kolommen uit de tabel geselecteerd die een kolomset bevatten. Hiermee wordt een XML-kolom geretourneerd met de gecombineerde waarden van de sparse kolommen. Het retourneert de spaarzame kolommen niet afzonderlijk.

SELECT DocID, Title, SpecialPurposeColumns FROM DocumentStoreWithColumnSet ;

Hier is de set met resultaten.

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. Bekijk de resultaten van het selecteren van de kolom die is ingesteld op naam

Omdat de productieafdeling niet geïnteresseerd is in de marketinggegevens, wordt in dit voorbeeld een WHERE component toegevoegd om de uitvoer te beperken. In het voorbeeld wordt de naam van de kolomset gebruikt.

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

Hier zijn de resultaten.

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

F. Bekijk de resultaten van het selecteren van sparse kolommen op naam

Wanneer een tabel een kolomset bevat, kunt u nog steeds een query uitvoeren op de tabel met behulp van de afzonderlijke kolomnamen, zoals wordt weergegeven in het volgende voorbeeld.

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

Dit is de resultaatset.

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

G. Een tabel bijwerken met behulp van een kolomset

In het volgende voorbeeld wordt het derde record bijgewerkt met nieuwe waarden voor beide sparse kolommen die door die rij worden gebruikt.

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

Belangrijk

Een UPDATE-instructie die gebruikmaakt van een kolomset werkt alle sparse kolommen in de tabel bij. Sparse kolommen waarnaar niet wordt verwezen, worden bijgewerkt naar NULL.

In het volgende voorbeeld wordt de derde record bijgewerkt, maar wordt alleen de waarde opgegeven van een van de twee gevulde kolommen. De tweede kolom ProductionLocation is niet opgenomen in de UPDATE-instructie en wordt bijgewerkt naar NULL.

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

Volgende stappen