Můj dobrý přítel Aaron Bertrand mě inspiroval k napsání tohoto článku. Připomněl mi, jak někdy bereme věci jako samozřejmost, když se nám zdají zřejmé a ne vždy se obtěžujeme kontrolovat celý příběh za nimi. Význam T-SQL je, že někdy budeme předpokládat, že víme, že všechno, co se vědět o určité T-SQL funkce, a ne vždy obtěžovat kontrola dokumentace, jestli tam je více k nim. V tomto článku se zabývám řadou funkcí T-SQL, které jsou buď často zcela přehlíženy, nebo které podporují parametry nebo schopnosti, které jsou často přehlíženy. Pokud máte vlastní příklady drahokamů T-SQL, které jsou často přehlíženy, sdílejte je prosím v sekci komentářů tohoto článku.

Než začnete číst tento článek, zeptejte se sami sebe, co víte o následující T-SQL funkce: EOMONTH, PŘEKLAD, KOREKCE, CONCAT a CONCAT_WS, LOG, kurzor proměnné a SLOUČIT s VÝSTUPEM.

v mých příkladech použiji ukázkovou databázi s názvem TSQLV5. Skript, který tuto databázi vytváří a naplňuje, najdete zde, a jeho ER diagram zde.

EOMONTH má druhý parametr

funkce EOMONTH byla zavedena v SQL Server 2012. Mnoho lidí si myslí, že podporuje pouze jeden parametr s datem vstupu, a že jednoduše vrátí datum konce měsíce, které odpovídá datu vstupu.

zvažte trochu sofistikovanější potřebu vypočítat konec předchozího měsíce. Předpokládejme například, že potřebujete dotaz na prodej.Tabulka objednávek a návratové objednávky, které byly zadány na konci předchozího měsíce.

Jeden způsob, jak toho dosáhnout, je použít funkce EOMONTH na SYSDATETIME, aby si na konci měsíce data z aktuálního měsíce, a pak použít funkce DATEADD odečíst měsíčně z výsledku, tak jako:

USE TSQLV5; SELECT orderid, orderdateFROM Sales.OrdersWHERE orderdate = EOMONTH(DATEADD(month, -1, SYSDATETIME()));

Všimněte si, že pokud jste skutečně spustit tento dotaz v TSQLV5 ukázkové databáze budete mít prázdný výsledek od poslední datum objednávky zaznamenány v tabulce 6. Května, 2019. Pokud by však tabulka měla objednávky s datem objednávky, které připadá na poslední den předchozího měsíce, dotaz by je vrátil.

mnoho lidí si neuvědomuje, že EOMONTH podporuje druhý parametr, kde označíte, kolik měsíců přidat nebo odečíst. Zde je syntaxe funkce:

EOMONTH ( start_date )

Náš úkol může být dosaženo snadněji a přirozeně jednoduchým zadáním -1 jako druhý parametr funkce, jako tak:

SELECT orderid, orderdateFROM Sales.OrdersWHERE orderdate = EOMONTH(SYSDATETIME(), -1);

PŘEKLADAČ je někdy jednodušší, než NAHRADIT

Mnoho lidí jsou obeznámeni s funkcí REPLACE a jak to funguje. Použijete jej, pokud chcete nahradit všechny výskyty jednoho podřetězce jiným ve vstupním řetězci. Někdy však, když máte více náhrad, které musíte použít, použití nahradit je trochu složité a má za následek spletité výrazy.

jako příklad předpokládejme, že dostanete vstupní řetězec @s, který obsahuje číslo se španělským formátováním. Ve Španělsku používají tečku jako oddělovač pro skupiny tisíců a čárku jako desetinný oddělovač. Musíte převést vstup na formátování US, kde se čárka používá jako oddělovač pro skupiny tisíců a tečka jako oddělovač desetinných míst.

pomocí jednoho volání funkce Nahradit můžete nahradit pouze všechny výskyty jednoho znaku nebo podřetězce jiným. Chcete-li použít dvě náhrady (periody na čárky a čárky na periody), musíte vnořit volání funkcí. Choulostivé části je, že pokud používáte NAHRADIT jednou pro změnu období, čárky, a pak podruhé proti výsledku změna čárky na doby, můžete skončit s pouze obdobích. Zkuste si to:

