Top 10 pokročilých T-SQL příkazů pro vývoj v SQL Serveru

Tento článek je seznamem příkazů a technik pro práci se SQL Serverem, které jsou rutinní součástí práce zejména pokročilých vývojářů v T-SQL. Pokud vás zajímají spíše základy SQL tak doporučuju rozcestník s návody SQL. Na většinu níže uvedených příkazů existuje tady na webu článek s detailním popisem, příkladem a screenshoty.

STRING_SPLIT s parametrem “ordinal” pro rozparsování textu

Novější verze funkce STRING_SPLIT dostupná od SQL Serveru 2022 rozšiřuje základní funkcionalitu o volitelný parametr ordinal, který poskytuje informaci o původním pořadí prvků, což nám dává lepší možnosti při zpracování dat a umožňuje přesnější rekonstrukci vstupních řetězců v transformačních procesech. K této funkci existuje i opak pro spojování řetězců, který je rozebrán zde – SQL STRING_AGG – spojení řádků do jedné hodnoty (concat string).

SELECT [value], [ordinal]
FROM STRING_SPLIT('a,b,c,d', ',', 1);

IIF/CHOOSE jako přehlednější alternativa k CASE

Funkce IIF a CHOOSE je podobná jako CASE s tím, že je přehlednější. Nabízí jednoduchý zápis pro podmínky a výběr z pevně definovaných možností. Tím se redukuje těžkopádnost CASE výrazů a zvyšují přehlednost dotazů.

SELECT IIF([Price] > 100, 'Expensive', 'Cheap');
SELECT CHOOSE(2, 'A', 'B', 'C');

CROSS APPLY a OUTER APPLY

Operátory CROSS APPLY a OUTER APPLY umožňují aplikovat subdotazy nebo tabulkové funkce na každý řádek vstupní (levé) tabulky, přičemž CROSS APPLY vrací pouze odpovídající výsledky. Jedná se o doporučený a nejrychlejší možný způsob řešení podobných datových úloh.

SELECT [c].[Name], [x].[TopOrder]
FROM [Customers] [c]
CROSS APPLY (
    SELECT TOP 1 [OrderID]
    FROM [Orders] [o]
    WHERE [o].[CustomerID] = [c].[ID]
    ORDER BY [o].[Date] DESC
) x;

LAG, LEAD a ROW_NUMBER – Okenní (window) funkce

Window funkce jako LAG, LEAD a ROW_NUMBER (podrobněji viz článek) umožňují pracovat se sousedními hodnotami (např. do aktuálního řádku přečíst předchozí/následující hodnotu), generovat pořadí nebo porovnávat stav mezi řádky uvnitř logického okna.

SELECT
    [SaleDate],
    [Amount],
    LAG([Amount]) OVER (ORDER BY [SaleDate]) AS [PreviousAmount],
    ROW_NUMBER() OVER (ORDER BY [Amount] DESC) AS [RN]
FROM [Sales];

TRY_CONVERT/TRY_PARSE pro bezpečnou konverzi dat

Funkce TRY_CONVERT a TRY_PARSE vracejí místo chyby hodnotu NULL při neplatném vstupu. To umožňuje bezpečně zpracovávat nekonzistentní datové zdroje v případech, kdy nechceme aby nám zpracování dat failovalo. Jedná se o velmi užitečnou funkci třeba při zpracování dat do datového skladu – kde konvertujeme hodnoty do požadovaného formátu datového modelu a nechceme, aby nám chyba konvertu shodila celý nápočet DWH.

SELECT TRY_CONVERT(INT, 'abc');

FOR JSON – Generování JSON

Klauzule FOR JSON generuje JSON dokumenty přímo z tabulkových dat. To je efektivní způsob výstupu pro scénáře kdy potřebujeme pracovat pracující s tímto formátem dat – třeba api a podobně.

SELECT * 
FROM [Orders] 
FOR JSON AUTO;

JSON_VALUE/OPENJSON pro práci s JSON strukturami

Předchozí funkce umožňuje generování JSON a funkce JSON_VALUE a OPENJSON naopak umožňují extrahovat hodnoty z JSON dokumentů nebo je převést do tabulkové podoby.

SELECT JSON_VALUE([JSONColumn], '$.customer.name');

COALESCE jako víceúrovňový fallback výraz

COALESCE umožňuje definovat postupné fallback hodnoty (hodnota, která se má dosadit pokud předchozí hodnota je NULL) a je univerzálnější alternativou k ISNULL, která umožňuje pouze 1 fallback hodnotu. Rozdíly mezi ISNULL a COALESCE řeším v článku SQL ISNULL a COALESCE funkce – jaké jsou rozdíly a použítí

SELECT COALESCE(NULL, NULL, 'Default');

Doporučení ohledně používání tabulkových proměnných

Tabulkové proměnné neobsahují statistiky. To může vést k nesprávným odhadům kardinality a následně k horším exekučním plánům. Jejich používání je tedy vhodné pro nenáročné SQL dotazy a pro větší objemy dat jsou vhodnější dočasné tabulky.

DECLARE @t TABLE (ID INT);

OUTPUT klauzule pro sledování změn při INSERT, UPDATE a DELETE

Klauzule OUTPUT umožňuje zachytit nové, změněné nebo smazané hodnoty přímo v rámci DML příkazů což se hodí třeba pro audit a logování. Příklad zachycení smazaných záznamů při DELETE:

DELETE FROM [Customers]
OUTPUT [deleted].*
WHERE [IsInactive] = 1;