Použití řídkých sloupců

Článek
02/04/2025

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

Řídké sloupce jsou běžné sloupce, které mají úložiště optimalizované pro nulové hodnoty. Řídké sloupce snižují nároky na paměť pro hodnoty null za cenu větší režie na načtení nenulových hodnot. Zvažte použití řídkých sloupců, pokud je šetřený prostor alespoň o 20 až 40 procent. Sloupce typu "sparse" a sady sloupců jsou definovány pomocí příkazů CREATE TABLE nebo ALTER TABLE.

Řídké sloupce je možné použít se sadami sloupců a filtrovanými indexy:

Sady sloupců

Příkazy INSERT, UPDATE a DELETE můžou odkazovat na řídké sloupce podle názvu. Můžete ale také zobrazit a pracovat se všemi řídkými sloupci tabulky, které jsou sloučeny do jednoho sloupce XML. Tento sloupec se nazývá sada sloupců. Další informace o sadách sloupců najdete v tématu Použití sad sloupců.
Filtrované indexy

Protože řídké sloupce mají mnoho řádků s hodnotou null, jsou vhodné zejména pro filtrované indexy. Filtrovaný index v řídkém sloupci může indexovat pouze řádky s vyplněnými hodnotami. Tím se vytvoří menší a efektivnější index. Další informace najdete v tématu vytvoření filtrovaných indexů.

Řídké sloupce a filtrované indexy umožňují aplikacím, jako je Windows SharePoint Services, efektivně ukládat a přistupovat k velkému počtu uživatelem definovaných vlastností pomocí SQL Serveru.

Vlastnosti řídkých sloupců

Řídké sloupce mají následující charakteristiky:

Databázový stroj SQL Serveru používá klíčové slovo SPARSE v definici sloupce k optimalizaci úložiště hodnot v daném sloupci. Proto pokud je hodnota sloupce null pro libovolný řádek v tabulce, hodnoty nevyžadují žádné úložiště.
Zobrazení katalogu pro tabulku, která obsahuje řídké sloupce, jsou stejná jako u typické tabulky. Zobrazení katalogu sys.columns obsahuje řádek pro každý sloupec v tabulce a obsahuje sadu sloupců, pokud je definována.
Řídké sloupce jsou vlastností vrstvy úložiště, nikoli logické tabulky. Příkaz SELECT ... INTO proto nekopíruje vlastnost zhuštěného sloupce do nové tabulky.
Funkce COLUMNS_UPDATED vrátí hodnotu typu varbinary, která označuje všechny sloupce, které byly aktualizovány během akce DML. Bity vrácené funkcí COLUMNS_UPDATED jsou následující:
- Když se explicitně aktualizuje řídký sloupec, nastaví se odpovídající bit pro tento řídký sloupec na 1 a bit pro sadu sloupců je nastaven na 1.
- Když je sada sloupců explicitně aktualizována, bit sady sloupců je nastaven na 1 a bity pro všechny řídké sloupce v tabulce jsou nastaveny na 1.
- U operací vložení jsou všechny bity nastavené na 1.
Další informace o sadách sloupců najdete v tématu Použití sad sloupců.

Následující datové typy nelze zadat jako SPARSE:

zeměpis
geometrie
obrázek
ntext

textové
časové razítko
uživatelem definované datové typy

Odhadované úspory místa podle datového typu

Řídké sloupce vyžadují více úložného prostoru pro hodnoty, které nejsou NULL, než je potřeba pro stejná data, která nejsou označena jako řidká. Následující tabulky ukazují využití místa pro každý datový typ. Sloupec NULL Procento označuje, jaké procento dat musí být NULL, aby se dosáhlo úspory místa o 40 procent.

Fixed-Length Datové typy

Datový typ	Neředěné bajty	Řídké bajty	Procento NULL
bit	0.125	5	98%
tinyint	1	5	86%
smallint	2	6	76%
int	4	8	64%
bigint	8	12	52%
skutečné	4	8	64%
plovoucí	8	12	52%
málo peněz	4	8	64%
peníze	8	12	52%
smalldatetime	4	8	64%
datum a čas	8	12	52%
jedinečnýidentifikátor	16	20	43%
datum	3	7	69%

Přesnost – Datové TypyDependent-Length

