Megosztás a következőn keresztül:

Oszlopkészletek használata

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

A ritka oszlopokat használó táblák kijelölhetnek egy oszlopkészletet, amely a tábla összes ritka oszlopát visszaadja. Az oszlopkészletek nem beírt XML-reprezentációk, amelyek a tábla összes ritka oszlopát strukturált kimenetbe egyesítik. Az oszlopkészlet olyan, mint egy számított oszlop, amelyben az oszlopkészlet fizikailag nincs tárolva a táblában. Az oszlopkészletek abban különböznek a számított oszlopoktól, hogy az oszlopkészlet közvetlenül frissíthető.

Érdemes lehet oszlopkészleteket használni, ha egy tábla oszlopainak száma nagy, és az egyesével végzett működés nehézkes. Az alkalmazások teljesítménybeli javulást tapasztalhatnak, ha sok oszlopot tartalmazó táblákban oszlopkészletek használatával választanak ki és szúrnak be adatokat. Az oszlopkészletek teljesítménye azonban csökkenthető, ha a tábla oszlopaiban számos index van definiálva. Ennek az az oka, hogy a végrehajtási tervhez szükséges memória mennyisége nő.

Oszlopkészlet meghatározásához használja a *<column_set_name>* FOR ALL_SPARSE_COLUMNS kulcsszavakat a CREATE TABLE vagy ALTER TABLE utasításokban.

Az oszlopkészletek használatának irányelvei

Oszlopkészletek használatakor vegye figyelembe az alábbi irányelveket:

  • A ritka oszlopokat és az oszlopkészleteket ugyanannak az utasításnak a részeként lehet hozzáadni.

  • Oszlopkészletet nem lehet hozzáadni egy táblához, ha ez a tábla már tartalmaz ritka oszlopokat.

  • Az oszlopkészlet oszlopát nem lehet módosítani vagy átnevezni. Az oszlopkészlet módosításához törölnie kell és újra létre kell hoznia a ritka oszlopokat és az oszlopkészletet. A SPARSE kulcsszóval rendelkező oszlopok hozzáadhatók és elvethetők a táblából.

  • Olyan oszlopkészletet adhat hozzá egy táblához, amely nem tartalmaz ritka oszlopokat. Ha a rendszer később hozzáadja a ritka oszlopokat a táblához, azok megjelennek az oszlopkészletben.

  • Táblánként csak egy oszlopkészlet engedélyezett.

  • Az oszlopkészlet használata opcionális, és a ritka oszlopok használata sem kötelező.

  • Az oszlopkészleteken nem határozhatók meg korlátozások vagy alapértelmezett értékek.

  • A számított oszlopok nem tartalmazhatnak oszlopkészlet-oszlopokat.

  • Az elosztott lekérdezések nem támogatottak az oszlopkészleteket tartalmazó táblákban.

  • A replikáció nem támogatja az oszlopkészleteket.

  • A változáskövetés nem támogatja az oszlopkészleteket.

  • Az oszlopkészletek nem tartozhatnak semmilyen indexhez. Ide tartoznak az XML-indexek, a teljes szöveges indexek és az indexelt nézetek. Egy oszlopkészlet nem adható hozzá egy indexbe belefoglalt oszlopként.

  • Egy oszlopkészlet nem használható szűrt index vagy szűrt statisztika szűrőkifejezésében.

  • Ha egy nézet tartalmaz egy oszlopkészletet, az oszlopkészlet XML-oszlopként jelenik meg a nézetben.

  • Az oszlopkészletek nem vehetők fel indexelt nézetdefiníciókba.

  • Az oszlopkészleteket tartalmazó táblákat tartalmazó particionált nézetek akkor frissíthetők, ha a particionált nézet név szerint adja meg a ritka oszlopokat. A particionált nézet nem frissíthető, ha az oszlopkészletre hivatkozik.

  • Az oszlopkészletekre hivatkozó lekérdezési értesítések nem engedélyezettek.

  • Az XML-adatok méretkorlátja 2 GB. Ha egy sor összes nem NULL értékű ritka oszlopának összesített adatai túllépik ezt a korlátot, a lekérdezés vagy a DML művelet hibát fog eredményezni.

  • A COLUMNS_UPDATED függvény által visszaadott adatokról további információt a Ritka oszlopok használatacímű témakörben talál.

Az oszlopkészletből származó adatok kiválasztásának irányelvei

Fontolja meg az alábbi irányelveket az adatok oszlopkészletből való kiválasztásához:

  • Az oszlopkészlet elméletileg egy frissíthető, kiszámított XML-oszloptípus, amely egyetlen XML-reprezentációba összesíti az alapul szolgáló relációs oszlopok készletét. Az oszlopkészlet csak a ALL_SPARSE_COLUMNS tulajdonságot támogatja. Ez a tulajdonság egy adott sor összes ritka oszlopából származó összes nem NULL érték összesítésére szolgál.

  • Az SQL Server Management Studio táblaszerkesztőjében az oszlopkészletek szerkeszthető XML-mezőként jelennek meg. Oszlopkészletek definiálása a következő formátumban:

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

    Az oszlopkészlet értékei például a következők:

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

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

  • A null értékeket tartalmazó ritka oszlopokat a rendszer kihagyja az oszlopkészlet XML-reprezentációjából.

