Použití specializovaných typů tabulek

SQL Server podporuje specializované typy tabulek navržené pro konkrétní scénáře a úlohy nad rámec standardních tabulek založených na disku. Tyto typy tabulek, včetně paměti, dočasného, externího, LEDGERu a GRAPHu, řeší konkrétní problémy s výkonem, dodržováním předpisů nebo architekturou, které standardní tabulky nemůžou efektivně řešit.

Pochopení, kdy a jak tyto specializované typy tabulek používat, je zásadní pro navrhování efektivních databázových řešení, která splňují požadavky vaší aplikace.

Použití tabulek optimalizovaných v paměti

U tradičních tabulek založených na disku dochází k latenci vstupně-výstupních operací disku i při ukládání do mezipaměti. V případě scénářů vyžadujících vysokou rychlost, například tisíce transakcí za sekundu s dobou odezvy milisekund, se latence disku stává kritickým bodem. Tabulky v paměti eliminují tento problém tím, že uchovávají data zcela v paměti RAM a využívají optimistickou souběžnost bez použití zámků.

Vysvětlení, kdy použít tabulky v paměti

Tabulky optimalizované v paměti poskytují významné výhody výkonu pro konkrétní úlohy:

• Úložiště stavu relace – Webové aplikace s miliony souběžných relací
• Analýza v reálném čase – Finanční obchodní systémy vyžadující latenci mikrosekund
• OLTP s vysokou frekvencí – Systémy zpracování objednávek zpracovávající 10 000 transakcí za sekundu
• Vrstva ukládání do mezipaměti – často přístupná referenční data (katalogy produktů, konfigurace)
• Ústupové tabulky – Procesy ETL s náročnými operacemi vložení/aktualizace

Například e-commerce web používal in-memory tabulky pro data nákupního košíku, zpracovávající 50 000 souběžných košíků s odezvou pod milisekundu a snižuje latenci při pokladně o 80%.

Zvažte kompromisy

Tabulky v paměti ukládají skutečná data tabulek v paměti RAM pro rychlejší přístup, zatímco tradiční tabulky ukládají data na disk. Velikost dat je však omezená dostupnou pamětí RAM a tyto tabulky nepodporují velké typy objektů, jako je VARCHAR(MAX), NVARCHAR(MAX)nebo VARBINARY(MAX).

I když jsou data tabulky uložená v paměti, SQL Server stále zapisuje transakční protokoly na disk, aby se zajistila stálost. To znamená, že při restartování serveru nepřijdete o potvrzené transakce – data se obnoví z transakčního protokolu zpět do paměti.

Pomocí této možnosti můžete vytvořit tabulku optimalizovanou v MEMORY_OPTIMIZED = ON paměti. Tady je příklad:

-- Create in-memory optimized table
CREATE TABLE dbo.OrderCache (
    OrderID INT PRIMARY KEY NONCLUSTERED,
    CustomerID INT,
    OrderDate DATETIME2,
    Amount DECIMAL(10,2),
    INDEX IX_CustomerID NONCLUSTERED (CustomerID)
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);

Použití dočasných tabulek

Dočasné tabulky automaticky sledují úplnou historii změn dat. Když aktualizujete nebo odstraníte řádek, SQL Server automaticky uloží předchozí verzi v tabulce propojené historie s časovými razítky, která ukazují, kdy byla tato verze platná. K tomu dochází transparentně – upravíte data pomocí normálních INSERTUPDATEpříkazů a DELETE příkazů a databázový stroj zpracovává správu verzí.

Klíčovou výhodou je dotazování dat, protože existovaly v jakémkoli okamžiku. Můžete se zeptat , "jaký byl plat tohoto zaměstnance 1. ledna 2025?" nebo "show me all products that were in stock last quarter" (jaký byl plat tohoto zaměstnance 1. ledna 2025?" nebo "show me all products that were in stock last quarter" (zobrazit všechny produkty, které byly v minulém čtvrtletí" bez zachování složitých tabulek auditu nebo psaní vlastní logiky správy verzí.

Dočasné tabulky obsluhují požadavky na dodržování předpisů, řešení potíží a analýzy:

• Dodržování předpisů a auditování – Finanční záznamy vyžadující úplnou historii změn
• Řešení potíží – Zkoumání zůstatků účtu v době, kdy došlo ke sporovým transakcím
• Analýza trendu – Analýza toho, jak se ceny produktů v průběhu čtvrtletí změnily
• Obnovení dat – Vrácení náhodných aktualizací bez obnovení záloh
• Pomalu se měnící dimenze - Automatizované dimenze datového skladu typu 2

Mezi běžné obchodní scénáře patří aplikace sledující změny platu a povýšení, řízení zásob analyzující trendy, uchovávání historie záznamů pacientů pro dodržování předpisů a sledování změn pojistného krytí pro řešení sporů.

Zvážit výhody časových tabulek

Dočasné tabulky vyžadují nulové změny kódu aplikace a nabízejí transparentní sledování historie. Dotazy k určitému bodu v čase používají jednoduchou syntaxi a automatické čištění spravuje stará data historie. Dočasné tabulky ale přibližně zdvojnásobí požadavky na úložiště.

Dočasné tabulky automaticky udržují úplnou historii změn dat pro auditování a analýzu k určitému bodu v čase.

Dočasnou tabulku můžete vytvořit pomocí SYSTEM_VERSIONING = ON této možnosti. Dočasné tabulky vyžadují ke sledování doby platnosti každé verze řádku dva další DATETIME2 sloupce a PERIOD FOR SYSTEM_TIME klauzuli, která definuje, které sloupce sledují tato časová razítka. Tady je příklad:

-- Create temporal table with automatic history tracking
CREATE TABLE Employee (
    EmployeeID INT PRIMARY KEY,
    EmployeeName NVARCHAR(100),
    Department NVARCHAR(50),
    SysStartTime DATETIME2 GENERATED ALWAYS AS ROW START,
    SysEndTime DATETIME2 GENERATED ALWAYS AS ROW END,
    PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime)
) WITH (SYSTEM_VERSIONING = ON);

