Använda glesa kolumner

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

Glesa kolumner är vanliga kolumner som har en optimerad lagring för null-värden. Glesa kolumner minskar utrymmeskraven för null-värden på bekostnad av mer omkostnader för att hämta icke-NULL-värden. Överväg att använda glesa kolumner när det sparade utrymmet är minst 20 till 40 procent. Glesa kolumner och kolumnuppsättningar definieras genom att använda satserna CREATE TABLE eller ALTER TABLE.

Glesa kolumner kan användas med kolumnuppsättningar och filtrerade index:

Kolumnuppsättningar

INSERT-, UPDATE- och DELETE-instruktioner kan referera till de glesa kolumnerna efter namn. Men du kan också visa och arbeta med alla glesa kolumner i en tabell som kombineras till en enda XML-kolumn. Den här kolumnen kallas för en kolumnuppsättning. Mer information om kolumnuppsättningar finns i Använda kolumnuppsättningar.
Filtrerade index

Eftersom glesa kolumner har många null-värderade rader är de särskilt lämpliga för filtrerade index. Ett filtrerat index i en gles kolumn kan endast indexera de rader som har fyllt i värden. Detta skapar ett mindre och effektivare index. Mer information finns i Skapa filtrerade index.

Med glesa kolumner och filtrerade index kan program, till exempel Windows SharePoint Services, effektivt lagra och komma åt ett stort antal användardefinierade egenskaper med hjälp av SQL Server.

Egenskaper för glesa kolumner

Glesa kolumner har följande egenskaper:

SQL Server Database Engine använder nyckelordet SPARSE i en kolumndefinition för att optimera lagringen av värden i den kolumnen. När kolumnvärdet är NULL för en rad i tabellen kräver därför värdena inget lagringsutrymme.
Katalogvyer för en tabell som har glesa kolumner är desamma som för en typisk tabell. Katalogvyn sys.columns innehåller en rad för varje kolumn i tabellen och innehåller en kolumnuppsättning om en definieras.
Glesa kolumner är en egenskap hos lagringsskiktet snarare än den logiska tabellen. Därför kopieras inte en SELECT ... INTO-instruktion över den glesa kolumnegenskapen till en ny tabell.
Funktionen COLUMNS_UPDATED returnerar ett varbinärt värde som anger alla kolumner som uppdaterades under en DML-åtgärd. De bitar som returneras av funktionen COLUMNS_UPDATED är följande:
- När en gles kolumn uttryckligen uppdateras anges motsvarande bit för den glesa kolumnen till 1 och biten för kolumnuppsättningen är inställd på 1.
- När en kolumnuppsättning uttryckligen uppdateras anges biten för kolumnuppsättningen till 1, och bitarna för alla glesa kolumner i tabellen är inställda på 1.
- För infogningsåtgärder är alla bitar inställda på 1.
Mer information om kolumnuppsättningar finns i Använda kolumnuppsättningar.

Följande datatyper kan inte anges som SPARSE:

geografi
geometri
bild
ntext

text
tidsstämpel
användardefinierade datatyper

Uppskattade utrymmesbesparingar efter datatyp

Sparsamt befolkade kolumner kräver mer lagringsutrymme för icke-NULL-värden än det utrymme som krävs för identiska data som inte är markerade som SPARSE. Följande tabeller visar utrymmesanvändningen för varje datatyp. Kolumnen NULL Percentage anger vilken procentandel av data som måste vara NULL för en nettobesparing på 40 procent.

Fixed-Length datatyper

Datatyp	Ickesparse byte	Glesa byte	NULL-procent
bit	0.125	5	98%
tinyint	1	5	86%
smallint	2	6	76%
int	4	8	64%
bigint	8	12	52%
riktiga	4	8	64%
flyttal	8	12	52%
småpengar	4	8	64%
pengar	8	12	52%
smalldatetime	4	8	64%
datum och tid	8	12	52%
unika identifierare	16	20	43%
datum	3	7	69%

Precision –Dependent-Length Datatyper

