Ritka oszlopok használata

Vonatkozik a következőkre: SQL Server 2016 (13.x) és későbbi verziók Azure SQL Database Azure SQL Managed Instance SQL database in Microsoft Fabric

A ritka oszlopok olyan hagyományos oszlopok egy típusa, amelyek optimalizált tárolást biztosítanak a null értékek számára. A ritka oszlopok csökkentik a null értékek térkövetelményeit a nem NULL értékek lekérésének többletköltsége miatt. Érdemes lehet ritkított oszlopokat használni, ha a megtakarított terület legalább 20-40 százalékos. A ritka oszlopokat és oszlopkészleteket a CREATE TABLE vagy ALTER TABLE utasításokkal lehet definiálni.

A ritka oszlopok oszlopkészletekkel és szűrt indexekkel használhatók:

Oszlopkészletek

Az INSERT, UPDATE és DELETE utasítások név szerint hivatkozhatnak a ritka oszlopokra. Megtekintheti és használhatja azonban a táblázat összes ritka oszlopát, amelyek egyetlen XML-oszlopba vannak kombinálva. Ezt az oszlopot oszlopkészletnek nevezzük. További információ az oszlopkészletekről: Oszlopkészletek használata.
Szűrt indexek

Mivel a ritka oszlopok számos null értékű sorból állnak, ezért különösen alkalmasak szűrt indexekre. A ritkán használt oszlopok szűrt indexei csak a kitöltött értékeket tartalmazó sorokat indexelhetik. Ez egy kisebb és hatékonyabb indexet hoz létre. További információ: Szűrt indexek létrehozása.

A ritkán használt oszlopok és szűrt indexek lehetővé teszik az alkalmazások, például a Windows SharePoint Services használatát, hogy hatékonyan tárolhassák és érhessék el a felhasználó által definiált tulajdonságokat az SQL Server használatával.

Ritka oszlopok tulajdonságai

A ritka oszlopok a következő jellemzőkkel rendelkeznek:

Az SQL Server adatbázismotor a SPARSE kulcsszót használja egy oszlopdefinícióban az oszlop értékeinek tárolásának optimalizálásához. Ezért ha az oszlop értéke NULL a tábla bármely sorához, az értékeknek nincs szükségük tárolóra.
A ritka oszlopokkal rendelkező táblák katalógusnézetei megegyeznek egy tipikus tábláéval. A sys.columns katalógusnézet a táblázat minden oszlopához tartalmaz egy sort, és ha van definiálva, tartalmaz egy oszlopkészletet.
A ritka oszlopok nem a logikai tábla, hanem a tárolási réteg tulajdonságai. Ezért egy SELECT ... INTO utasítás nem másolja át a ritka oszlop tulajdonságát egy új táblába.
A COLUMNS_UPDATED függvény egy varbinary értéket ad vissza, amely jelzi a DML-művelet során frissített összes oszlopot. A COLUMNS_UPDATED függvény által visszaadott bitek a következők:
- Ha egy ritka oszlop explicit módon frissül, az adott ritka oszlophoz tartozó bit értéke 1, az oszlopkészlet bitje pedig 1.
- Ha egy oszlopkészlet explicit módon frissül, az oszlopkészlet bitje 1, a táblában lévő ritka oszlopok bitjei pedig 1 értékre vannak beállítva.
- Beszúrási műveletek esetén az összes bit értéke 1.
További információ az oszlopkészletekről: Oszlopkészletek használata.

A következő adattípusok nem adhatók meg SPARSE-ként:

geography
geometry
image
ntext

text
timestamp
felhasználó által definiált adattípusok

Becsült helymegtakarítás adattípus szerint

A ritka oszlopok több tárterületet igényelnek a nem NULL értékekhez, mint a SPARSE jelöléssel nem rendelkező azonos adatokhoz szükséges hely. Az alábbi táblázatok az egyes adattípusok térhasználatát mutatják be. A NULL százalék oszlop azt jelzi, hogy az adatok hány százaléka legyen NULL a 40%-os nettó helymegtakarításhoz.

Fixed-Length adattípusok

Adattípus	Nem ritka bájtok	Ritkás bájtok	NULL százalék
bit	0.125	5	98%
tinyint	1	5	86%
smallint	2	6	76%
int	4	8	64%
bigint	8	12	52%
real	4	8	64%
float	8	12	52%
smallmoney	4	8	64%
money	8	12	52%
smalldatetime	4	8	64%
datetime	8	12	52%
uniqueidentifier	16	20	43%
date	3	7	69%

Pontosságfüggő hosszúságú adattípusok

Adattípus	Nem ritkított bájtok	Szórt bájtok	NULL százalék
datetime2(0)	6	10	57%
datetime2(7)	8	12	52%
time(0)	3	7	69%
time(7)	5	9	60%
datetimetoffset(0)	8	12	52%
datetimetoffset (7)	10	14	49%
decimal/numeric(1,s)	5	9	60%
decimal/numeric(38,s)	17	21	42%
vardecimal(p,s)	Használja a decimális típust konzervatív becslésként.