-- Query historical data
SELECT * FROM Employee
FOR SYSTEM_TIME AS OF '2026-01-01' 
WHERE EmployeeID = 1;

Když vytvoříte dočasnou tabulku, SQL Server automaticky vytvoří tabulku historie pro uložení předchozích verzí řádků a transparentně spravuje obě tabulky.

Použití externích tabulek

Moderní datové architektury často obsahují data rozptýlená mezi datovými jezery, blob úložišti a různými systémy. Tradičně byste museli všechna data extrahovat, transformovat a načíst (ETL) do databáze před jejich dotazováním. Externí tabulky umožňují virtualizaci dat dotazovat data tam, kde se nacházejí, aniž by se přesunuly, což šetří náklady na úložiště a složitost ETL.

Vysvětlení, kdy použít externí tabulky

Externí tabulky vynikají při dotazování se na data napříč distribuovanými systémy úložiště.

• Integrace služby Data Lake – Dotazování souborů Parquet/CSV ve službě Azure Data Lake Storage bez importu
• Zkoumání dat – Analýza nezpracovaných dat před rozhodováním, co se má importovat
• Optimalizace nákladů – Vyhněte se duplikování dat, která jsou už uložená jinde
• Federované dotazy – Spojení databázových tabulek se soubory v externích systémech
• Archivní úložiště – Přístup k historickým datům uloženým v cenově výhodnějším blob úložišti

Mezi běžné scénáře patří dotazování logovacích souborů podle roků v datových jezerech spolu s transakčními daty, kombinování aktuálních záznamů v databázi s archivovanými daty v blob úložišti, přístup k historickým datům bez úplné migrace a dotazování milionů IoT senzorů souborů JSON bez importu.

Zvažte omezení výkonu.

Externí tabulky poskytují jednotné dotazování napříč zdroji, ale mají omezení:

• Žádný pohyb ani duplikace ukládání dat
• Často pomalejší než nativní tabulky kvůli latenci sítě a analýze souborů
• Jen pro čtení (ve většině scénářů nejde aktualizovat nebo odstranit)
• Omezené indexování a optimalizace

Externí tabulku můžete vytvořit pomocí CREATE EXTERNAL TABLE příkazu se zdrojem dat a formátem souboru. Tady je příklad:

-- Create external table pointing to data lake
CREATE EXTERNAL TABLE dbo.ExternalSalesData (
    OrderID INT,
    CustomerID INT,
    OrderAmount DECIMAL(10,2),
    OrderDate DATE
) WITH (
    LOCATION = '/raw/sales/',
    DATA_SOURCE = DataLakeSource,
    FILE_FORMAT = ParquetFormat
);

Použití tabulek registru

V regulovaných odvětvích je důležité prokázat, že data nebyla manipulována. Tradiční databáze můžou mít data upravená správci, zastaralé změny provedené nebo odstraněné protokoly auditu. Tabulky registru používají kryptografické ověřování inspirované blockchainovou technologií k vytváření záznamů, které je možné nezávisle ověřit, a poskytují kryptografickou kontrolu integrity dat.

Vysvětlení, kdy použít tabulky registru

Tabulky registru slouží k dodržování právních předpisů a potřebám forenzního auditování:

• Finanční transakce - bankovnictví, zpracování plateb, kryptografické výměny
• Dodavatelský řetězec – Sledování původu, opatrovnictví a pravosti produktů
• Právní záznamy – Smlouvy, dohody, právní podání vyžadující nezměnitelnost
• Zdravotní péče – záznamy o předpisech, formuláře souhlasu pacienta
• Vláda - hlasovací záznam, pozemkové registry, vydávání povolení

Například banka může k ukládání záznamů transakcí použít tabulky registru, což umožňuje auditorům ověřit, že po zaúčtování nebyly změněny žádné transakce. Společnost dodavatelského řetězce může sledovat původ produktů pomocí tabulek registru a poskytovat zákazníkům doklad o pravosti.

Volba mezi aktualizovatelnými a připojovacími knihami