Datový typ	Neparsparsní bajty	Řídké bajty	Procento NULL
datetime2(0)	6	10	57%
datetime2(7)	8	12	52%
čas(0)	3	7	69%
čas(7)	5	9	60%
datetimetoffset(0)	8	12	52%
datetimetoffset (7)	10	14	49%
desetinné/numerické(1,s)	5	9	60%
desetinné číslo/číslice(38;s)	17	21	42%
vardecimal(p;s)	Jako konzervativní odhad použijte číselný typ .

Data-Dependent-Length Datové typy

Datový typ	Neparsparsní bajty	Řídké bajty	Procento NULL
sql_variant	Liší se u podkladového datového typu.
varchar nebo char	2*	4*	60%
nvarchar nebo nchar	2*	4*+	60%
varbinary nebo binární	2*	4*	60%
xml	2*	4*	60%
hierarchie	2*	4*	60%

*Délka se rovná průměru dat obsažených v typu plus 2 nebo 4 bajty.

In-Memory režijní náklady potřebné pro aktualizace řídkých sloupců

Při navrhování tabulek s řídkými sloupci mějte na paměti, že při aktualizaci řádku se vyžadují další 2 bajty nákladů pro každý sloupec, který není nulový. V důsledku tohoto dalšího požadavku na paměť můžou aktualizace neočekávaně selhat s chybou 576, pokud celková velikost řádku, včetně této režie paměti, překročí 8019 a žádné sloupce se nedají vysunout z řádku.

Představte si příklad tabulky, která obsahuje 600 řídkých sloupců typu bigint. Pokud existuje 571 sloupců, které nejsou null, celková velikost disku je 571 × 12 = 6852 bajtů. Po zahrnutí dodatečných režijních nákladů na řádky a hlavičky řídkých sloupců se cena zvýší na přibližně 6895 bajtů. Stránka má na disku stále k dispozici přibližně 1124 bajtů. To může mít dojem, že se dají úspěšně aktualizovat další sloupce. Během aktualizace však dochází k další režii v paměti, a to představuje 2*(počet nenulových řídkých sloupců). V tomto příkladu včetně dalších režijních nákladů – 2 × 571 = 1142 bajtů – zvětšuje velikost řádku na disku na přibližně 8 037 bajtů. Tato velikost překračuje maximální povolenou velikost 8019 bajtů. Vzhledem k tomu, že všechny sloupce jsou datové typy s pevnou délkou, nelze je posunout mimo řádek. V důsledku toho aktualizace selže s chybou 576.

Omezení používání řídkých sloupců

Řídké sloupce můžou být libovolného datového typu SQL Serveru a chovají se stejně jako jakýkoli jiný sloupec s následujícími omezeními:

Řídký sloupec musí být nastavitelný na hodnotu null a nesmí mít vlastnosti ROWGUIDCOL nebo identity. Časový sloupec nesmí mít následující typy dat: text, ntext, obraz, časová značka, uživatelsky definovaný datový typ, geometrienebo geografie; ani nemůže mít atribut FILESTREAM.
Řídký sloupec nemůže mít výchozí hodnotu.
Řídký sloupec nelze přiřadit k pravidlu.
I když vypočítaný sloupec může obsahovat řídký sloupec, sám vypočítaný sloupec nemůže být označen jako ŘÍDKÝ.
Masku dat lze definovat v řídkém sloupci, ale ne v řídkém sloupci, který je součástí sady sloupců.
Řídký sloupec nemůže být součástí clusterovaného indexu nebo jedinečného indexu primárního klíče. Trvalé i neuchované počítané sloupce definované v řídkých sloupcích ale můžou být součástí clusterovaného klíče.
Řídký sloupec nelze použít jako klíč pro oddíl clusterovaného indexu nebo haldy. Řídký sloupec se ale dá použít jako klíč oddílu neclusterovaného indexu.
Řídký sloupec nemůže být součástí uživatelem definovaného typu tabulky, který se používá v proměnných tabulek a parametrech s hodnotou tabulky.
Zhuštěné sloupce nejsou kompatibilní s kompresí dat. Proto nelze do komprimovaných tabulek přidat řídké sloupce, ani nelze komprimovat žádné tabulky obsahující řídké sloupce.
Změna sloupce z řídce obsazeného na neřídce obsazený nebo naopak vyžaduje změnu formátu úložiště sloupce. Databázový stroj SQL Serveru používá k provedení této změny následující postup:
1. Přidá do tabulky nový sloupec v nové velikosti a formátu úložiště.
2. Pro každý řádek v tabulce aktualizuje a zkopíruje hodnotu uloženou ve starém sloupci do nového sloupce.
3. Odebere starý sloupec ze schématu tabulky.
4. Znovu sestaví tabulku (pokud neexistuje clusterovaný index) nebo znovu sestaví clusterovaný index, aby se uvolnilo místo používané starým sloupcem.
Poznámka