DECLARE @s AS VARCHAR(20) = '123.456.789,00'; SELECT REPLACE(REPLACE(@s, '.', ','), ',', '.');

dostanete následující výstup:

123.456.789.00

Pokud chcete držet pomocí funkce REPLACE, budete potřebovat tři volání funkce. Jeden, který nahradí období neutrálním znakem, o kterém víte, že se v datech normálně nemůže objevit (řekněme ~). Další proti výsledku nahradit všechny čárky tečkami. Další proti výsledku nahradit všechny výskyty dočasného znaku (~v našem příkladu) čárkami. Zde je kompletní vyjádření:

DECLARE @s AS VARCHAR(20) = '123.456.789,00';SELECT REPLACE(REPLACE(REPLACE(@s, '.', '~'), ',', '.'), '~', ',');

tentokrát se dostanete na pravý výstup:

123,456,789.00

To je docela proveditelné, ale to má za následek dlouhé a spletité výraz. Co kdybyste měli více náhradníků?

mnoho lidí si není vědomo, že SQL Server 2017 představil novou funkci nazvanou TRANSLATE, která tyto náhrady značně zjednodušuje. Tady je funkce syntaxe:

TRANSLATE ( inputString, characters, translations )

druhý vstup (postavy) je řetězec s na seznam jednotlivých znaků, které chcete nahradit, a třetí vstup (překlady) je řetězec s na seznam odpovídajících znaků, které chcete nahradit zdroj znaky. To přirozeně znamená, že druhý a třetí parametr musí mít stejný počet znaků. Na funkci je důležité, že nedělá samostatné průchody pro každou z náhrad. Pokud ano, mělo by to potenciálně za následek stejnou chybu jako v prvním příkladu, který jsem ukázal pomocí dvou volání na funkci nahradit. V důsledku toho, manipulaci naším úkolem se stává ne-nasnadě:

DECLARE @s AS VARCHAR(20) = '123.456.789,00';SELECT TRANSLATE(@s, '.,', ',.');

Tento kód generuje požadovaný výstup:

123,456,789.00

to je docela hezké!

TRIM je více než LTRIM (RTRIM ())

SQL Server 2017 představil podporu pro funkci TRIM. Mnoho lidí, včetně mě, zpočátku jen předpokládá, že to není nic jiného než jednoduchá zkratka LTRIM(RTRIM(input)). Pokud však zkontrolujete dokumentaci, uvědomíte si, že je ve skutečnosti silnější než to.

než se pustím do podrobností, zvažte následující úkol: vzhledem k zadanému řetězci @s odstraňte přední a zadní lomítka (dozadu a dopředu). Jako příklad předpokládejme, že @s obsahuje následující řetězec:

//\\ remove leading and trailing backward (\) and forward (/) slashes \\//

požadovaný výstup je:

 remove leading and trailing backward (\) and forward (/) slashes 

Všimněte si, že výstup by měl udržet vedoucí a koncové mezery.

Pokud jste nevěděli, ČALOUNĚNÍ je plný možností, tady je jeden způsob, možná jste vyřešili úkol:

