Använda kolumnuppsättningar

Artikel
02/04/2025

gäller för: SQL Server 2016 (13.x) och senare versioner Azure SQL Database Azure SQL Managed Instance SQL-databas i Microsoft Fabric

Tabeller som använder glesa kolumner kan ange en kolumnuppsättning för att returnera alla glesa kolumner i tabellen. En kolumnuppsättning är en otypad XML-representation som kombinerar alla glesa kolumner i en tabell till strukturerade utdata. En kolumnuppsättning är som en beräknad kolumn eftersom kolumnuppsättningen inte lagras fysiskt i tabellen. En kolumnuppsättning skiljer sig från en beräknad kolumn eftersom kolumnuppsättningen är direkt uppdaterbar.

Du bör överväga att använda kolumnuppsättningar när antalet kolumner i en tabell är stort och det är besvärligt att arbeta på dem individuellt. Program kan se vissa prestandaförbättringar när de väljer och infogar data med hjälp av kolumnuppsättningar i tabeller som har många kolumner. Prestanda för kolumnuppsättningar kan dock minskas när många index definieras för kolumnerna i tabellen. Det beror på att mängden minne som behövs för en körningsplan ökar.

Om du vill definiera en kolumnuppsättning använder du nyckelorden *<column_set_name>* FOR ALL_SPARSE_COLUMNS i CREATE TABLE eller ALTER TABLE-instruktioner.

Riktlinjer för att använda kolumnuppsättningar

Tänk på följande riktlinjer när du använder kolumnuppsättningar:

Glesa kolumner och en kolumnuppsättning kan läggas till som en del av samma instruktion.
Det går inte att lägga till en kolumnuppsättning i en tabell om den tabellen redan innehåller glesa kolumner.
Kolumnuppsättningskolumnen kan inte ändras eller byta namn. Om du vill ändra en kolumnuppsättning måste du ta bort och återskapa de glesa kolumnerna och kolumnuppsättningen. Kolumner med NYCKELORDET SPARSE kan läggas till och tas bort från tabellen.
En kolumnuppsättning kan läggas till i en tabell som inte innehåller några glesa kolumner. Om glesa kolumner senare läggs till i tabellen visas de i kolumnuppsättningen.
Endast en kolumnuppsättning tillåts per tabell.
En kolumnuppsättning är valfri och krävs inte för att använda glesa kolumner.
Begränsningar eller standardvärden kan inte definieras för en kolumnuppsättning.
Beräknade kolumner får inte innehålla kolumnuppsättningskolumner.
Distribuerade frågor stöds inte i tabeller som innehåller kolumnuppsättningar.
Replikering stöder inte kolumnuppsättningar.
Ändringsdatainsamling stöder inte kolumnuppsättningar.
En kolumnuppsättning kan inte ingå i någon typ av index. Detta omfattar XML-index, fulltextindex och indexerade vyer. Det går inte att lägga till en kolumnuppsättning som en inkluderad kolumn i något index.
Det går inte att använda en kolumnuppsättning i filteruttrycket för ett filtrerat index eller filtrerad statistik.
När en vy innehåller en kolumnuppsättning visas kolumnuppsättningen i vyn som en XML-kolumn.
Det går inte att inkludera en kolumnuppsättning i en indexerad vydefinition.
Partitionerade vyer som innehåller tabeller som innehåller kolumnuppsättningar är uppdaterbara när den partitionerade vyn anger de glesa kolumnerna efter namn. En partitionerad vy är inte uppdaterad när den refererar till kolumnuppsättningen.
Frågemeddelanden som refererar till kolumnuppsättningar är inte tillåtna.
XML-data har en storleksgräns på 2 GB. Om de kombinerade data för alla icke-NULL-glesa kolumner i en rad överskrider den här gränsen, genererar frågan eller DML-åtgärden ett fel.
Information om de data som returneras av funktionen COLUMNS_UPDATED finns i Använda glesa kolumner.

Riktlinjer för att välja data från en kolumnuppsättning

Överväg följande riktlinjer för att välja data från en kolumnuppsättning:

Konceptuellt är en kolumnuppsättning en typ av uppdaterbar, beräknad XML-kolumn som aggregerar en uppsättning underliggande relationskolumner i en enda XML-representation. Kolumnuppsättningen stöder endast egenskapen ALL_SPARSE_COLUMNS. Den här egenskapen används för att aggregera alla icke-NULL-värden från alla glesa kolumner för en viss rad.

I SQL Server Management Studio-tabellredigeraren visas kolumnuppsättningar som ett redigerbart XML-fält. Definiera kolumnuppsättningar i formatet:

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

Exempel på kolumnuppsättningsvärden är följande:

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

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

Glesa kolumner som innehåller null-värden utelämnas från XML-representationen för kolumnuppsättningen.

Varning

