Mein guter Freund Aaron Bertrand hat mich zu diesem Artikel inspiriert. Er erinnerte mich daran, wie wir manchmal Dinge für selbstverständlich halten, wenn sie uns offensichtlich erscheinen und uns nicht immer die Mühe machen, die ganze Geschichte dahinter zu überprüfen. Die Relevanz für T-SQL besteht darin, dass wir manchmal davon ausgehen, dass wir alles wissen, was es über bestimmte T-SQL-Funktionen zu wissen gibt, und uns nicht immer die Mühe machen, die Dokumentation zu überprüfen, um festzustellen, ob mehr dahinter steckt. In diesem Artikel behandle ich eine Reihe von T-SQL-Funktionen, die entweder oft völlig übersehen werden oder Parameter oder Funktionen unterstützen, die oft übersehen werden. Wenn Sie eigene Beispiele für T-SQL-Edelsteine haben, die oft übersehen werden, teilen Sie diese bitte im Kommentarbereich dieses Artikels mit.
Bevor Sie diesen Artikel lesen, fragen Sie sich, was Sie über die folgenden T-SQL-Funktionen wissen: EOMONTH, TRANSLATE, TRIM, CONCAT und CONCAT_WS, LOG, Cursorvariablen und MERGE with OUTPUT.
In meinen Beispielen verwende ich eine Beispieldatenbank namens TSQLV5. Das Skript, das diese Datenbank erstellt und füllt, finden Sie hier und das ER-Diagramm hier.
EOMONTH hat einen zweiten Parameter
Die Funktion EOMONTH wurde in SQL Server 2012 eingeführt. Viele Leute denken, dass es nur einen Parameter unterstützt, der ein Eingabedatum enthält, und dass es einfach das Monatsendedatum zurückgibt, das dem Eingabedatum entspricht.
Betrachten Sie eine etwas anspruchsvollere Notwendigkeit, das Ende des Vormonats zu berechnen. Angenommen, Sie müssen die Verkäufe abfragen.Tabelle Bestellungen und Retourenbestellungen, die am Ende des Vormonats aufgegeben wurden.
Eine Möglichkeit, dies zu erreichen, besteht darin, die Funktion EOMONTH auf SYSDATETIME anzuwenden, um das Datum des Monatsendes des aktuellen Monats abzurufen, und dann die Funktion DATEADD anzuwenden, um einen Monat vom Ergebnis zu subtrahieren:
USE TSQLV5; SELECT orderid, orderdateFROM Sales.OrdersWHERE orderdate = EOMONTH(DATEADD(month, -1, SYSDATETIME()));
Wenn Sie diese Abfrage tatsächlich in der TSQLV5-Beispieldatenbank ausführen, erhalten Sie ein leeres Ergebnis, da das letzte in der Tabelle aufgezeichnete Bestelldatum der 6. Mai 2019 ist. Wenn die Tabelle jedoch Bestellungen mit einem Bestelldatum hätte, das auf den letzten Tag des Vormonats fällt, hätte die Abfrage diese zurückgegeben.
Was viele Leute nicht wissen, ist, dass EOMONTH einen zweiten Parameter unterstützt, in dem Sie angeben, wie viele Monate addiert oder subtrahiert werden sollen. Hier ist die Syntax der Funktion:
EOMONTH ( start_date )
Unsere Aufgabe kann einfacher und natürlicher erreicht werden, indem einfach -1 als zweiter Parameter für die Funktion angegeben wird:
SELECT orderid, orderdateFROM Sales.OrdersWHERE orderdate = EOMONTH(SYSDATETIME(), -1);
ÜBERSETZEN ist manchmal einfacher als ERSETZEN
Viele Menschen kennen die Ersetzungsfunktion und ihre Funktionsweise. Sie verwenden es, wenn Sie alle Vorkommen einer Teilzeichenfolge durch eine andere in einer Eingabezeichenfolge ersetzen möchten. Manchmal, wenn Sie mehrere Ersetzungen haben, die Sie anwenden müssen, ist die Verwendung von REPLACE etwas schwierig und führt zu verschachtelten Ausdrücken.
Angenommen, Sie erhalten eine Eingabezeichenfolge @s, die eine Zahl mit spanischer Formatierung enthält. In Spanien verwenden sie einen Punkt als Trennzeichen für Tausendergruppen und ein Komma als Dezimaltrennzeichen. Sie müssen die Eingabe in US-Formatierung konvertieren, wobei ein Komma als Trennzeichen für Tausendergruppen und ein Punkt als Dezimaltrennzeichen verwendet wird.
Mit einem Aufruf der REPLACE-Funktion können Sie nur alle Vorkommen eines Zeichens oder einer Teilzeichenfolge durch ein anderes ersetzen. Um zwei Ersetzungen (Punkte auf Kommas und Kommas auf Punkte) anzuwenden, müssen Sie Funktionsaufrufe verschachteln. Der schwierige Teil ist, dass Sie, wenn Sie REPLACE einmal verwenden, um Punkte in Kommas zu ändern, und dann ein zweites Mal gegen das Ergebnis, um Kommas in Punkte zu ändern, nur Punkte erhalten. Probieren Sie es aus:
DECLARE @s AS VARCHAR(20) = '123.456.789,00'; SELECT REPLACE(REPLACE(@s, '.', ','), ',', '.');
Sie erhalten die folgende Ausgabe:
123.456.789.00
Wenn Sie bei der Verwendung der Ersetzungsfunktion bleiben möchten, können Sie benötigen Sie drei Funktionsaufrufe. Eine, um Punkte durch ein neutrales Zeichen zu ersetzen, von dem Sie wissen, dass es normalerweise nicht in den Daten vorkommen kann (z. B. ~). Ein anderer gegen das Ergebnis, um alle Kommas durch Punkte zu ersetzen. Ein weiteres gegen das Ergebnis, um alle Vorkommen des temporären Zeichens (in unserem Beispiel ~) durch Kommas zu ersetzen. Hier ist der vollständige Ausdruck:
DECLARE @s AS VARCHAR(20) = '123.456.789,00';SELECT REPLACE(REPLACE(REPLACE(@s, '.', '~'), ',', '.'), '~', ',');
Dieses Mal erhalten Sie die richtige Ausgabe:
123,456,789.00
Es ist irgendwie machbar, aber es führt zu einem langen und gewundenen Ausdruck. Was wäre, wenn Sie mehr Ersatz hätten?
Vielen Menschen ist nicht bewusst, dass SQL Server 2017 eine neue Funktion namens TRANSLATE eingeführt hat, die solche Ersetzungen erheblich vereinfacht. Hier ist die Syntax der Funktion:
TRANSLATE ( inputString, characters, translations )
Die zweite Eingabe (Zeichen) ist eine Zeichenfolge mit der Liste der einzelnen Zeichen, die Sie ersetzen möchten, und die dritte Eingabe (Übersetzungen) ist eine Zeichenfolge mit der Liste der entsprechenden Zeichen, durch die Sie die Quellzeichen ersetzen möchten. Dies bedeutet natürlich, dass der zweite und dritte Parameter die gleiche Anzahl von Zeichen haben müssen. Was an der Funktion wichtig ist, ist, dass sie nicht für jeden Ersatz separate Durchgänge ausführt. Wenn dies der Fall wäre, hätte dies möglicherweise zu demselben Fehler geführt wie im ersten Beispiel, das ich mit den beiden Aufrufen der Ersetzungsfunktion gezeigt habe. Folglich wird die Handhabung unserer Aufgabe zum Kinderspiel:
DECLARE @s AS VARCHAR(20) = '123.456.789,00';SELECT TRANSLATE(@s, '.,', ',.');
123,456,789.00
Das ist ziemlich ordentlich!
TRIM ist mehr als LTRIM(RTRIM())
SQL Server 2017 hat Unterstützung für die Funktion TRIM eingeführt. Viele Leute, mich eingeschlossen, zunächst nur davon ausgehen, dass es nicht mehr als eine einfache Verknüpfung zu LTRIM(RTRIM(input)). Wenn Sie jedoch die Dokumentation überprüfen, stellen Sie fest, dass sie tatsächlich leistungsfähiger ist.
Bevor ich auf die Details eingehe, betrachten Sie die folgende Aufgabe: Entfernen Sie bei einer Eingabezeichenfolge @s führende und nachfolgende Schrägstriche (rückwärts und vorwärts). Angenommen, @s enthält die folgende Zeichenfolge:
//\\ remove leading and trailing backward (\) and forward (/) slashes \\//
Die gewünschte Ausgabe ist:
remove leading and trailing backward (\) and forward (/) slashes
Beachten Sie, dass die Ausgabe die führenden und nachfolgenden Leerzeichen beibehalten sollte.
Wenn Sie nicht über die vollen Funktionen von TRIM Bescheid wussten, haben Sie die Aufgabe möglicherweise folgendermaßen gelöst:
DECLARE @s AS VARCHAR(100) = '//\\ remove leading and trailing backward (\) and forward (/) slashes \\//'; SELECT TRANSLATE(TRIM(TRANSLATE(TRIM(TRANSLATE(@s, ' /', '~ ')), ' \', '^ ')), ' ^~', '\/ ') AS outputstring;
Die Lösung beginnt mit TRANSLATE , um alle Leerzeichen durch ein neutrales Zeichen (~) und Schrägstriche durch Leerzeichen zu ersetzen , verwenden Sie dann TRIM , um führende und nachfolgende Leerzeichen aus dem Ergebnis zu trimmen. Dieser Schritt schneidet im Wesentlichen führende und nachfolgende Schrägstriche ab und verwendet vorübergehend ~ anstelle von ursprünglichen Leerzeichen. Hier ist das Ergebnis dieses Schritts:
\\~remove~leading~and~trailing~backward~(\)~and~forward~( )~slashes~\\
Der zweite Schritt verwendet dann TRANSLATE , um alle Leerzeichen durch ein anderes neutrales Zeichen (^) und umgekehrte Schrägstriche durch Leerzeichen zu ersetzen. Dieser Schritt schneidet im Wesentlichen führende und nachfolgende Schrägstriche ab und verwendet vorübergehend ^ anstelle von Zwischenräumen. Hier ist das Ergebnis dieses Schritts:
~remove~leading~and~trailing~backward~( )~and~forward~(^)~slashes~
Der letzte Schritt verwendet TRANSLATE, um Leerzeichen durch Schrägstriche, ^ durch Schrägstriche und ~ durch Leerzeichen zu ersetzen und die gewünschte Ausgabe zu generieren:
remove leading and trailing backward (\) and forward (/) slashes
Versuchen Sie, diese Aufgabe mit einer vor SQL Server 2017-kompatiblen Lösung zu lösen, bei der Sie TRIM und TRANSLATE nicht verwenden können.
Zurück zu SQL Server 2017 und höher, wenn Sie sich die Mühe gemacht hätten, die Dokumentation zu überprüfen, hätten Sie festgestellt, dass TRIM anspruchsvoller ist als das, was Sie ursprünglich dachten. Hier ist die Syntax der Funktion:
TRIM ( string )
Mit dem optionalen Teil characters FROM können Sie ein oder mehrere Zeichen angeben, die vom Anfang und Ende der Eingabezeichenfolge abgeschnitten werden sollen. In unserem Fall müssen Sie nur ‚/\‘ als diesen Teil angeben:
DECLARE @s AS VARCHAR(100) = '//\\ remove leading and trailing backward (\) and forward (/) slashes \\//'; SELECT TRIM( '/\' FROM @s) AS outputstring;
Das ist eine ziemlich deutliche Verbesserung gegenüber der vorherigen Lösung!
CONCAT und CONCAT_WS
Wenn Sie schon eine Weile mit T-SQL arbeiten, wissen Sie, wie umständlich es ist, mit NULLEN umzugehen, wenn Sie Zeichenfolgen verketten müssen. Betrachten Sie als Beispiel die Standortdaten, die für Mitarbeiter in der Tabelle HR.Employees aufgezeichnet wurden:
SELECT empid, country, region, cityFROM HR.Employees;
Diese Abfrage generiert die folgende Ausgabe:
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
Beachten Sie, dass für einige Mitarbeiter der Regionsteil irrelevant ist und eine irrelevante Region durch eine NULL dargestellt wird. Angenommen, Sie müssen die Standortteile (Land, Region und Stadt) verketten, indem Sie ein Komma als Trennzeichen verwenden, aber Nullregionen ignorieren. Wenn die Region relevant ist, soll das Ergebnis die Form <coutry>,<region>,<city> und wenn die Region irrelevant ist, soll das Ergebnis die Form <country>,<city> . Normalerweise führt die Verkettung von etwas mit einer NULL zu einem NULL-Ergebnis. Sie können dieses Verhalten ändern, indem Sie die Sitzungsoption CONCAT_NULL_YIELDS_NULL deaktivieren.
Wenn Sie nicht von der Existenz der Funktionen CONCAT und CONCAT_WS gewusst hätten, hätten Sie wahrscheinlich ISNULL oder COALESCE verwendet, um eine NULL durch eine leere Zeichenfolge zu ersetzen:
SELECT empid, country + ISNULL(',' + region, '') + ',' + city AS locationFROM HR.Employees;
Hier ist die Ausgabe dieser Abfrage:
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 hat die Funktion CONCAT eingeführt. Diese Funktion akzeptiert eine Liste von Zeichenfolgeneingaben und verkettet sie. Mit CONCAT können Sie die Lösung also folgendermaßen vereinfachen:
SELECT empid, CONCAT(country, ',' + region, ',', city) AS locationFROM HR.Employees;
Dennoch müssen Sie die Trennzeichen explizit als Teil der Funktionseingaben angeben. Um unser Leben noch einfacher zu machen, hat SQL Server 2017 eine ähnliche Funktion namens CONCAT_WS eingeführt, bei der Sie zunächst das Trennzeichen angeben, gefolgt von den Elementen, die Sie verketten möchten. Mit dieser Funktion wird die Lösung weiter vereinfacht:
SELECT empid, CONCAT_WS(',', country, region, city) AS locationFROM HR.Employees;
Der nächste Schritt ist natürlich Mindreading. Am 1. April 2020 plant Microsoft die Veröffentlichung von CONCAT_MR. Die Funktion akzeptiert eine leere Eingabe und ermittelt automatisch, welche Elemente verkettet werden sollen, indem Sie Ihre Gedanken liest. Die Abfrage wird dann so aussehen:
SELECT empid, CONCAT_MR() AS locationFROM HR.Employees;
LOG hat einen zweiten Parameter
Ähnlich wie bei der EOMONTH-Funktion ist vielen nicht klar, dass die LOG-Funktion bereits ab SQL Server 2012 einen zweiten Parameter unterstützt, mit dem Sie um die Basis des Logarithmus anzugeben. Zuvor unterstützte T-SQL die Funktion LOG(input), die den natürlichen Logarithmus der Eingabe zurückgibt (unter Verwendung der Konstanten e als Basis), und LOG10(input), die 10 als Basis verwendet.
Da sie sich der Existenz des zweiten Parameters für die LOG-Funktion nicht bewusst waren, wenn Leute Logb(x) berechnen wollten, wobei b eine andere Basis als e und 10 ist, haben sie es oft weit gemacht. Sie könnten sich auf die folgende Gleichung verlassen:
Um beispielsweise Log2(8) zu berechnen, verlassen Sie sich auf die folgende Gleichung:
Übersetzt in T-SQL wenden Sie die folgende:
DECLARE @x AS FLOAT = 8, @b AS INT = 2;SELECT LOG(@x) / LOG(@b);
Sobald Sie erkennen, dass LOG einen zweiten Parameter unterstützt, bei dem Sie die Basis angeben, wird die Berechnung einfach:
DECLARE @x AS FLOAT = 8, @b AS INT = 2;SELECT LOG(@x, @b);
Cursorvariable
Wenn Sie eine Weile mit T-SQL gearbeitet haben, hatten Sie wahrscheinlich viele Möglichkeiten, mit Cursorn zu arbeiten. Wie Sie wissen, führen Sie beim Arbeiten mit einem Cursor normalerweise die folgenden Schritte aus:
- Den Cursor deklarieren
- Den Cursor öffnen
- Durch die Cursordatensätze iterieren
- Den Cursor schließen
- Den Cursor freigeben
Angenommen, Sie müssen in Ihrer Instanz eine Aufgabe pro Datenbank ausführen. Mit einem Cursor würden Sie normalerweise Code ähnlich dem folgenden verwenden:
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;
Der Befehl CLOSE gibt die aktuelle Ergebnismenge frei und gibt Sperren frei. Der Befehl DEALLOCATE entfernt eine Cursorreferenz und gibt, wenn die letzte Referenz freigegeben wird, die Datenstrukturen frei, die den Cursor umfassen. Wenn Sie versuchen, den obigen Code zweimal ohne die Befehle CLOSE und DEALLOCATE auszuführen, wird die folgende Fehlermeldung angezeigt:
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.
Stellen Sie sicher, dass Sie die Befehle CLOSE und DEALLOCATE ausführen, bevor Sie fortfahren.
Vielen Menschen ist nicht klar, dass Sie, wenn sie mit einem Cursor in nur einem Stapel arbeiten müssen, was der häufigste Fall ist, anstelle eines normalen Cursors mit einer Cursorvariablen arbeiten können. Wie bei jeder Variablen ist der Gültigkeitsbereich einer Cursorvariablen nur der Bereich, in dem sie deklariert wurde. Dies bedeutet, dass alle Variablen ablaufen, sobald ein Stapel beendet ist. Verwenden einer Cursorvariablen, sobald ein Stapel beendet ist, schließt SQL Server ihn automatisch und gibt ihn frei, sodass Sie den Befehl CLOSE und DEALLOCATE nicht explizit ausführen müssen.
Hier ist der überarbeitete Code, der diesmal eine Cursorvariable verwendet:
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;
Fühlen Sie sich frei, es mehrmals auszuführen und beachten Sie, dass Sie diesmal keine Fehler erhalten. Es ist nur sauberer, und Sie müssen sich keine Sorgen machen, Cursor-Ressourcen zu behalten, wenn Sie vergessen haben, den Cursor zu schließen und freizugeben.
MERGE with OUTPUT
Seit der Einführung der OUTPUT-Klausel für Modification-Anweisungen in SQL Server 2005 erwies sie sich als sehr praktisches Werkzeug, wenn Sie Daten aus geänderten Zeilen zurückgeben wollten. Menschen nutzen diese Funktion regelmäßig für Zwecke wie Archivierung, Auditing und viele andere Anwendungsfälle. Eines der ärgerlichen Dinge an dieser Funktion ist jedoch, dass Sie bei Verwendung mit INSERT-Anweisungen nur Daten aus den eingefügten Zeilen zurückgeben dürfen, wobei den Ausgabespalten inserted vorangestellt ist. Sie haben keinen Zugriff auf die Spalten der Quelltabelle, obwohl Sie manchmal Spalten aus der Quelle neben Spalten aus dem Ziel zurückgeben müssen.
Betrachten Sie als Beispiel die Tabellen T1 und T2, die Sie erstellen und füllen, indem Sie den folgenden Code ausführen:
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');
Beachten Sie, dass eine identity-Eigenschaft verwendet wird, um die Schlüssel in beiden Tabellen zu generieren.
Angenommen, Sie müssen einige Zeilen von T1 nach T2 kopieren. sagen wir, diejenigen, bei denen keycol % 2 = 1 . Sie möchten die OUTPUT Klausel verwenden, um die neu generierten Schlüssel in T2 zurückzugeben, aber Sie möchten neben diesen Schlüsseln auch die entsprechenden Quellschlüssel von T1 zurückgeben. Die intuitive Erwartung besteht darin, die folgende INSERT-Anweisung zu verwenden:
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;
Leider erlaubt Ihnen die OUTPUT Klausel, wie bereits erwähnt, nicht, auf Spalten aus der Quelltabelle zu verweisen, so dass Sie den folgenden Fehler erhalten:
Die mehrteilige Kennung „T1.keycol“ konnte nicht gebunden werden.
Viele Leute wissen nicht, dass diese Einschränkung seltsamerweise nicht für die MERGE-Anweisung gilt. Obwohl es etwas umständlich ist, können Sie Ihre INSERT-Anweisung in eine MERGE-Anweisung konvertieren, aber dazu muss das MERGE-Prädikat immer false sein. Dadurch wird die WHEN NOT MATCHED-Klausel aktiviert und dort die einzige unterstützte EINFÜGEAKTION angewendet. Sie können eine falsche Dummy-Bedingung wie 1 = 2 verwenden. Hier ist der vollständige konvertierte Code:
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;
Diesmal läuft der Code erfolgreich und erzeugt die folgende Ausgabe:
T1_keycol T2_keycol----------- -----------1 13 25 3