Warning

Az oszlopkészlet hozzáadása megváltoztatja SELECT * lekérdezések viselkedését. A lekérdezés az XML-oszlopként beállított oszlopot adja vissza, és nem az egyes ritka oszlopokat. A sématervezőknek és a szoftverfejlesztőknek ügyelniük kell arra, hogy ne törjenek meg meglévő alkalmazásokat. Az egyes ritka oszlopok továbbra is név alapján kérdezhetők le a SELECT utasításban.

Adatok beszúrása vagy módosítása oszlopkészletben

Szórványos oszlopok adatmanipulációja végrehajtható az egyes oszlopok nevének használatával, vagy az oszlopkészlet nevére való hivatkozással, az oszlopkészlet értékeinek XML-formátumban történő megadásával. A ritka oszlopok tetszőleges sorrendben megjelenhetnek az XML-oszlopban.

Ha a ritka oszlopértékeket az XML-oszlopkészlettel szúrja be vagy frissíti, a mögöttes ritka oszlopokba beszúrt értékek implicit módon lesznek konvertálva az xml adattípusból. A legtöbb numerikus adattípusnál, beleértve bigint, int, smallint, tinyint, bit, lebegőpontosés valós, az XML üres értéke üres karakterlánccá alakul át. Emiatt a rendszer nullát szúr be az oszlopba, ahogy az az alábbi példában is látható. A 0-ra való helyettesítés azonban nem vonatkozik a numerikus és decimális adattípusra, ezeket az értékeket meg kell adni, vagy konverziós hibát fog okozni.

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

Ebben a példában nincs megadva érték a ioszlophoz, de a 0 érték lett beszúrva.

A sql_variant adattípus használata

A sql_variant dátumtípus több különböző adattípust is tárolhat, például int, karakterés dátum. Az oszlopkészletek az olyan adattípus-információkat adják ki, mint a méretezés, a pontosság és a területi adatok, amelyek a létrehozott XML-oszlop attribútumaiként sql_variant értékhez tartoznak. Ha ezeket az attribútumokat egy egyénileg létrehozott XML-utasításban próbálja meg megadni egy oszlopkészlet beszúrási vagy frissítési műveletének bemeneteként, ezek közül néhányat kötelező megadni, és néhányat alapértelmezett értékhez rendel. Az alábbi táblázat azokat az adattípusokat és alapértelmezett értékeket sorolja fel, amelyeket a kiszolgáló generál, ha az érték nincs megadva.

Adattípus localeID* sqlCompareOptions sqlCollationVersion SqlSortId Maximális hossz Precision Scale
char, varchar, binary -1 'Default' 0 0 8000 Nem alkalmazható** Nem alkalmazható
nvarchar -1 'Default' 0 0 4000 Nem alkalmazható Nem alkalmazható
decimális, lebegő, valós Nem alkalmazható Nem alkalmazható Nem alkalmazható Nem alkalmazható Nem alkalmazható 18 0
egész szám, bigint, apró egész szám, kis egész szám Nem alkalmazható Nem alkalmazható Nem alkalmazható Nem alkalmazható Nem alkalmazható Nem alkalmazható Nem alkalmazható
datetime2 Nem alkalmazható Nem alkalmazható Nem alkalmazható Nem alkalmazható Nem alkalmazható Nem alkalmazható 7
dátum-idő eltolás Nem alkalmazható Nem alkalmazható Nem alkalmazható Nem alkalmazható Nem alkalmazható Nem alkalmazható 7
datetime, dátum, smalldatetime Nem alkalmazható Nem alkalmazható Nem alkalmazható Nem alkalmazható Nem alkalmazható Nem alkalmazható Nem alkalmazható
pénz, aprópénz Nem alkalmazható Nem alkalmazható Nem alkalmazható Nem alkalmazható Nem alkalmazható Nem alkalmazható Nem alkalmazható
time Nem alkalmazható Nem alkalmazható Nem alkalmazható Nem alkalmazható Nem alkalmazható Nem alkalmazható 7

* localeID -1 az alapértelmezett területi beállítás. Az angol helyi beállítás kódja 1033.

** Nem alkalmazható = Ezek az attribútumok nem adnak értéket az oszlopkészlet kijelölési művelete során. Hibát okoz, ha a hívó a beszúrási vagy frissítési műveletben megadott oszlophoz megadott XML-reprezentációban megad egy értéket ehhez az attribútumhoz.

Biztonság

