Sdílet prostřednictvím

Použití sad sloupců

  • Článek

platí pro: SQL Server 2016 (13.x) a novější verze Azure SQL DatabaseAzure SQL Managed InstanceSQL Database v Microsoft Fabric

Tabulky, které používají řídké sloupce, mohou určit sadu sloupců, aby vrátily všechny řídké sloupce v tabulce. Sada sloupců je netypová reprezentace XML, která kombinuje všechny řídké sloupce tabulky do strukturovaného výstupu. Sada sloupců je jako počítaný sloupec v tom, že sada sloupců není fyzicky uložena v tabulce. Sada sloupců se liší od počítaného sloupce v tom, že sada sloupců je přímo aktualizovatelná.

Pokud je počet sloupců v tabulce velký, měli byste zvážit použití sad sloupců a práce na nich jednotlivě je těžkopádná. Aplikace můžou při výběru a vkládání dat pomocí sad sloupců v tabulkách s velkým množstvím sloupců zobrazit určité zlepšení výkonu. Výkon sad sloupců se ale dá snížit, když je na sloupcích v tabulce definováno mnoho indexů. Důvodem je to, že se zvyšuje množství paměti potřebné pro plán provádění.

K definování sady sloupců použijte klíčová slova *<column_set_name>* FOR ALL_SPARSE_COLUMNS v příkazech CREATE TABLE nebo ALTER TABLE.

Pokyny pro používání sad sloupců

Při použití sad sloupců zvažte následující pokyny:

  • Řídké sloupce a sloupcová sada mohou být přidány jako součást stejného příkazu.

  • Sadu sloupců nelze přidat do tabulky, pokud tato tabulka již obsahuje řídké sloupce.

  • Sloupec sady sloupců nelze změnit ani přejmenovat. Pokud chcete změnit sadu sloupců, musíte odstranit a znovu vytvořit řídké sloupce a sadu sloupců. Sloupce s klíčovým slovem SPARSE je možné přidat a vyhodit z tabulky.

  • Sadu sloupců lze přidat do tabulky, která neobsahuje žádné řídké sloupce. Pokud se do tabulky později přidají řídké sloupce, zobrazí se v sadě sloupců.

  • Pro každou tabulku je povolena pouze jedna sada sloupců.

  • Sada sloupců je volitelná a není nutná k použití řídkých sloupců.

  • Omezení nebo výchozí hodnoty nelze definovat v sadě sloupců.

  • Počítané sloupce nemohou obsahovat sady sloupců.

  • Distribuované dotazy nejsou podporovány u tabulek, které obsahují sady sloupců.

  • Replikace nepodporuje sady sloupců.

  • Zachytávání dat změn nepodporuje sady sloupců.

  • Sada sloupců nemůže být součástí žádného typu indexu. To zahrnuje indexy XML, fulltextové indexy a indexovaná zobrazení. Sadu sloupců nelze přidat jako zahrnutý sloupec v žádném indexu.

  • Sadu sloupců nelze použít ve filtrovacím výrazu filtrovaného indexu nebo filtrované statistiky.

  • Pokud zobrazení obsahuje sadu sloupců, zobrazí se sada sloupců v zobrazení jako sloupec XML.

  • Sadu sloupců nelze zahrnout do definice indexovaného zobrazení.

  • Dělené zobrazení, která obsahují tabulky obsahující sady sloupců, se dají aktualizovat, když rozdělené zobrazení určuje řídké sloupce podle názvu. Dělené zobrazení není možné aktualizovat, pokud odkazuje na sadu sloupců.

  • Oznámení dotazů, která odkazují na sady sloupců, nejsou povolená.

  • Data XML mají limit velikosti 2 GB. Pokud kombinovaná data všech sloupců bez hodnoty NULL v řádku překročí tento limit, operace dotazu nebo DML způsobí chybu.

  • Informace o datech vrácených funkcí COLUMNS_UPDATED naleznete v tématu Použití řídkých sloupců.

Pokyny pro výběr dat ze sady sloupců

Zvažte následující pokyny pro výběr dat ze sady sloupců:

  • Koncepčně je sada sloupců typem aktualizovatelného počítaného sloupce XML, který agreguje sadu základních relačních sloupců do jediné reprezentace XML. Sada sloupců podporuje pouze vlastnost ALL_SPARSE_COLUMNS. Tato vlastnost slouží k agregaci všech hodnot bez hodnoty NULL ze všech řídkých sloupců pro konkrétní řádek.

  • V editoru tabulek aplikace SQL Server Management Studio se sady sloupců zobrazí jako upravitelné pole XML. Definujte sady sloupců ve formátu:

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

    Příklady hodnot sady sloupců jsou následující:

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

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

  • Řídké sloupce, které obsahují hodnoty null, jsou vynechány z reprezentace XML pro sadu sloupců.