Tabulky registru mají dva typy. Aktualizovatelné tabulky registru umožňují INSERT, UPDATEa DELETE operace při sledování všech změn kryptograficky. Systém automaticky ukládá předchozí verze do tabulky historie, podobně jako dočasné tabulky, ale s přidanou výhodou ověřování proti manipulaci. Tabulky registru jen pro připojení umožňují INSERT operace a vytváření skutečně neměnných záznamů pro scénáře vyžadující absolutní integritu dat.

Obě technologie můžete kombinovat vytvořením tabulek, které jsou aktualizovatelnými tabulkami registru i dočasnými tabulkami, a získat kryptografické ověřování společně s možnostmi dotazů k určitému bodu v čase.

Například farmaceutická společnost používá nezměnitelné účetní knihy pro data z klinických hodnocení, které poskytují nezávislým auditorům kryptografický důkaz o tom, že výsledky testů nebyly po odeslání změněny.

Pomocí možnosti LEDGER = ON můžete vytvořit záznamovou tabulku. Tady je příklad:

-- Create ledger table
CREATE TABLE dbo.FinancialTransaction (
    TransactionID INT PRIMARY KEY IDENTITY,
    AccountNumber NVARCHAR(20),
    Amount DECIMAL(15,2),
    TransactionType NVARCHAR(20)
) WITH (LEDGER = ON);

-- Append-only ledger provides immutability
CREATE TABLE dbo.AuditLog (
    LogID INT PRIMARY KEY IDENTITY,
    EventDescription NVARCHAR(500),
    EventTimestamp DATETIME2
) WITH (LEDGER = ON, APPEND_ONLY = ON);

Když vytvoříte tabulku registru, SQL Server automaticky přidá skryté sloupce a vytvoří podpůrné databázové objekty pro sledování kryptografického řetězce. Každá úprava řádku vygeneruje kryptografický hash, který se propojuje s předchozími operacemi a vytváří auditní stopu prokazující možné manipulace. Integritu dat můžete ověřit pomocí integrovaných systémových zobrazení, jako sys.database_ledger_transactions, a pomocí postupů, jako je sp_verify_database_ledger, můžete ověřit, že kryptografický řetězec zůstane nepřerušený.

Použijte tabulky grafu

Relační databáze excelují ve strukturovaných datech, ale mají potíže s vysoce propojenými daty vyžadujícími mnoho spojení. Hledání "přátel přátel" nebo "produktů propojených prostřednictvím tří úrovní kategorií" se stává složitým s tradičními tabulkami. Funkce SQL Graphu nativně modelují uzly (entity) a hrany (relace), což zjednodušuje a zefektivňuje provádění složitých dotazů na relace.

Grafové tabulky zjednodušují modelování relací, ale vyžadují učení nové syntaxe. Poskytují intuitivní modelování propojených dat, jednodušší dotazy pro procházení vztahů a lepší výkon pro víceskokové dotazy. Flexibilní schéma vyhovuje vyvíjejícím se relacím. Tabulky grafů ale mají křivku učení pro MATCH syntaxi a jsou nejvýkonnější pro dotazy zaměřené na čtení relací.

Databáze může obsahovat více uzlů a hraničních tabulek, které spolupracují na modelování dat grafu. Definujete, které tabulky představují uzly a které představují hrany na základě relací dat.

Principy struktury tabulky grafu

SQL Graph používá k relacím modelu dva typy tabulek. Tabulky uzlů ukládají entity a automaticky obsahují skrytý $node_id sloupec, který jednoznačně identifikuje každý uzel. Tabulky hran ukládají relace mezi uzly, obsahují skryté sloupce $edge_id, $from_id, a $to_id k udržování připojení. Tyto speciální sloupce umožňují syntaxí MATCH efektivně procházet relace.

Tabulky grafu můžete vytvářet pomocí AS NODE syntaxe a AS EDGE syntaxe. Tady je příklad:

-- Create graph tables
CREATE TABLE Person AS NODE;
CREATE TABLE Manages AS EDGE;
CREATE TABLE Knows AS EDGE;

-- Insert nodes
INSERT INTO Person VALUES (1, 'Alice'), (2, 'Bob'), (3, 'Charlie');

-- Insert edges (relationships)
INSERT INTO Manages VALUES (1, 2), (2, 3);

-- Query relationships
SELECT Person1.name, Person2.name 
FROM Person AS Person1, Manages, Person AS Person2
WHERE MATCH (Person1-(Manages)->Person2)
AND Person1.id = 1;

Když vytváříte uzly a hraniční tabulky, SQL Server automaticky spravuje skryté systémové sloupce, které umožňují efektivní dotazy procházení grafů.

Každý specializovaný typ tabulky má kompromisy: tabulky v paměti vyžadují paměť RAM, dočasné tabulky dvojité úložiště, externí tabulky přidávají latenci sítě, tabulky registru brání odstranění a tabulky grafů vyžadují novou syntaxi. Při návrhu doporučujeme zvolit správný typ tabulky, protože tato rozhodnutí se po nasazení obtížně mění.