Om du lägger till en kolumnuppsättning ändras beteendet för SELECT * frågor. Frågan returnerar kolumnuppsättningen som en XML-kolumn och returnerar inte de enskilda glesa kolumnerna. Schemakonstruktörer och programvaruutvecklare måste vara försiktiga så att de inte förstör befintliga program. Enskilda glesa kolumner kan fortfarande frågas efter namn i en SELECT-instruktion.

Infoga eller ändra data i en kolumnuppsättning

Datamanipulering av en gles kolumn kan utföras med hjälp av namnet på de enskilda kolumnerna, eller genom att referera till namnet på kolumnuppsättningen och ange värdena för kolumnuppsättningen med hjälp av XML-formatet för kolumnuppsättningen. Glesa kolumner kan visas i valfri ordning i XML-kolumnen.

När glesa kolumnvärden infogas eller uppdateras med hjälp av XML-kolumnuppsättningen konverteras de värden som infogas i de underliggande glesa kolumnerna implicit från xml- datatyp. För de flesta numeriska datatyper, inklusive bigint, int, smallint, tinyint, bit, floatoch verklig, konverteras ett tomt värde i XML för kolumnen till en tom sträng. Detta gör att en nolla infogas i kolumnen, som du ser i följande exempel. Ersättningen till 0 gäller dock inte för numeriska och decimaler datatyper, dessa värden måste anges eller orsakar ett konverteringsfel.

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

I det här exemplet angavs inget värde för kolumnen i, men värdet 0 infogades.

Använd datatypen sql_variant

Datumtypen sql_variant kan lagra flera olika datatyper, till exempel int, charoch datum. Kolumnuppsättningar matar ut information om datatypen, till exempel skalning, precision och språkinformation som är associerad med ett sql_variant värde som attribut i den genererade XML-kolumnen. Om du försöker ange dessa attribut i en egengenererad XML-instruktion som indata för en infognings- eller uppdateringsåtgärd i en kolumnuppsättning krävs vissa av dessa attribut och vissa av dem tilldelas ett standardvärde. I följande tabell visas de datatyper och standardvärden som servern genererar när värdet inte anges.

Datatyp	localeID*	sqlCompareOptions	SQL-kollationsversion	SqlSortId	Maxlängden	Precision	Skala
tecken, varchar, binär	-1	"Standard"	0	0	8000	Ej tillämpligt**	Ej tillämpligt
nvarchar	-1	"Standard"	0	0	4000	Ej tillämpligt	Ej tillämpligt
decimal, float, verklig	Ej tillämpligt	Ej tillämpligt	Ej tillämpligt	Ej tillämpligt	Ej tillämpligt	18	0
heltal, bigint, tinyint, smallint	Ej tillämpligt	Ej tillämpligt	Ej tillämpligt	Ej tillämpligt	Ej tillämpligt	Ej tillämpligt	Ej tillämpligt
datetime2	Ej tillämpligt	Ej tillämpligt	Ej tillämpligt	Ej tillämpligt	Ej tillämpligt	Ej tillämpligt	7
datum- och tidförskjutning	Ej tillämpligt	Ej tillämpligt	Ej tillämpligt	Ej tillämpligt	Ej tillämpligt	Ej tillämpligt	7
datetime, date, smalldatetime	Ej tillämpligt	Ej tillämpligt	Ej tillämpligt	Ej tillämpligt	Ej tillämpligt	Ej tillämpligt	Ej tillämpligt
pengarsmåpengar	Ej tillämpligt	Ej tillämpligt	Ej tillämpligt	Ej tillämpligt	Ej tillämpligt	Ej tillämpligt	Ej tillämpligt
tid	Ej tillämpligt	Ej tillämpligt	Ej tillämpligt	Ej tillämpligt	Ej tillämpligt	Ej tillämpligt	7

* localeID -1 betyder standardmiljö. Den engelska språkvarianten är 1033.

** Ej tillämpligt = Inga värden genereras för dessa attribut under en urvalsåtgärd i kolumnuppsättningen. Genererar ett fel när ett värde anges för det här attributet av anroparen i XML-representationen som anges för en kolumnuppsättning i en infognings- eller uppdateringsåtgärd.

Säkerhet

Säkerhetsmodellen för en kolumnuppsättning fungerar ungefär som den säkerhetsmodell som finns mellan tabeller och kolumner. Kolumnuppsättningar kan visualiseras som en minitabell och en välj-åtgärd liknar en SELECT *-åtgärd i den här minitabellen. Men relationen mellan kolumnuppsättningen till glesa kolumner är en grupperingsrelation i stället för strikt en container. Säkerhetsmodellen kontrollerar säkerheten i kolumnuppsättningskolumnen och respekterar DENY-åtgärderna på de underliggande glesa kolumnerna. Ytterligare egenskaper för säkerhetsmodellen är följande:

