mój dobry przyjaciel Aaron Bertrand zainspirował mnie do napisania tego artykułu. Przypomniał mi, jak czasami bierzemy rzeczy za pewnik, kiedy wydają się nam oczywiste i nie zawsze sprawdzamy pełną historię za nimi. Znaczenie dla T-SQL polega na tym, że czasami Zakładamy, że wiemy wszystko o pewnych funkcjach T-SQL i nie zawsze sprawdzamy dokumentację, aby sprawdzić, czy jest ich więcej. W tym artykule omówię szereg funkcji T-SQL, które są często całkowicie pomijane, lub które obsługują parametry lub możliwości, które są często pomijane. Jeśli masz własne przykłady klejnotów T-SQL, które są często pomijane, podziel się nimi w sekcji komentarzy tego artykułu.
zanim zaczniesz czytać ten artykuł, zadaj sobie pytanie, co wiesz o następujących funkcjach T-SQL: EOMONTH, TRANSLATE, TRIM, CONCAT i CONCAT_WS, LOG, cursor variables i MERGE with OUTPUT.
w moich przykładach użyję przykładowej bazy danych o nazwie TSQLV5. Skrypt tworzący i wypełniający tę bazę danych znajdziesz tutaj, a jego diagram ER tutaj.
EOMONTH ma drugi parametr
funkcja EOMONTH została wprowadzona w SQL Server 2012. Wiele osób uważa, że obsługuje tylko jeden parametr zawierający datę wejściową i po prostu zwraca datę końca miesiąca, która odpowiada dacie wejściowej.
rozważmy nieco bardziej wyrafinowaną potrzebę obliczenia końca poprzedniego miesiąca. Załóżmy na przykład, że musisz zapytać o sprzedaż.Tabela zamówień i zwrot zamówień, które zostały złożone na koniec poprzedniego miesiąca.
jednym ze sposobów osiągnięcia tego jest zastosowanie funkcji EOMONTH do SYSDATETIME, aby uzyskać datę końca miesiąca bieżącego miesiąca, a następnie zastosowanie funkcji DATEADD, aby odjąć miesiąc od wyniku, w następujący sposób:
USE TSQLV5; SELECT orderid, orderdateFROM Sales.OrdersWHERE orderdate = EOMONTH(DATEADD(month, -1, SYSDATETIME()));
ależy pamiętać, że jeśli faktycznie uruchomisz to zapytanie w przykładowej bazie danych tsqlv5, otrzymasz pusty wynik, ponieważ ostatnia data zamówienia zapisana w tabeli to 6 maja 2019. Jeśli jednak w tabeli znajdują się zamówienia z datą zamówienia przypadającą na ostatni dzień poprzedniego miesiąca, zapytanie zwróciłoby te zamówienia.
Wiele osób nie zdaje sobie sprawy, że EOMONTH obsługuje drugi parametr, w którym wskazujesz, ile miesięcy dodać lub odjąć. Oto składnia funkcji:
EOMONTH ( start_date )
nasze zadanie można osiągnąć łatwiej i naturalnie, po prostu podając -1 jako drugi parametr funkcji, tak jak:
SELECT orderid, orderdateFROM Sales.OrdersWHERE orderdate = EOMONTH(SYSDATETIME(), -1);
TRANSLATE jest czasami prostsze niż REPLACE
Wiele osób zna funkcję REPLACE i jej działanie. Używasz go, gdy chcesz zastąpić wszystkie wystąpienia jednego podłańcucha innym w łańcuchu wejściowym. Czasami jednak, gdy masz wiele zamienników, które musisz zastosować, użycie REPLACE jest nieco trudne i skutkuje zawiłymi wyrażeniami.
jako przykład załóżmy, że otrzymujesz wejściowy ciąg @s, który zawiera liczbę z hiszpańskim formatowaniem. W Hiszpanii używa się kropki jako separatora dla grup tysięcy, a przecinka jako separatora dziesiętnego. Musisz przekonwertować dane wejściowe do formatowania US, gdzie przecinek jest używany jako separator dla grup tysięcy, a kropka jako separator dziesiętny.
używając jednego wywołania funkcji REPLACE, możesz zastąpić tylko wszystkie wystąpienia jednego znaku lub podłańcucha innym. Aby zastosować dwa zamienniki (kropki do przecinków i przecinki do kropek), należy zagnieżdżać wywołania funkcji. Najtrudniejsze jest to, że jeśli użyjesz opcji Zastąp raz, aby zmienić okresy na przecinki, a następnie drugi raz, aby zmienić przecinki na okresy, otrzymasz tylko okresy. Spróbuj:
DECLARE @s AS VARCHAR(20) = '123.456.789,00'; SELECT REPLACE(REPLACE(@s, '.', ','), ',', '.');
otrzymasz następujące wyjście:
123.456.789.00
jeśli chcesz pozostać przy użyciu funkcji replace, potrzebujesz trzech wywołań funkcji. Jedną z nich jest zastąpienie kropek znakiem neutralnym, o którym wiesz, że nie może normalnie pojawić się w danych (powiedzmy~). Inny przeciw wyniku, aby zastąpić wszystkie przecinki kropkami. Inny przeciwko wyniku, aby zastąpić wszystkie wystąpienia znaku tymczasowego (~ w naszym przykładzie) przecinkami. Oto pełne wyrażenie:
DECLARE @s AS VARCHAR(20) = '123.456.789,00';SELECT REPLACE(REPLACE(REPLACE(@s, '.', '~'), ',', '.'), '~', ',');
tym razem otrzymasz właściwe wyjście:
123,456,789.00
to trochę wykonalne, ale skutkuje długim i zawiłym wyrażeniem. Co, jeśli będziesz miał więcej zastępstw do zastosowania?
Wiele osób nie jest świadomych, że SQL Server 2017 wprowadził nową funkcję o nazwie TRANSLATE, która znacznie upraszcza takie zamienniki. Oto składnia funkcji:
TRANSLATE ( inputString, characters, translations )
drugie wejście (znaki) jest ciągiem znaków z listą poszczególnych znaków, które chcesz zastąpić, a trzecie wejście (tłumaczenia) jest ciągiem znaków z listą odpowiadających im znaków, którymi chcesz zastąpić znaki źródłowe. Oznacza to oczywiście, że drugi i trzeci parametry muszą mieć taką samą liczbę znaków. Co jest ważne w tej funkcji, to to, że nie robi oddzielnych przejść dla każdego z zamienników. Gdyby tak było, mogłoby to potencjalnie spowodować ten sam błąd, co w pierwszym przykładzie, który pokazałem używając dwóch wywołań funkcji REPLACE. W związku z tym obsługa naszego zadania staje się prosta:
DECLARE @s AS VARCHAR(20) = '123.456.789,00';SELECT TRANSLATE(@s, '.,', ',.');
ten kod generuje pożądane wyjście:
123,456,789.00
to całkiem niezłe!
TRIM to więcej niż LTRIM(RTRIM ())
SQL Server 2017 wprowadził obsługę funkcji TRIM. Wiele osób, w tym ja, początkowo zakłada, że jest to tylko prosty skrót do LTRIM(RTRIM(input)). Jeśli jednak sprawdzisz dokumentację, zdasz sobie sprawę, że jest ona potężniejsza.
zanim przejdę do szczegółów, rozważ następujące zadanie: biorąc pod uwagę ciąg wejściowy @s, Usuń początkowe i końcowe ukośniki (do tyłu i do przodu). Jako przykład załóżmy, że @s zawiera następujący ciąg znaków:
//\\ remove leading and trailing backward (\) and forward (/) slashes \\//
pożądane wyjście to:
remove leading and trailing backward (\) and forward (/) slashes
należy pamiętać, że wyjście powinno zachować spacje początkową i końcową.
Jeśli nie znasz pełnych możliwości TRIM ’ a, oto jeden sposób, w jaki mogłeś rozwiązać zadanie:
DECLARE @s AS VARCHAR(100) = '//\\ remove leading and trailing backward (\) and forward (/) slashes \\//'; SELECT TRANSLATE(TRIM(TRANSLATE(TRIM(TRANSLATE(@s, ' /', '~ ')), ' \', '^ ')), ' ^~', '\/ ') AS outputstring;
rozwiązanie rozpoczyna się od użycia TRANSLATE aby zastąpić wszystkie spacje znakiem neutralnym (~) i przekreślać ukośniki spacjami, a następnie za pomocą przycięcia przyciąć spacje początkową i końcową od wyniku. Ten krok zasadniczo przycina początkowe i końcowe ukośniki do przodu, tymczasowo używając ~ zamiast oryginalnych spacji. Oto wynik tego kroku:
\\~remove~leading~and~trailing~backward~(\)~and~forward~( )~slashes~\\
drugi krok wykorzystuje TRANSLATE do zastąpienia wszystkich spacji innym znakiem neutralnym (^) i ukośników wstecznych spacjami, a następnie za pomocą TRIM do przycięcia spacji początkowych i końcowych z wyniku. Ten krok zasadniczo przycina początkowe i końcowe ukośniki do tyłu, tymczasowo używając ^ zamiast spacji pośrednich. Oto wynik tego kroku:
~remove~leading~and~trailing~backward~( )~and~forward~(^)~slashes~
ostatni krok wykorzystuje TRANSLATE do zastąpienia spacji ukośnikami do tyłu, ^ ukośnikami do przodu i ~ spacjami, generując pożądane wyjście:
remove leading and trailing backward (\) and forward (/) slashes
jako ćwiczenie spróbuj rozwiązać to zadanie za pomocą rozwiązania zgodnego z pre-SQL Server 2017, w którym nie można używać TRIM i TRANSLATE.
Wracając do SQL Server 2017 i nowszych, gdybyś zadał sobie trud sprawdzania dokumentacji, odkryłbyś, że TRIM jest bardziej wyrafinowany niż początkowo myślałeś. Oto składnia funkcji:
TRIM ( string )
opcjonalne znaki z części pozwalają określić jeden lub więcej znaków, które mają być przycięte od początku i końca ciągu wejściowego. W naszym przypadku wszystko, co musisz zrobić, to podać '/\’ jako tę część, tak:
DECLARE @s AS VARCHAR(100) = '//\\ remove leading and trailing backward (\) and forward (/) slashes \\//'; SELECT TRIM( '/\' FROM @s) AS outputstring;
to dość znaczna poprawa w porównaniu do poprzedniego rozwiązania!
CONCAT i CONCAT_WS
Jeśli pracujesz z T-SQL przez jakiś czas, wiesz, jak niezręczne jest radzenie sobie z Nullami, gdy musisz połączyć łańcuchy znaków. Jako przykład, rozważ dane o lokalizacji zapisane dla pracowników w tabeli hr. Employees:
SELECT empid, country, region, cityFROM HR.Employees;
to zapytanie generuje następujące wyjście:
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
zauważ, że dla niektórych pracowników część regionu jest nieistotna, a nieistotny region jest reprezentowany przez null. Załóżmy, że musisz połączyć części lokalizacji (kraj, region i miasto), używając przecinka jako separatora, ale ignorując regiony NULL. Gdy region jest istotny, chcesz, aby wynik miał formę <coutry>,<region>,<city>, a gdy region jest nieistotny, chcesz, aby wynik miał formę <country>,<city>. Zwykle łączenie czegoś z NULL daje wynik NULL. Możesz zmienić to zachowanie, wyłączając opcję sesji CONCAT_NULL_YIELDS_NULL, ale nie polecam włączania niestandardowego zachowania.
Jeśli nie wiesz o istnieniu funkcji CONCAT i CONCAT_WS, prawdopodobnie użyłbyś ISNULL lub COALESCE do zastąpienia NULL pustym ciągiem, tak jak:
SELECT empid, country + ISNULL(',' + region, '') + ',' + city AS locationFROM HR.Employees;
to wynik tego zapytania:
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 wprowadził funkcję concat. Ta funkcja przyjmuje listę wejść ciągów znaków i łączy je, a robiąc to, ignoruje wartości null. Dzięki CONCAT możesz uprościć rozwiązanie w następujący sposób:
SELECT empid, CONCAT(country, ',' + region, ',', city) AS locationFROM HR.Employees;
nadal musisz jawnie określić separatory jako część wejść funkcji. Aby uczynić nasze życie jeszcze łatwiejszym, SQL Server 2017 wprowadził podobną funkcję o nazwie CONCAT_WS, gdzie zaczynasz od wskazania separatora, a następnie elementów, które chcesz połączyć. Dzięki tej funkcji rozwiązanie jest dodatkowo uproszczone w ten sposób:
SELECT empid, CONCAT_WS(',', country, region, city) AS locationFROM HR.Employees;
następnym krokiem jest oczywiście mindreading. 1 kwietnia 2020 r. Microsoft planuje udostępnić CONCAT_MR. funkcja zaakceptuje puste dane wejściowe i automatycznie zorientuje się, które elementy chcesz połączyć, czytając w myślach. Zapytanie będzie wtedy wyglądać następująco:
SELECT empid, CONCAT_MR() AS locationFROM HR.Employees;
LOG ma drugi parametr
podobny do funkcji EOMONTH, Wiele osób nie zdaje sobie sprawy, że zaczynając już od SQL Server 2012, funkcja log obsługuje drugi parametr, który pozwala wskazać podstawę logarytmu. Wcześniej T-SQL obsługiwał funkcję LOG (input), która Zwraca logarytm naturalny wejścia(używając stałej e jako bazy), oraz LOG10 (input), która używa 10 jako bazy.
nie wiedząc o istnieniu drugiego parametru funkcji LOG, kiedy ludzie chcieli obliczyć Logb(x), gdzie b jest bazą inną niż e i 10, często robili to na dłuższą metę. Możesz polegać na następującym równaniu:
jako przykład, aby obliczyć Log2 (8), polegasz na następującym równaniu:
przetłumaczone na T-SQL, stosujesz następujące obliczenia:
DECLARE @x AS FLOAT = 8, @b AS INT = 2;SELECT LOG(@x) / LOG(@b);
gdy uświadomisz sobie, że LOG obsługuje drugi parametr, w którym wskazujesz bazę, obliczenie staje się po prostu:
jeśli pracujesz z T-SQL od jakiegoś czasu, prawdopodobnie miałeś wiele okazji do pracy z kursorami. Jak wiesz, podczas pracy z kursorem zwykle wykonujesz następujące czynności:
- Zadeklaruj kursor
- Otwórz kursor
- Iteruj za pomocą rekordów kursora
- Zamknij kursor
- Dealokuj kursor
jako przykład załóżmy, że musisz wykonać pewne zadanie dla bazy danych w swojej instancji. Używając kursora, zwykle używasz kodu podobnego do następującego:
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;
polecenie Zamknij zwalnia bieżący zestaw wyników i zwalnia blokady. Polecenie DEALOKACJA usuwa odniesienie do kursora, a gdy ostatnie odniesienie jest dealokowane, uwalnia struktury danych zawierające kursor. Jeśli spróbujesz uruchomić powyższy kod dwa razy bez polecenia zamknij i DEALOKUJ, pojawi się następujący błąd:
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.
upewnij się, że uruchomiłeś polecenia zamknij i DEALOKUJ przed kontynuowaniem.
Wiele osób nie zdaje sobie sprawy, że kiedy muszą pracować z kursorem tylko w jednej partii, co jest najczęstszym przypadkiem, zamiast używać zwykłego kursora można pracować ze zmienną kursora. Jak każda zmienna, zakres zmiennej kursora jest tylko zbiorem, w którym została zadeklarowana. Oznacza to, że po zakończeniu partii wszystkie zmienne wygasają. Korzystając ze zmiennej kursora, po zakończeniu wsadu, SQL Server zamyka i dealokuje go automatycznie, oszczędzając potrzebę jawnego uruchomienia polecenia zamknij i DEALOKUJ.
oto poprawiony kod za pomocą zmiennej kursora tym razem:
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;
możesz wykonać go wiele razy i zauważyć, że tym razem nie otrzymasz żadnych błędów. Jest po prostu czystszy i nie musisz się martwić o utrzymanie zasobów kursora, jeśli zapomniałeś zamknąć i dealokować kursor.
MERGE with OUTPUT
od momentu powstania klauzuli OUTPUT dla instrukcji modyfikacji w SQL Server 2005, okazało się to bardzo praktycznym narzędziem, gdy chcemy zwracać dane ze zmodyfikowanych wierszy. Użytkownicy regularnie korzystają z tej funkcji do celów takich jak Archiwizacja, audyt i wiele innych przypadków użycia. Jedną z irytujących rzeczy w tej funkcji jest to, że jeśli używasz jej z instrukcjami INSERT, możesz zwracać dane tylko z wstawionych wierszy, poprzedzając kolumny wyjściowe wstawionymi. Nie masz dostępu do kolumn tabeli źródłowej, mimo że czasami musisz zwracać kolumny ze źródła obok kolumn z miejsca docelowego.
jako przykład rozważ tabele T1 i T2, które tworzysz i wypełniasz, uruchamiając następujący kod:
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');
zauważ, że właściwość identity jest używana do generowania kluczy w obu tabelach.
Załóżmy, że musisz skopiować kilka wierszy z T1 do T2; powiedzmy te, w których keycol % 2 = 1. Chcesz użyć klauzuli OUTPUT do zwrócenia nowo wygenerowanych kluczy w T2, ale chcesz również zwrócić obok tych kluczy odpowiednie klucze źródłowe z T1. Intuicyjne oczekiwanie polega na użyciu następującego polecenia 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;
Niestety, jak wspomniano, klauzula OUTPUT nie pozwala na odwoływanie się do kolumn z tabeli źródłowej, więc pojawia się następujący błąd:
wieloczęściowy identyfikator „T1.keycol ” nie może być związany.
Wiele osób nie zdaje sobie sprawy, że to ograniczenie Nie dotyczy instrukcji MERGE. Mimo, że jest to trochę niezręczne, możesz przekonwertować instrukcję INSERT na instrukcję MERGE, ale aby to zrobić, potrzebujesz predykatu MERGE, aby zawsze był fałszywy. Spowoduje to aktywację klauzuli WHEN NOT MATCHED i zastosowanie tam jedynej obsługiwanej akcji Wstaw. Możesz użyć fałszywego warunku, takiego jak 1 = 2. Oto kompletny przekonwertowany Kod:
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;
tym razem kod działa pomyślnie, tworząc następujące wyjście:
T1_keycol T2_keycol----------- -----------1 13 25 3