Datatyp	Ickesparse byte	Glesa byte	NULL-procent
datetime2(0)	6	10	57%
datetime2(7)	8	12	52%
tid(0)	3	7	69%
tid(7)	5	9	60%
datetimetoffset(0)	8	12	52%
datetimetoffset (7)	10	14	49%
decimaltal/numeriskt(1,s)	5	9	60%
decimal/numerisk(38,s)	17	21	42%
vardecimal(p,s)	Använd decimal typ som en konservativ uppskattning.

Data-Dependent-Length Datatyper

Datatyp	Ickesparse byte	Glesa byte	NULL-procent
sql_variant	Varierar med den underliggande datatypen
varchar eller char	2*	4*	60%
nvarchar eller nchar	2*	4*+	60%
varbinär eller binär	2*	4*	60%
xml	2*	4*	60%
hierarchyid	2*	4*	60%

*Längden är lika med medelvärdet av de data som finns i typen plus 2 eller 4 byte.

In-Memory omkostnader som krävs för uppdateringar av glesa kolumner

När du utformar tabeller med glesa kolumner bör du tänka på att ytterligare 2 byte omkostnader krävs för varje icke-null-gles kolumn i tabellen när en rad uppdateras. Som ett resultat av detta ytterligare minnesbehov kan uppdateringar misslyckas oväntat med fel 576 när den totala radstorleken, inklusive denna minneskostnad, överskrider 8019 och inga kolumner kan flyttas ut från raden.

Överväg exemplet med en tabell som har 600 glesa kolumner av typen bigint. Om det finns 571 kolumner som inte är null är den totala storleken på disken 571 * 12 = 6 852 byte. När du har inkluderat ytterligare radomkostnader och den glesa kolumnrubriken ökar detta till cirka 6 895 byte. Sidan har fortfarande cirka 1 124 byte tillgängliga på disken. Detta kan ge intryck av att ytterligare kolumner kan uppdateras framgångsrikt. Under uppdateringen finns det dock ytterligare merbelastning i minnet som är 2*(antal icke-noll glesa kolumner). I det här exemplet, inklusive den extra kostnaden – 2 * 571 = 1 142 byte – ökar radstorleken på disken till cirka 8 037 byte. Den här storleken överskrider den maximala tillåtna storleken på 8019 byte. Eftersom alla kolumner är datatyper med fast längd kan de inte tas bort från raden. Därför misslyckas uppdateringen med 576-felet.

Begränsningar för användning av glesa kolumner

Glesa kolumner kan vara av valfri SQL Server-datatyp och bete sig som andra kolumner med följande begränsningar:

En gles kolumn måste vara null och kan inte ha egenskaperna ROWGUIDCOL eller IDENTITY. En gles kolumn kan inte vara av följande datatyper: text, ntext, bild, tidsstämpel, användardefinierad datatyp, geometrieller geografi; eller har attributet FILESTREAM.
En gles kolumn kan inte ha ett standardvärde.
Det går inte att binda en gles kolumn till en regel.
Även om en beräknad kolumn kan innehålla en gles kolumn kan inte en beräknad kolumn markeras som SPARSE.
En datamask kan definieras på en gles kolumn, men inte på en gles kolumn som ingår i en kolumnuppsättning.
En gles kolumn kan inte ingå i ett grupperat index eller ett unikt primärnyckelindex. Dock kan både ihållande och icke-ihållande beräknade kolumner som definieras på glesa kolumner vara en del av en klustrad nyckel.
En gles kolumn kan inte användas som en partitionsnyckel för ett grupperat index eller en heap. En gles kolumn kan dock användas som partitionsnyckel för ett icke-grupperat index.
En gles kolumn kan inte ingå i en användardefinierad tabelltyp som används i tabellvariabler och tabellvärdesparametrar.
Glesa kolumner är inte kompatibla med datakomprimering. Därför går det inte att lägga till glesa kolumner i komprimerade tabeller och inte heller kan tabeller som innehåller glesa kolumner komprimeras.
Om du ändrar en kolumn från att vara gles till tät eller från tät till gles måste du ändra lagringsformatet för kolumnen. SQL Server Database Engine använder följande procedur för att utföra den här ändringen:
1. Lägger till en ny kolumn i tabellen i den nya lagringsstorleken och formatet.
2. För varje rad i tabellen uppdaterar och kopierar värdet som lagras i den gamla kolumnen till den nya kolumnen.
3. Tar bort den gamla kolumnen från tabellschemat.
4. Återskapar tabellen (om det inte finns något klustrat index) eller återskapar det klustrade indexet för att frigöra utrymme som används av den gamla kolumnen.
Not