Adathossztól függő adattípusok

Adattípus	Nem ritkított bájtok	Szórt bájtok	NULL százalék
sql_variant	Az alapul szolgáló adattípustól függően változik
varchar vagy char	2*	4*	60%
nvarchar vagy nchar	2*	4*+	60%
varbináris vagy bináris	2*	4*	60%
xml	2*	4*	60%
hierarchyid	2*	4*	60%

*A hossz megegyezik a típusban található adatok átlagával, plusz 2 vagy 4 bájt.

In-Memory ritkán használt oszlopok frissítéséhez szükséges többletterhelés

A ritka oszlopokkal rendelkező táblák tervezésekor ne feledje, hogy a sor frissítésekor további 2 bájt többletterhelésre van szükség a tábla nem null értékű ritka oszlopaihoz. Ennek a további memóriaigénynek köszönhetően a frissítések váratlanul meghiúsulhatnak az 576-os hibával, ha a teljes sorméret , beleértve ezt a memóriaterhelést is, meghaladja a 8019-et, és egyetlen oszlop sem küldhető le a sorról.

Vegyük példaként azt a táblát, amely 600 ritka, bigint típusú oszlopot tartalmaz. Ha 571 nem null oszlop van, akkor a lemez teljes mérete 571 * 12 = 6852 bájt. A további sor-többletterhelés és a ritkás oszlopfejléc hozzáadásával ez körülbelül 6895 bájtra nő. A lapon továbbra is körülbelül 1124 bájt érhető el a lemezen. Ez azt a benyomást keltheti, hogy további oszlopok is sikeresen frissíthetők. A frissítés során azonban a memória többletterhelést jelent, amely 2*(a nem null értékű ritka oszlopok száma). Ebben a példában, beleértve a további többletterhelést – 2 * 571 = 1142 bájt – a lemez sorméretét körülbelül 8037 bájtra növeli. Ez a méret meghaladja a maximálisan megengedett 8019 bájtot. Mivel az összes oszlop rögzített hosszúságú adattípus, nem mozdíthatók el a sorról. Ennek eredményeképpen a frissítés az 576-os hibával meghiúsul.

Ritka oszlopok használatára vonatkozó korlátozások

A ritka oszlopok bármilyen SQL Server-adattípusúak lehetnek, és az alábbi korlátozásokkal ugyanúgy viselkedhetnek, mint bármely más oszlop:

A ritka oszlopnak null értékűnek kell lennie, és nem rendelkezhet ROWGUIDCOL- vagy IDENTITY-tulajdonságokkal. A ritka oszlop nem lehet a következő adattípusokból: szöveges, ntext, kép, időbélyeg, felhasználó által definiált adattípus, geometriavagy földrajzi; vagy rendelkezik a FILESTREAM attribútummal.
A ritka oszlopnak nem lehet alapértelmezett értéke.
A ritka oszlopokat nem lehet szabályhoz kötni.
Bár a számított oszlopok tartalmazhatnak ritka halmazú oszlopokat, a számított oszlopok nem jelölhetők SPARSE-ként.
Az adatmaszkok definiálhatók ritka oszlopon, az oszlopkészlet részét képező ritka oszlopon azonban nem.
A ritka oszlop nem lehet klaszteres index vagy egyedi elsődleges kulcsindex része. A ritka oszlopokon definiált megmaradó és nem megmaradó számított oszlopok azonban a fürtözött kulcs részét képezhetik.
A ritka oszlop nem használható klaszteres index vagy halom partíciókulcsaként. Egy ritka oszlop azonban használható nem klaszterezett index partíciókulcsaként.
A ritka oszlop nem lehet egy felhasználó által definiált táblatípus része, amelyet táblaváltozókban és táblaértékkel rendelkező paraméterekben használnak.
A ritka oszlopok nem kompatibilisek az adattömörítéssel. Ezért a ritka oszlopokat nem lehet tömörített táblákhoz hozzáadni, és a ritka oszlopokat tartalmazó táblákat sem lehet tömöríteni.
Egy oszlop ritkáról nem ritkára való módosítása, vagy nem ritkáról ritkára való módosítása megköveteli az oszlop tárolási formátumának megváltoztatását. Az SQL Server adatbázismotorja a következő eljárással hajtja végre ezt a módosítást:
1. Új oszlopot ad hozzá a táblához az új tárterület méretében és formátumában.
2. A táblázat minden sorában frissíti és átmásolja a régi oszlopban tárolt értéket az új oszlopba.
3. Eltávolítja a régi oszlopot a táblázatsémából.
4. Újraépíti a táblát (ha nincs fürtözött index), vagy újraépíti a fürtözött indexet, hogy visszanyerje a régi oszlop által használt területet.
Note