Varování

Přidání sady sloupců změní chování SELECT * dotazů. Dotaz vrátí sadu sloupců jako sloupec XML a nevrátí jednotlivé řídké sloupce. Návrháři schémat a vývojáři softwaru musí být opatrní, aby nepřerušili stávající aplikace. Jednotlivé řídké sloupce se stále dají dotazovat podle názvu v příkazu SELECT.

Vložení nebo úprava dat v sadě sloupců

Manipulaci s daty zhuštěného sloupce je možné provést pomocí názvu jednotlivých sloupců nebo odkazem na název sady sloupců a zadáním hodnot sady sloupců pomocí formátu XML sady sloupců. Řídké sloupce se můžou zobrazit v libovolném pořadí ve sloupci XML.

Při vkládání nebo aktualizaci řídkých hodnot sloupců pomocí sady sloupců XML se hodnoty vložené do podkladových řídkých sloupců implicitně převedou z datového typu xml. V případě většiny číselných datových typů, včetně bigint, int, smallint, tinyint, bit, floata real, se prázdná hodnota v XML pro tento sloupec převede na prázdný řetězec. To způsobí, že se do sloupce vloží nula, jak je znázorněno v následujícím příkladu. Nahrazení 0 se však nevztahuje na číselné datové typy a desetinné datové typy , tyto hodnoty musí být zadány, jinak způsobí chybu převodu.

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

V tomto příkladu nebyla pro sloupec izadána žádná hodnota, ale hodnota 0 byla vložena.

Použití datového typu sql_variant

Datový typ sql_variant může ukládat více různých datových typů, například int, chara datum. Sady sloupců zobrazují informace o datovém typu, jako je škálování, přesnost a národní prostředí, které jsou k hodnotě sql_variant přidružené jako atributy v generovaném sloupci XML. Pokud se pokusíte poskytnout tyto atributy ve vlastním vygenerovaném příkazu XML jako vstup pro operaci vložení nebo aktualizace v sadě sloupců, některé z těchto atributů jsou povinné a některé z nich mají přiřazenou výchozí hodnotu. Následující tabulka uvádí datové typy a výchozí hodnoty, které server vygeneruje, když není zadaná hodnota.

Datový typ localeID* sqlCompareOptions sqlCollationVersion SqlSortId Maximální délka Přesnost Škála
char, varchar, binární -1 Výchozí 0 0 8 000 Nepoužitelné** Není relevantní
nvarchar -1 Výchozí 0 0 4000 Není relevantní Není relevantní
desetinné číslo, float, reálné číslo Není relevantní Není relevantní Není relevantní Není relevantní Není relevantní 18 0
integer, bigint, tinyint, smallint Není relevantní Není relevantní Není relevantní Není relevantní Není relevantní Není relevantní Není relevantní
datetime2 Není relevantní Není relevantní Není relevantní Není relevantní Není relevantní Není relevantní 7
časový posun Není relevantní Není relevantní Není relevantní Není relevantní Není relevantní Není relevantní 7
datum a čas, , malý datum a čas Není relevantní Není relevantní Není relevantní Není relevantní Není relevantní Není relevantní Není relevantní
peníze, malopeníze Není relevantní Není relevantní Není relevantní Není relevantní Není relevantní Není relevantní Není relevantní
čas Není relevantní Není relevantní Není relevantní Není relevantní Není relevantní Není relevantní 7

* id národního prostředí -1 znamená výchozí národní prostředí. Anglické místní nastavení je 1033.

** Nelze použít = Pro tyto atributy nejsou výstupem žádné hodnoty během operace výběru v sadě sloupců. Vygeneruje chybu při zadání hodnoty pro tento atribut volajícím v reprezentaci XML zadané pro sloupec nastavený v operaci vložení nebo aktualizace.

Bezpečnost