Steg 2 kan misslyckas när datastorleken på raden överskrider den maximala tillåtna radstorleken. Den här storleken inkluderar storleken på de data som lagras i den gamla kolumnen och de uppdaterade data som lagras i den nya kolumnen. Den här gränsen är 8 060 byte för tabeller som inte innehåller några glesa kolumner eller 8 018 byte för tabeller som innehåller glesa kolumner. Det här felet kan inträffa även om alla kvalificerade kolumner har flyttats utanför raden.
När du ändrar en icke-gles kolumn till en gles kolumn förbrukar den glesa kolumnen mer utrymme för värden som inte är null. När en rad ligger nära gränsen för maximal radstorlek kan åtgärden misslyckas.

SQL Server-tekniker som stöder glesa kolumner

I det här avsnittet beskrivs hur glesa kolumner stöds i följande SQL Server-tekniker:

Transaktionsreplikering

Transaktionsreplikering stöder glesa kolumner, men den stöder inte kolumnuppsättningar som kan användas med glesa kolumner. Mer information om kolumnuppsättningar finns i Använda kolumnuppsättningar.

Replikeringen av SPARSE-attributet bestäms av ett schemaalternativ som anges med hjälp av sp_addarticle eller med hjälp av dialogrutan Artikelegenskaper i SQL Server Management Studio. Tidigare versioner av SQL Server stöder inte glesa kolumner. Om du måste replikera data till en tidigare version anger du att SPARSE-attributet inte ska replikeras.

För tabeller som publiceras kan du inte lägga till några nya glesa kolumner i en tabell eller ändra den glesa egenskapen för en befintlig kolumn. Om en sådan åtgärd krävs, ta bort och återskapa publikationen.
Sammanfogningsreplikering

Sammanslagningsreplikering stöder inte glesa kolumner eller kolumnuppsättningar.
Ändringsspårning

Ändringsspårning stöder glesa kolumner och kolumnuppsättningar. När en kolumnuppsättning uppdateras i en tabell behandlar ändringsspårning detta som en uppdatering av hela raden. Ingen detaljerad ändringsspårning tillhandahålls för att hämta den exakta mängden glesa kolumner som uppdateras genom åtgärden för att uppdatera kolumnuppsättningar. Om de glesa kolumnerna uppdateras explicit via en DML-instruktion fungerar ändringsspårning på dem normalt och kan identifiera den exakta uppsättningen ändrade kolumner.
Ändra datainsamling

Insamling av ändringsdata stöder glesa kolumner, men det stöder inte kolumnuppsättningar.
Den glesa egenskapen för en kolumn bevaras inte när tabellen kopieras.

Exempel

I det här exemplet innehåller en dokumenttabell en gemensam uppsättning med kolumnerna 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. Koden i det här exemplet skapar en tabell som använder glesa kolumner, infogar två rader i tabellen och sedan väljer data från tabellen.

Notera

Den här tabellen har bara fem kolumner som gör det enklare att visa och läsa. Det är valfritt att deklarera att de glesa kolumnerna ska vara null om alternativet ANSI_NULL_DFLT_ON har angetts. När SET ANSI_DEFAULTS är PÅ, är SET ANSI_NULL_DFLT_ON aktiverat. ANSI_DEFAULTS är PÅ som standard för de flesta anslutningsleverantörer. Mer information finns i SET ANSI_DEFAULTS.

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

Om du vill markera alla kolumner i tabellen returneras en vanlig resultatuppsättning.

SELECT * FROM DocumentStore ;

Här är resultatet.

DocID Title ProductionSpecification ProductionLocation MarketingSurveyGroup

1 Tire Spec 1 AXZZ217 27 NULL

2 Survey 2142 NULL NULL Men 25-35

Eftersom produktionsavdelningen inte är intresserad av marknadsföringsdata vill de använda en kolumnlista som endast returnerar kolumner av intresse, enligt följande fråga.

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

Här är resultatet.

DocID Title ProductionSpecification ProductionLocation

1 Tire Spec 1 AXZZ217 27

Dela via