Krok 2 může selhat, když velikost dat v řádku překročí maximální povolenou velikost řádku. Tato velikost zahrnuje velikost dat uložených ve starém sloupci a aktualizovaná data uložená v novém sloupci. Tento limit je 8060 bajtů pro tabulky, které neobsahují žádné řídké sloupce nebo 8018 bajtů pro tabulky, které obsahují řídké sloupce. K této chybě může dojít i v případě, že byly všechny oprávněné sloupce posunuty mimo řádek.
Když změníte neřídký sloupec na řídký sloupec, bude řídký sloupec spotřebovávat více místa pro hodnoty, které nejsou null. Pokud je řádek blízko limitu maximální velikosti řádku, operace může selhat.

Technologie SQL Serveru, které podporují řídké sloupce

Tato část popisuje, jak se řídké sloupce podporují v následujících technologiích SQL Serveru:

Transakční replikace

Transakční replikace podporuje řídké sloupce, ale nepodporuje sady sloupců, které lze použít se řídkými sloupci. Další informace o sadách sloupců najdete v tématu Použití sad sloupců.

Replikace atributu SPARSE je určena možností schématu určenou pomocí sp_addarticle nebo pomocí dialogového okna vlastnosti článku článku v aplikaci SQL Server Management Studio. Starší verze SQL Serveru nepodporují řídké sloupce. Pokud je nutné replikovat data do starší verze, určete, že atribut SPARSE by se neměl replikovat.

U publikovaných tabulek nelze do tabulky přidat žádné nové řídké sloupce ani změnit řídkou vlastnost existujícího sloupce. Pokud je taková operace požadovaná, odstraňte a znovu vytvořte publikaci.
Sloučení replikace

Replikace sloučení nepodporuje řídké sloupce ani sady sloupců.
Sledování změn

Sledování změn podporuje řídké sloupce a sady sloupců. Při aktualizaci sady sloupců v tabulce se sledování změn považuje za aktualizaci celého řádku. Není k dispozici žádné podrobné sledování změn pro získání přesné sady řídkých sloupců, které se aktualizují prostřednictvím operace aktualizace sady sloupců. Pokud se řídké sloupce aktualizují explicitně prostřednictvím příkazu DML, bude sledování změn fungovat obvykle a dokáže identifikovat přesnou sadu změněných sloupců.
Změna zachytávání dat

Funkce Change Data Capture podporuje řídké sloupce, ale nepodporuje sady sloupců.
Řídká vlastnost sloupce se při kopírování tabulky nezachová.

Příklady

V tomto příkladu tabulka dokumentů obsahuje společnou sadu, která obsahuje sloupce DocID a Title. Produkční skupina chce pro všechny produkční dokumenty sloupec ProductionSpecification a ProductionLocation. Marketingová skupina chce pro marketingové dokumenty sloupec MarketingSurveyGroup. Kód v tomto příkladu vytvoří tabulku, která používá řídké sloupce, 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. Deklarování řídkých sloupců, které mají hodnotu null, je volitelné, pokud je nastavena možnost ANSI_NULL_DFLT_ON. Pokud je SET ANSI_DEFAULTS zapnuté, je povoleno SET ANSI_NULL_DFLT_ON. ANSI_DEFAULTS je ve výchozím nastavení pro většinu poskytovatelů připojení zapnuto. Další informace naleznete v části 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

Pokud chcete vybrat všechny sloupce z tabulky, vrátí se běžná sada výsledků.

SELECT * FROM DocumentStore ;

Tady je sada výsledků.

DocID Title ProductionSpecification ProductionLocation MarketingSurveyGroup

1 Tire Spec 1 AXZZ217 27 NULL

2 Survey 2142 NULL NULL Men 25-35

Vzhledem k tomu, že oddělení Production nemá zájem o marketingová data, chce použít seznam sloupců, který vrací pouze sloupce zájmu, jak je znázorněno v následujícím dotazu.

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

Tady je sada výsledků.

DocID Title ProductionSpecification ProductionLocation

1 Tire Spec 1 AXZZ217 27

Sdílet prostřednictvím