Säkerhetsbehörigheter kan beviljas och återkallas från kolumnuppsättningskolumnen, ungefär som andra kolumner i tabellen.
Behörigheten att bevilja eller återkalla SELECT, INSERT, UPDATE, DELETE och REFERENCES för en kolumn i en kolumnuppsättning sprids inte automatiskt till de underliggande medlemskolumnerna i den uppsättningen. Den gäller endast för användningen av kolumnuppsättningskolumnen. NEKA-behörighet för en kolumnuppsättning sprids även till de underliggande glesa kolumnerna i tabellen.
Om du kör SELECT-, INSERT-, UPDATE- och DELETE-instruktioner i kolumnen för kolumnuppsättningen måste användaren ha motsvarande behörigheter för kolumnuppsättningskolumnen och även motsvarande behörighet för alla glesa kolumner i tabellen. Eftersom kolumnuppsättningen representerar alla glesa kolumner i tabellen måste du ha behörighet för alla glesa kolumner, och detta inkluderar glesa kolumner som du kanske inte ändrar.
Genom att köra en REVOKE-instruktion på en gles kolumn eller kolumnuppsättning återställs säkerheten till deras överordnade objekt.

Exempel

I följande exempel innehåller en dokumenttabell den gemensamma uppsättningen kolumner DocID och Title. Gruppen Produktion vill ha en kolumn med ProductionSpecification och ProductionLocation för alla produktionsdokument. Marknadsföringsgruppen vill ha en MarketingSurveyGroup kolumn för marknadsföringsdokument.

A. Skapa en tabell som har en kolumnuppsättning

I följande exempel skapas tabellen som använder glesa kolumner och innehåller kolumnuppsättningen SpecialPurposeColumns. Exemplet infogar två rader i tabellen och väljer sedan data från tabellen.

Notera

Den här tabellen har bara fem kolumner som gör det enklare att visa och läsa.

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. Infoga data i en tabell med namnen på de glesa kolumnerna

I följande exempel infogas två rader i tabellen som skapas i exempel A. Exemplen använder namnen på de glesa kolumnerna och refererar inte till kolumnuppsättningen.

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. Infoga data i en tabell med namnet på kolumnuppsättningen

I följande exempel infogas en tredje rad i tabellen som skapas i exempel A. Den här gången används inte namnen på de glesa kolumnerna. I stället används namnet på kolumnuppsättningen, och infogningen innehåller värdena för två av de fyra glesa kolumnerna i XML-format.

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

D. Observera resultatet av en kolumnuppsättning när SELECT * används

I följande exempel markeras alla kolumner från tabellen som innehåller en kolumnuppsättning. Den returnerar en XML-kolumn med de kombinerade värdena för de glesa kolumnerna. Den returnerar inte de glesa kolumnerna individuellt.

SELECT DocID, Title, SpecialPurposeColumns FROM DocumentStoreWithColumnSet ;

Här är resultatet.

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. Observera resultaten av att välja kolumnuppsättningen med namn

Eftersom produktionsavdelningen inte är intresserad av marknadsföringsdata lägger det här exemplet till en WHERE-sats för att begränsa utdata. I exemplet används namnet på kolumnuppsättningen.

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

Här är resultatet.

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

F. Observera resultatet av att välja glesa kolumner efter namn

När en tabell innehåller en kolumnuppsättning kan du fortfarande köra frågor mot tabellen med hjälp av de enskilda kolumnnamnen som visas i följande exempel.

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

Här är resultatet.

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

G. Uppdatera en tabell med hjälp av en kolumnuppsättning

I följande exempel uppdateras den tredje posten med nya värden för de två glesa kolumner som används av denna post.

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

Viktig

En UPDATE-instruktion som använder en kolumnuppsättning uppdaterar alla glesa kolumner i tabellen. Glesa kolumner som inte refereras uppdateras till NULL.

I följande exempel uppdateras den tredje posten, men endast värdet för en av de två ifyllda kolumnerna. Den andra kolumnen ProductionLocation ingår inte i UPDATE-instruktionen och uppdateras till NULL.

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

Nästa steg

Använd glesa kolumner

Dela via

Använda kolumnuppsättningar

Riktlinjer för att använda kolumnuppsättningar

Riktlinjer för att välja data från en kolumnuppsättning

Infoga eller ändra data i en kolumnuppsättning

Använd datatypen sql_variant

Säkerhet

Exempel

A. Skapa en tabell som har en kolumnuppsättning

B. Infoga data i en tabell med namnen på de glesa kolumnerna

C. Infoga data i en tabell med namnet på kolumnuppsättningen

D. Observera resultatet av en kolumnuppsättning när SELECT * används

E. Observera resultaten av att välja kolumnuppsättningen med namn

F. Observera resultatet av att välja glesa kolumner efter namn

G. Uppdatera en tabell med hjälp av en kolumnuppsättning

Nästa steg

Feedback

Ytterligare resurser