Delen via

Sparse-kolommen gebruiken

  • Artikel

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

Sparse-kolommen zijn gewone kolommen met een geoptimaliseerde opslag voor null-waarden. Sparse-kolommen verminderen de ruimtevereisten voor null-waarden ten koste van meer overhead om niet-NULL-waarden op te halen. Overweeg om sparsekolommen te gebruiken wanneer de ruimte die is bespaard ten minste 20 procent tot 40 procent is. Sparse kolommen en kolomsets worden gedefinieerd met behulp van de instructies CREATE TABLE of ALTER TABLE instructies.

Sparse-kolommen kunnen worden gebruikt met kolomsets en gefilterde indexen:

  • Kolomsets

    INSERT-, UPDATE- en DELETE-instructies kunnen verwijzen naar de sparsekolommen op naam. U kunt echter ook alle sparsekolommen van een tabel bekijken en ermee werken die zijn gecombineerd in één XML-kolom. Deze kolom wordt een kolomset genoemd. Zie Kolomsets gebruikenvoor meer informatie over kolomsets.

  • Gefilterde indexen

    Omdat sparse-kolommen veel rijen met null-waarden hebben, zijn ze met name geschikt voor gefilterde indexen. Een gefilterde index op een sparse-kolom kan alleen de rijen indexeren die ingevulde waarden hebben. Hierdoor wordt een kleinere en efficiëntere index gemaakt. Zie Gefilterde indexen makenvoor meer informatie.

Met sparsekolommen en gefilterde indexen kunnen toepassingen, zoals Windows SharePoint Services, efficiënt een groot aantal door de gebruiker gedefinieerde eigenschappen opslaan en openen met behulp van SQL Server.

Eigenschappen van spaarzame kolommen

Sparse-kolommen hebben de volgende kenmerken:

  • De SQL Server Database Engine gebruikt het trefwoord SPARSE in een kolomdefinitie om de opslag van waarden in die kolom te optimaliseren. Als de kolomwaarde NULL is voor een rij in de tabel, hebben de waarden daarom geen opslag nodig.

  • Catalogusweergaven voor een tabel met zeldzame kolommen zijn hetzelfde als voor een typische tabel. De sys.columns catalogusweergave bevat een rij voor elke kolom in de tabel en bevat een kolomset als deze is gedefinieerd.

  • Sparse-kolommen zijn een eigenschap van de opslaglaag in plaats van de logische tabel. Daarom kopieert een SELECT ... INTO-instructie de eigenschap van een sparse-kolom niet naar een nieuwe tabel.

  • De functie COLUMNS_UPDATED retourneert een varbinaire waarde om alle kolommen aan te geven die zijn bijgewerkt tijdens een DML-actie. De bits die door de functie COLUMNS_UPDATED worden geretourneerd, zijn als volgt:

    • Wanneer een sparse-kolom expliciet wordt bijgewerkt, wordt de bijbehorende bit voor die parserende kolom ingesteld op 1 en wordt de bit voor de kolomset ingesteld op 1.

    • Wanneer een kolomset expliciet wordt bijgewerkt, worden de bits voor de kolomset ingesteld op 1 en worden de bits voor alle spaarzame kolommen in die tabel ingesteld op 1.

    • Voor invoegbewerkingen worden alle bits ingesteld op 1.

    Zie Kolomsets gebruikenvoor meer informatie over kolomsets.

De volgende gegevenstypen kunnen niet worden opgegeven als SPARSE:

geografie
geometrie
afbeelding
ntext

tekst
tijdstempel
door de gebruiker gedefinieerde gegevenstypen

Geschatte ruimtebesparing per gegevenstype

Sparse-kolommen vereisen meer opslagruimte voor niet-NULL-waarden dan de ruimte die is vereist voor identieke gegevens die niet zijn gemarkeerd als SPARSE. In de volgende tabellen ziet u het ruimtegebruik voor elk gegevenstype. De kolom NULL-percentage geeft aan welk percentage van de gegevens NULL moet zijn voor een nettoruimtebesparing van 40 procent.

Fixed-Length Gegevenstypen

Gegevenstype Niet-sparse bytes Parseren van bytes NULL-percentage
bit 0.125 5 98%
tinyint 1 5 86%
smallint 2 6 76%
int 4 8 64%
grote 8 12 52%
echte 4 8 64%
float 8 12 52%
kleingeld 4 8 64%
geld 8 12 52%
smalldatetime 4 8 64%
datum/tijd 8 12 52%
uniqueidentifier- 16 20 43%
datum 3 7 69%

Precision-Dependent-Length Gegevenstypen

Gegevenstype Niet-sparse bytes Parseren van bytes nulpercentage
datetime2(0) 6 10 57%
datetime2(7) 8 12 52%
tijd(0) 3 7 69%
tijd(7) 5 9 60%
datetimetoffset(0) 8 12 52%
datetimetoffset (7) 10 14 49%
decimaal-numeriek(1,s) 5 9 60%
decimaal/numeriek(38,s) 17 21 42%
vardecimal(p,s) Gebruik het decimaal type als conservatieve schatting.