Model zabezpečení pro sadu sloupců funguje podobně jako model zabezpečení, který existuje mezi tabulkami a sloupci. Sady sloupců lze vizualizovat jako mini-table a výběrová operace je jako operace SELECT * v této mini-tabulce. Vztah mezi sadou sloupců a řídkými sloupci je ale seskupovací vztah namísto čistě kontejnerového. Model zabezpečení ověřuje zabezpečení v sadě sloupců a respektuje operace DENY na podkladových řídkých sloupcích. Další charakteristiky modelu zabezpečení jsou následující:

  • Oprávnění zabezpečení lze udělit a odvolat pro sloupec v sadě sloupců, podobně jako pro jakýkoli jiný sloupec v tabulce.

  • UDĚLENÍ nebo ODVOLÁNÍ OPRÁVNĚNÍ SELECT, INSERT, UPDATE, DELETE a REFERENCES na sloupci sady sloupců se nepřenáší na podkladové členské sloupce této sady. Týká se to pouze použití sloupce v rámci sady sloupců. Oprávnění ODEPŘÍT u sady sloupců se rozšíří na základní řídké sloupce tabulky.

  • Spuštění příkazů SELECT, INSERT, UPDATE a DELETE ve sloupci sady sloupců vyžaduje, aby uživatel měl odpovídající oprávnění k tomuto sloupci a také odpovídající oprávnění ke všem řídkým sloupcům v tabulce. Vzhledem k tomu, že sada sloupců představuje všechny řídké sloupce v tabulce, musíte mít oprávnění ke všem řídkým sloupcům a to zahrnuje řídké sloupce, které se nemusí měnit.

  • Spuštěním příkazu REVOKE v řídkém sloupci nebo sadě sloupců se zabezpečení nastaví zpět na výchozí úroveň jejich nadřazeného objektu.

Příklady

V následujících příkladech tabulka dokumentů obsahuje společnou sadu sloupců DocID a Title. Produkční skupina chce pro všechny produkční dokumenty sloupec ProductionSpecification a ProductionLocation. Skupina Marketing chce pro marketingové dokumenty sloupec MarketingSurveyGroup.

A. Vytvoření tabulky se sadou sloupců

Následující příklad vytvoří tabulku, která používá řídké sloupce a obsahuje sadu sloupců SpecialPurposeColumns. Příklad vloží do tabulky dva řádky a pak vybere data z tabulky.

Poznámka

Tato tabulka obsahuje jenom pět sloupců, aby se snadněji zobrazovalo a četlo.

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. Vložení dat do tabulky pomocí názvů řídkých sloupců

Následující příklady vloží do tabulky vytvořené v příkladu A dva řádky. Příklady používají názvy řídkých sloupců a neodkazují na sadu sloupců.

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. Vložení dat do tabulky pomocí názvu sady sloupců

Následující příklad vloží třetí řádek do tabulky vytvořené v příkladu A. Tentokrát se nepoužijí názvy řídkých sloupců. Místo toho se použije název sady sloupců a vložení poskytuje hodnoty pro dva ze čtyř řídkých sloupců ve formátu XML.

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

D. Sledování výsledků sady sloupců při použití příkazu SELECT *

Následující příklad vybere všechny sloupce z tabulky, která obsahuje sadu sloupců. Vrátí sloupec XML s kombinovanými hodnotami řídkých sloupců. Nevrací řídké sloupce jednotlivě.

SELECT DocID, Title, SpecialPurposeColumns FROM DocumentStoreWithColumnSet ;

Zde jsou výsledky.

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. Podívejte se na výsledky výběru sloupce nastaveného podle názvu.

Vzhledem k tomu, že oddělení Production nemá zájem o marketingová data, přidá tento příklad klauzuli WHERE k omezení výstupu. Příklad používá název sady sloupců.

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

Zde je sada výsledků.

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

F. Prohlédněte si výsledky výběru řídkých sloupců podle názvu.

Pokud tabulka obsahuje sadu sloupců, můžete se na tabulku dotazovat pomocí názvů jednotlivých sloupců, jak je znázorněno v následujícím příkladu.

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

Zde je sada výsledků.

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

G. Aktualizace tabulky pomocí sady sloupců

Následující příklad aktualizuje třetí záznam s novými hodnotami pro oba řídké sloupce, které ten řádek používá.

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

Důležitý

Příkaz UPDATE, který používá sadu sloupců, aktualizuje všechny řídké sloupce v tabulce. Ředěné sloupce, na které nejsou odkazy, se aktualizují na NULL.

Následující příklad aktualizuje třetí záznam, ale určuje pouze hodnotu jednoho ze dvou naplněných sloupců. Druhý sloupec ProductionLocation není součástí příkazu UPDATE a aktualizuje se na NULL.

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

Další kroky