Az oszlopkészlet biztonsági modellje a tábla és az oszlopok közötti biztonsági modellhez hasonlóan működik. Az oszlopkészletek minitáblaként jeleníthetők meg, a kijelölési művelet pedig olyan, mint egy SELECT * művelet ezen a minitáblán. A ritka oszlopokra beállított oszlopok közötti kapcsolat azonban csoportosítási kapcsolat, nem pedig tároló. A biztonsági modell ellenőrzi az oszlopkészlet biztonságát, és tiszteletben tartja a DENY műveleteket a mögöttes ritkán használt oszlopokon. A biztonsági modell további jellemzői a következők:

  • A biztonsági engedélyek megadhatók és visszavonhatók az oszlopkészlet oszlopán, hasonlóan a tábla bármely más oszlopához.

  • A SELECT, INSERT, UPDATE, DELETE és REFERENCES engedélyek megadása vagy visszavonása egy oszlopkészlet egy oszlopán nem terjed ki az adott csoport alapjául szolgáló tagoszlopokra. Ez csak az oszlopkészlet adott oszlopának használatára vonatkozik. A 'DENY' jogosultság egy oszlopkészleten hatással van a tábla alapjául szolgáló ritka oszlopokra.

  • Ha a SELECT, INSERT, UPDATE és DELETE utasítást végrehajtja az oszlopkészlet oszlopán, a felhasználónak megfelelő engedélyekkel kell rendelkeznie az oszlopkészlet oszlopán, valamint a tábla összes ritka oszlopára vonatkozó engedélyekkel. Mivel az oszlopkészlet a táblázat összes ritka oszlopát jelöli, minden ritka oszlophoz rendelkeznie kell engedéllyel, és ez magában foglalja azokat a ritka oszlopokat is, amelyeket esetleg nem módosít.

  • A VISSZAVONÁS utasítás végrehajtása egy ritka oszlopon vagy oszlopkészleten alapértelmezés szerint a biztonsági beállításokat visszaállítja a szülőobjektumra.

Examples

Az alábbi példákban egy dokumentumtábla a DocID és Titleoszlopokat tartalmazza. A Termelési csoport egy ProductionSpecification és ProductionLocation oszlopot szeretne az összes termelési dokumentumhoz. A Marketing csoport egy MarketingSurveyGroup oszlopot szeretne a marketingdokumentumokhoz.

A. Hozzon létre egy táblát, amelynek van egy oszlopa

Az alábbi példa a ritka oszlopokat használó táblát hozza létre, és tartalmazza a SpecialPurposeColumnsoszlopkészletet. A példa két sort szúr be a táblázatba, 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ó.

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. Adatok beszúrása táblázatba a ritka oszlopok nevével

Az alábbi példák két sort szúrnak be az A példában létrehozott táblázatba. A példák a ritka oszlopok nevét használják, és nem hivatkoznak az oszlopkészletre.

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. Adatok beszúrása táblázatba az oszlopkészlet nevével

Az alábbi példa egy harmadik sort szúr be az A példában létrehozott táblába. Ezúttal a ritka oszlopok neve nem használatos. Ehelyett az oszlopkészlet nevét használja a program, és a beszúrás a négy ritka oszlop közül kettő értékét adja meg XML formátumban.

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

D. Az oszlopkészletek eredményeinek megfigyelése a SELECT * használatakor

Az alábbi példa az oszlopkészletet tartalmazó táblázat összes oszlopát kijelöli. Egy XML-oszlopot ad vissza a ritka oszlopok kombinált értékeivel. Nem adja vissza külön-külön a ritka oszlopokat.

SELECT DocID, Title, SpecialPurposeColumns FROM DocumentStoreWithColumnSet ;

Itt vannak az eredmények.

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. Figyelje meg a név szerint beállított oszlop kiválasztásának eredményeit

Mivel a termelési részleget nem érdeklik a marketingadatok, ez a példa egy WHERE záradékot ad hozzá a kimenet korlátozásához. A példa az oszlopkészlet nevét használja.

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

Itt vannak az eredmények.

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

F. A ritka oszlopok név szerinti kiválasztásának eredményeinek megfigyelése

Ha egy tábla oszlopkészletet tartalmaz, akkor is lekérdezheti a táblát az egyes oszlopnevek használatával az alábbi példában látható módon.

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

Itt vannak az eredmények.

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

G. Tábla frissítése oszlopkészlet használatával

Az alábbi példa frissíti a harmadik rekordot az adott sor által használt két ritka oszlop új értékeivel.

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

Important

Az oszlopkészletet használó UPDATE utasítás frissíti a táblázat összes ritka oszlopát. Nem hivatkozott ritka oszlopok vannak frissítve NULL-ra.

Az alábbi példa frissíti a harmadik rekordot, de csak a két kitöltött oszlop egyikének értékét adja meg. A második oszlop ProductionLocation nem szerepel a UPDATE utasításban, és frissítve lett NULL-re.

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

Következő lépések

  • Last updated on