Gegevens-Dependent-Length Gegevenstypen

Gegevenstype Niet-gespreide bytes Spaarzame bytes NULL-percentage
sql_variant Varieert met het onderliggende gegevenstype
varchar of char 2* 4* 60%
nvarchar of nchar 2* 4*+ 60%
varbinair of binaire 2* 4* 60%
xml-- 2* 4* 60%
hierarchyid- 2* 4* 60%

*De lengte is gelijk aan het gemiddelde van de gegevens in het type, plus 2 of 4 bytes.

In-Memory overhead vereist voor updates aan sparse kolommen

Houd er bij het ontwerpen van tabellen met sparse kolommen rekening mee dat er een extra overhead van 2 bytes vereist is voor elke niet-null sparse kolom in de tabel wanneer een rij wordt bijgewerkt. Als gevolg van deze extra geheugenvereiste kunnen updates onverwacht mislukken met fout 576 wanneer de totale rijgrootte, inclusief deze geheugenoverhead, groter is dan 8019 en er geen kolommen van de rij kunnen worden gepusht.

Bekijk het voorbeeld van een tabel met 600 sparse kolommen van het type bigint. Als er 571 niet-null-kolommen zijn, is de totale grootte op schijf 571 * 12 = 6852 bytes. Nadat u extra rijoverhead en de sparsekolomkop hebt opgenomen, neemt dit toe tot ongeveer 6895 bytes. De pagina heeft nog steeds ongeveer 1124 bytes beschikbaar op schijf. Dit kan de indruk geven dat extra kolommen succesvol kunnen worden bijgewerkt. Tijdens de update is er echter extra overhead in het geheugen, namelijk 2*(aantal niet-null-parseringskolommen). In dit voorbeeld, inclusief de extra overhead - 2 * 571 = 1142 bytes - verhoogt u de rijgrootte op schijf tot ongeveer 8037 bytes. Deze grootte overschrijdt de maximaal toegestane grootte van 8019 bytes. Omdat alle kolommen gegevenstypen met een vaste lengte zijn, kunnen ze niet van de rij worden gepusht. Als gevolg hiervan mislukt de update met de fout 576.

Beperkingen voor het gebruik van sparse kolommen

Sparse-kolommen kunnen van elk SQL Server-gegevenstype zijn en zich gedragen als elke andere kolom met de volgende beperkingen:

  • Een sparse-kolom moet nullable zijn en mag geen ROWGUIDCOL- of IDENTITY-eigenschappen hebben. Een sparsekolom kan niet van de volgende gegevenstypen zijn: tekst, ntext, afbeelding, tijdstempel, door de gebruiker gedefinieerd gegevenstype, geometrieof geografie; of het filestream-kenmerk hebben.

  • Een sparse-kolom kan geen standaardwaarde hebben.

  • Een spaarzame kolom kan niet gekoppeld worden aan een regel.

  • Hoewel een berekende kolom een sparsekolom kan bevatten, kan een berekende kolom niet worden gemarkeerd als SPARSE.

  • Een gegevensmasker kan worden gedefinieerd voor een sparse-kolom, maar niet voor een sparse-kolom die deel uitmaakt van een kolomset.

  • Een sparse-kolom kan geen deel uitmaken van een geclusterde index of een unieke primaire-sleutelindex. Zowel persistente als niet-persistente berekende kolommen die zijn gedefinieerd in sparsekolommen, kunnen echter deel uitmaken van een geclusterde sleutel.

  • Een sparse-kolom kan niet worden gebruikt als partitiesleutel van een geclusterde index of heap. Een sparse-kolom kan echter worden gebruikt als de partitiesleutel van een niet-geclusterde index.

  • Een sparse-kolom kan geen deel uitmaken van een door de gebruiker gedefinieerd tabeltype, dat wordt gebruikt in tabelvariabelen en parameters met tabelwaarden.

  • Sparse-kolommen zijn niet compatibel met gegevenscompressie. Daarom kunnen sparse kolommen niet worden toegevoegd aan gecomprimeerde tabellen en kunnen ook geen tabellen met parseringskolommen worden gecomprimeerd.

  • Het wijzigen van een kolom van sparse in niet-sparse of van niet-sparse naar sparse vereist dat u de opslagindeling van de kolom wijzigt. De SQL Server Database Engine gebruikt de volgende procedure om deze wijziging uit te voeren:

    1. Voegt een nieuwe kolom toe aan de tabel in de nieuwe opslaggrootte en -indeling.

    2. Voor elke rij in de tabel wordt de waarde die in de oude kolom is opgeslagen, bijgewerkt en gekopieerd naar de nieuwe kolom.

    3. Hiermee verwijdert u de oude kolom uit het tabelschema.

    4. Herbouwt de tabel (als er geen geclusterde index is) of herbouwt de geclusterde index om ruimte vrij te maken die wordt gebruikt door de oude kolom.

    Notitie

    Stap 2 kan mislukken wanneer de grootte van de gegevens in de rij groter is dan de maximaal toegestane rijgrootte. Deze grootte omvat de grootte van de gegevens die zijn opgeslagen in de oude kolom en de bijgewerkte gegevens die zijn opgeslagen in de nieuwe kolom. Deze limiet is 8060 bytes voor tabellen die geen sparse kolommen of 8018 bytes bevatten voor tabellen die sparse kolommen bevatten. Deze fout kan ook optreden als alle in aanmerking komende kolommen buiten de rij zijn gepusht.

  • Wanneer u een niet-sparse kolom wijzigt in een sparsekolom, verbruikt de sparsekolom meer ruimte voor niet-nulwaarden. Wanneer een rij zich dicht bij de maximale limiet voor rijgrootte bevindt, kan de bewerking mislukken.