A 2. lépés meghiúsulhat, ha a sorban lévő adatok mérete meghaladja a maximálisan megengedett sorméretet. Ez a méret magában foglalja a régi oszlopban tárolt adatok méretét és az új oszlopban tárolt frissített adatokat. Ez a korlát 8060 bájt azoknál a tábláknál, amelyek nem tartalmaznak ritka oszlopokat, illetve 8018 bájtot a ritka oszlopokat tartalmazó táblák esetében. Ez a hiba akkor is előfordulhat, ha az összes jogosult oszlop el lett távolítva a sorból.
Ha egy nem ritka oszlopot ritka oszlopra módosít, a ritka oszlop több helyet fog használni a nem null értékekhez. Ha egy sor közel van a maximális sorméretkorláthoz, a művelet meghiúsulhat.

Ritka oszlopokat támogató SQL Server-technológiák

Ez a szakasz azt ismerteti, hogy a ritka oszlopok hogyan támogatottak a következő SQL Server-technológiákban:

Tranzakciós replikáció

A tranzakciós replikáció támogatja a ritka oszlopokat, de nem támogatja az oszlopkészleteket, amelyek ritkán használt oszlopokkal használhatók. További információ az oszlopkészletekről: Oszlopkészletek használata.

A SPARSE attribútum replikációját egy sémabeállítás határozza meg, amely a sp_addarticle használatával vagy az SQL Server Management Studio Cikk tulajdonságai párbeszédpaneljének használatával van megadva. Az SQL Server korábbi verziói nem támogatják a ritka oszlopokat. Ha adatokat kell replikálnia egy korábbi verzióra, adja meg, hogy a SPARSE attribútumot ne replikálja.

Közzétett táblák esetén nem adhat hozzá új ritka oszlopokat egy táblához, és nem módosíthatja egy meglévő oszlop ritka tulajdonságát. Ha ilyen műveletre van szükség, vesse el és hozza létre újra a kiadványt.
Összevonásos replikáció

Az egyesítési replikáció nem támogatja a ritkán használt oszlopokat és oszlopkészleteket.
Változások követése

A változáskövetés támogatja a ritka oszlopokat és oszlopkészleteket. Ha egy oszlopkészlet frissül egy táblában, a változáskövetés ezt a teljes sor frissítésének tekinti. Nincs részletes változáskövetés az oszlopkészlet frissítési műveletével frissített ritka oszlopok pontos készletének lekéréséhez. Ha a ritka oszlopok kifejezetten DML-utasítással frissülnek, a változások nyomon követése általában működik, és képes azonosítani a módosított oszlopok pontos készletét.
Adatrögzítés módosítása

Az adatváltozások rögzítése támogatja a ritka oszlopokat, de az oszlopkészleteket nem támogatja.
Az oszlop ritka tulajdonsága nem marad meg a táblázat másolásakor.

Examples

Ebben a példában egy dokumentumtábla olyan közös készletet tartalmaz, amely DocID és Titleoszlopokat tartalmaz. Az Előállítási csoport egy ProductionSpecification és ProductionLocation oszlopot szeretne az összes gyártási dokumentumhoz. A Marketing csoport egy MarketingSurveyGroup oszlopot szeretne a marketingdokumentumokhoz. A példában szereplő kód létrehoz egy táblát, amely ritka oszlopokat használ, két sort szúr be a táblába, majd kiválasztja a táblából származó adatokat.

Note

Ez a táblázat csak öt oszlopból áll, így könnyebben megjeleníthető és olvasható. A ritka oszlopok null értékűvé nyilvánítása nem kötelező, ha a ANSI_NULL_DFLT_ON beállítás be van állítva. Ha a SET ANSI_DEFAULTS be van kapcsolva, a SET ANSI_NULL_DFLT_ON engedélyezve van. ANSI_DEFAULTS alapértelmezés szerint be van kapcsolva a legtöbb kapcsolatszolgáltató esetében. További információért lásd a(z) 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

Ha a tábla összes oszlopát ki szeretné jelölni, egy szokásos eredményhalmazt ad vissza.

SELECT * FROM DocumentStore ;

Itt van az eredményhalmaz.

DocID Title ProductionSpecification ProductionLocation MarketingSurveyGroup

1 Tire Spec 1 AXZZ217 27 NULL

2 Survey 2142 NULL NULL Men 25-35

Mivel a termelési részleget nem érdeklik a marketingadatok, olyan oszloplistát szeretnének használni, amely csak érdekes oszlopokat ad vissza, ahogyan az az alábbi lekérdezésben is látható.

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

Itt van az eredményhalmaz.

DocID Title ProductionSpecification ProductionLocation

1 Tire Spec 1 AXZZ217 27

Lásd még

Visszajelzés

Hasznosnak találta ezt az oldalt?

Last updated on 2025-11-18