DECLARE @s AS VARCHAR(100) = '//\\ remove leading and trailing backward (\) and forward (/) slashes \\//'; SELECT TRANSLATE(TRIM(TRANSLATE(TRIM(TRANSLATE(@s, ' /', '~ ')), ' \', '^ ')), ' ^~', '\/ ') AS outputstring;

řešení se spouští pomocí PŘELOŽIT nahradit všechny mezery s neutrální znak (~) a lomítka se mezery, pak pomocí OŘÍZNUTÍ oříznout úvodní a koncové mezery z výsledku. Tento krok v podstatě ořízne přední a zadní lomítka, dočasně pomocí ~ místo původních mezer. Tady je výsledek tohoto kroku je:

\\~remove~leading~and~trailing~backward~(\)~and~forward~( )~slashes~\\

druhý krok pak využívá PŘELOŽIT nahradit všechny mezery s další neutrální znak (^) a zpětná lomítka se mezery, pak pomocí OŘÍZNUTÍ oříznout úvodní a koncové mezery z výsledku. Tento krok v podstatě ořízne přední a zadní lomítka dozadu, dočasně pomocí ^ místo mezilehlých mezer. Zde je výsledek tohoto kroku:

~remove~leading~and~trailing~backward~( )~and~forward~(^)~slashes~

poslední krok používá PŘEKLÁDAT nahradit mezery zpětná lomítka, ^ s lomítka, a ~ s mezerami, vytváří požadovaný výstup:

 remove leading and trailing backward (\) and forward (/) slashes 

Jako cvičení, zkuste řešení tohoto úkolu s předem SQL Server 2017 kompatibilní řešení, kde nelze použít ZKRÁTIT a PŘELOŽIT.

Zpět na SQL Server 2017 a výše, pokud jste se obtěžovat kontrolu dokumentace, zjistila bys, že TRIM je více sofistikované, že to, co jste si mysleli původně. Zde je syntaxe funkce:

TRIM ( string )

volitelné postavy Z části umožňuje zadat jeden nebo více znaků, které chcete oříznout od začátku a na konci vstupní řetězec. V našem případě, vše, co musíte udělat, je zadat ‚/\‘, jak tuto část, tak jako:

DECLARE @s AS VARCHAR(100) = '//\\ remove leading and trailing backward (\) and forward (/) slashes \\//'; SELECT TRIM( '/\' FROM @s) AS outputstring;

to je docela významné zlepšení oproti předchozímu řešení!

CONCAT a CONCAT_WS

Pokud už nějakou dobu pracujete s T-SQL, víte, jak nepříjemné je vypořádat se s nulami, když potřebujete zřetězit řetězce. Jako příklad, zvážit umístění dat zaznamenaných pro zaměstnance v HR.V tabulce zaměstnanci:

SELECT empid, country, region, cityFROM HR.Employees;

Tento dotaz generuje následující výstup:

empid country region city----------- --------------- --------------- ---------------1 USA WA Seattle2 USA WA Tacoma3 USA WA Kirkland4 USA WA Redmond5 UK NULL London6 UK NULL London7 UK NULL London8 USA WA Seattle9 UK NULL London

Všimněte si, že pro některé zaměstnance regionu část je irelevantní a nepodstatná oblast je zastoupena NULL. Předpokládejme, že je třeba zřetězit části Umístění (země, region a město) pomocí čárky jako oddělovače, ale ignorování nulových oblastí. Když regionu je relevantní, chcete, aby výsledek mají formu <coutry>,<region>,<city> a když regionu je irelevantní chcete výsledek mít podobu <country>,<city>. Za normálních okolností, zřetězení něco s NULL vytváří NULL výsledek. Toto chování můžete změnit vypnutím volby relace CONCAT_NULL_YIELDS_NULL, ale nedoporučoval bych povolit nestandardní chování.

Pokud jste nevěděli o existenci CONCAT a CONCAT_WS funkce, by asi používá ISNULL nebo SPLÝVAJÍ nahradit NULL prázdným řetězcem, tak jako:

SELECT empid, country + ISNULL(',' + region, '') + ',' + city AS locationFROM HR.Employees;

Tady je výstup z tohoto dotazu:

empid location----------- -----------------------------------------------1 USA,WA,Seattle2 USA,WA,Tacoma3 USA,WA,Kirkland4 USA,WA,Redmond5 UK,London6 UK,London7 UK,London8 USA,WA,Seattle9 UK,London

SQL Server 2012 zavedena funkce CONCAT. Tato funkce přijímá seznam vstupů řetězce znaků a zřetězuje je, a přitom ignoruje nuly. Takže pomocí CONCAT můžete toto řešení zjednodušit:

SELECT empid, CONCAT(country, ',' + region, ',', city) AS locationFROM HR.Employees;

Stále, musíte explicitně specifikovat oddělovače jako součást funkce vstupů. Aby se náš život ještě jednodušší, SQL Server 2017 představil podobnou funkci nazvanou CONCAT_WS, kde můžete začít tím, že uvede oddělovač, následuje položek, které chcete zřetězit. S touto funkcí je řešení dále zjednodušeno:

SELECT empid, CONCAT_WS(',', country, region, city) AS locationFROM HR.Employees;

další krok je samozřejmě jasnovidném. 1. Dubna 2020 společnost Microsoft plánuje uvolnit CONCAT_MR. funkce přijme prázdný vstup a automaticky zjistí, které prvky chcete zřetězit čtením vaší mysli. Dotaz pak bude vypadat takto:

SELECT empid, CONCAT_MR() AS locationFROM HR.Employees;

PROTOKOL má druhý parametr

Podobné funkce EOMONTH, mnoho lidí si neuvědomuje, že začíná již s SQL Server 2012 funkce LOG podporuje druhý parametr, který umožňuje uvést logaritmus základny. Předtím T-SQL podporoval funkci LOG(input), která vrací přirozený logaritmus vstupu (pomocí konstanty e jako základny) a LOG10(input), který používá 10 jako základnu.

Není si vědom existence druhý parametr funkce LOG, když lidé chtěli vypočítat Logb(x), kde b je báze, jiné než e a 10, oni často dělali to na dlouhou cestu. Můžete se spolehnout na následující rovnici:

Jako příklad pro výpočet Log2(8), můžete se spolehnout na následující rovnici:

Přeloženo do T-SQL, můžete použít následující výpočet:

DECLARE @x AS FLOAT = 8, @b AS INT = 2;SELECT LOG(@x) / LOG(@b);

Jakmile jste si uvědomit, že PROTOKOL podporuje druhý parametr, kde uvedete základní, výpočet, jednoduše se stává:

DECLARE @x AS FLOAT = 8, @b AS INT = 2;SELECT LOG(@x, @b);

Kurzor proměnné

Pokud pracujete s T-SQL na chvíli, pravděpodobně jste měli mnoho příležitostí k práci s kurzory. Jak víte, při práci s kurzorem obvykle používáte následující kroky:

• Deklarovat kurzor
• kurzor
• Iterovat kurzor záznamy
• kurzor
• Deallocate kurzor

Jako příklad, předpokládejme, že budete potřebovat provést nějaký úkol, za databáze v instanci. Pomocí kurzoru, byste normálně použít kód podobná následující:

DECLARE @dbname AS sysname; DECLARE C CURSOR FORWARD_ONLY STATIC READ_ONLY FOR SELECT name FROM sys.databases; OPEN C; FETCH NEXT FROM C INTO @dbname; WHILE @@FETCH_STATUS = 0BEGIN PRINT N'Handling database ' + QUOTENAME(@dbname) + N'...'; /* ... do your thing here ... */ FETCH NEXT FROM C INTO @dbname;END; CLOSE C;DEALLOCATE C;

příkaz ZAVŘÍT zprávy aktuální sadu výsledků a uvolní zámky. Příkaz DEALLOCATE odstraní odkaz kurzoru, a když je poslední odkaz přidělen, uvolní datové struktury obsahující kurzor. Pokud se pokusíte spustit výše uvedený kód dvakrát bez ÚZKÉ a NAVRÁTIT příkazy, bude se následující chybová zpráva:

Msg 16915, Level 16, State 1, Line 4A cursor with the name 'C' already exists.Msg 16905, Level 16, State 1, Line 6The cursor is already open.

ujistěte Se, že spustit BLÍZKO a NAVRÁTIT příkazy, než budete pokračovat.

Mnoho lidí si neuvědomuje, že když potřebují pracovat s kurzorem v pouze jedné dávce, což je nejčastější případ, namísto použití normální kurzor můžete pracovat s kurzorem proměnné. Jako každá proměnná, rozsah proměnné kurzoru je pouze dávka, kde byla deklarována. To znamená, že jakmile dávka skončí, všechny proměnné vyprší. Použití proměnné kurzoru, jakmile dávka skončí, SQL Server se zavře a automaticky jej přerozdělí, což vám ušetří potřebu explicitně spustit příkaz Zavřít a DEALLOCATE.

zde je revidovaný kód, který tentokrát používá proměnnou kurzoru:

DECLARE @dbname AS sysname, @C AS CURSOR; SET @C = CURSOR FORWARD_ONLY STATIC READ_ONLY FOR SELECT name FROM sys.databases; OPEN @C; FETCH NEXT FROM @C INTO @dbname; WHILE @@FETCH_STATUS = 0BEGIN PRINT N'Handling database ' + QUOTENAME(@dbname) + N'...'; /* ... do your thing here ... */ FETCH NEXT FROM @C INTO @dbname;END;

neváhejte a spusťte jej několikrát, a všimněte si, že tentokrát nedostanete žádné chyby. Je to prostě čistší a nemusíte se starat o udržení zdrojů kurzoru, pokud jste zapomněli zavřít a přerozdělit kurzor.

SLOUČIT s VÝSTUPEM

Od počátku VÝSTUPNÍ klauzule pro změnu prohlášení v SQL Server 2005, to se ukázalo být velmi praktický nástroj, kdykoliv budete chtít vrátit data z upravené řádky. Lidé tuto funkci používají pravidelně pro účely, jako je archivace, audit a mnoho dalších případů použití. Jedna z nepříjemných věcí, o této funkci je, že pokud jej používáte s příkazy INSERT, jste jen dovoleno vrátit data z vložené řádky, prefixu výstupní sloupce se vloží. Nemáte přístup ke sloupcům zdrojové tabulky, i když někdy potřebujete vrátit sloupce ze zdroje vedle sloupců z cíle.

jako příklad zvažte tabulky T1 a T2, které vytvoříte a naplníte spuštěním následujícího kódu:

DROP TABLE IF EXISTS dbo.T1, dbo.T2;GO CREATE TABLE dbo.T1(keycol INT NOT NULL IDENTITY PRIMARY KEY, datacol VARCHAR(10) NOT NULL); CREATE TABLE dbo.T2(keycol INT NOT NULL IDENTITY PRIMARY KEY, datacol VARCHAR(10) NOT NULL); INSERT INTO dbo.T1(datacol) VALUES('A'),('B'),('C'),('D'),('E'),('F');

Všimněte si, že identity vlastnost se používá ke generování klíče v obou tabulkách.

Předpokládejme, že musíte zkopírovat některé řádky od T1 do T2; řekněme ty, kde keycol % 2 = 1. Chcete použít výstupní klauzuli k vrácení nově generovaných klíčů v T2, ale také chcete vrátit vedle těchto klíčů příslušné zdrojové klíče z T1. Intuitivním očekáváním je použití následujícího příkazu INSERT:

INSERT INTO dbo.T2(datacol) OUTPUT T1.keycol AS T1_keycol, inserted.keycol AS T2_keycol SELECT datacol FROM dbo.T1 WHERE keycol % 2 = 1;

Bohužel, jak již bylo zmíněno, VÝSTUPNÍ klauzule neumožňuje odkazovat na sloupce ze zdrojové tabulky, tak dostanete následující chybu:

mnoho lidí si neuvědomuje, že toto omezení se kupodivu nevztahuje na příkaz sloučení. Takže i když je to trochu trapné, můžete převést příkaz INSERT na příkaz MERGE, ale k tomu potřebujete, aby byl predikát MERGE vždy nepravdivý. Tím se aktivuje klauzule WHEN NOT MATCHED a použije se tam jediná podporovaná akce INSERT. Můžete použít fiktivní falešný stav, jako je 1 = 2. Zde je kompletní převést kód:

MERGE INTO dbo.T2 AS TGTUSING (SELECT keycol, datacol FROM dbo.T1 WHERE keycol % 2 = 1) AS SRC ON 1 = 2WHEN NOT MATCHED THEN INSERT(datacol) VALUES(SRC.datacol)OUTPUT SRC.keycol AS T1_keycol, inserted.keycol AS T2_keycol;

Tento časový kód bude úspěšně spuštěn, produkovat následující výstup:

T1_keycol T2_keycol----------- -----------1 13 25 3