SQL Server-technologieën die ondersteuning bieden voor sparse kolommen

In deze sectie wordt beschreven hoe sparse kolommen worden ondersteund in de volgende SQL Server-technologieën:

  • Transactionele replicatie

    Transactionele replicatie ondersteunt spaarse kolommen, maar biedt geen ondersteuning voor kolomsets, die samen met spaarse kolommen kunnen worden gebruikt. Zie Kolomsets gebruikenvoor meer informatie over kolomsets.

    De replicatie van het kenmerk SPARSE wordt bepaald door een schemaoptie die is opgegeven met behulp van sp_addarticle of met behulp van het dialoogvenster Artikeleigenschappen in SQL Server Management Studio. Eerdere versies van SQL Server bieden geen ondersteuning voor sparse-kolommen. Als u gegevens naar een eerdere versie moet repliceren, geeft u op dat het kenmerk SPARSE niet mag worden gerepliceerd.

    Voor tabellen die zijn gepubliceerd, kunt u geen nieuwe parseringskolommen toevoegen aan een tabel of de sparse-eigenschap van een bestaande kolom wijzigen. Als een dergelijke bewerking is vereist, kunt u de publicatie verwijderen en opnieuw maken.

  • Replicatie samenvoegen

    Samenvoegreplicatie biedt geen ondersteuning voor sparse kolommen of kolomsets.

  • Wijzigingen bijhouden

    Wijzigingen bijhouden ondersteunt spaarzame kolommen en kolomsets. Wanneer een kolomset in een tabel wordt bijgewerkt, beschouwt de functie wijzigingsopsporing dit als een update van de hele rij. Er wordt geen gedetailleerde wijzigingen bijgehouden om de exacte set schaarse kolommen te bepalen die via de updatebewerking van de kolomset worden bijgewerkt. Als de sparse-kolommen expliciet worden bijgewerkt via een DML-instructie, werkt het bijhouden van wijzigingen in deze kolommen normaal gesproken en kan de exacte set gewijzigde kolommen worden geïdentificeerd.

  • Gegevens vastleggen wijzigen

    Wijzigingsgegevens vastleggen ondersteunt sparse kolommen, maar biedt geen ondersteuning voor kolomsets.

  • De parseringseigenschap van een kolom blijft niet behouden wanneer de tabel wordt gekopieerd.

Voorbeelden

In dit voorbeeld bevat een documenttabel een gemeenschappelijke set met de kolommen DocID en Title. De productiegroep wil een kolom ProductionSpecification en ProductionLocation voor alle productiedocumenten. De groep Marketing wil een MarketingSurveyGroup kolom voor marketingdocumenten. Met de code in dit voorbeeld wordt een tabel gemaakt die gebruikmaakt van parseringskolommen, twee rijen in de tabel invoegt en vervolgens gegevens uit de tabel selecteert.

Notitie

Deze tabel heeft slechts vijf kolommen, zodat u deze gemakkelijker kunt weergeven en lezen. Het declareren van de 'sparse columns' als nullable is optioneel als de optie ANSI_NULL_DFLT_ON is ingesteld. Wanneer SET ANSI_DEFAULTS is ingeschakeld, is SET ANSI_NULL_DFLT_ON ingeschakeld. ANSI_DEFAULTS is standaard INGESCHAKELD voor de meeste verbindingsproviders. Zie SET ANSI_DEFAULTSvoor meer informatie.

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

Als u alle kolommen in de tabel wilt selecteren, wordt een gewone resultatenset geretourneerd.

SELECT * FROM DocumentStore ;

Hier zijn de resultaten.

DocID Title ProductionSpecification ProductionLocation MarketingSurveyGroup

1 Tire Spec 1 AXZZ217 27 NULL

2 Survey 2142 NULL NULL Men 25-35

Omdat de productieafdeling niet geïnteresseerd is in de marketinggegevens, willen ze een kolomlijst gebruiken die alleen kolommen met interesse retourneert, zoals wordt weergegeven in de volgende query.

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

Dit is de resultaatset.

DocID Title ProductionSpecification ProductionLocation

1 Tire Spec 1 AXZZ217 27

Zie ook