mijn goede vriend Aaron Bertrand inspireerde me om dit artikel te schrijven. Hij herinnerde me eraan hoe we soms dingen voor lief nemen als ze voor de hand liggen voor ons en niet altijd de moeite nemen om het volledige verhaal achter hen te controleren. De relevantie voor T-SQL is dat we er soms van uitgaan dat we alles weten wat er te weten valt over bepaalde functies van T-SQL, en dat we niet altijd de moeite nemen om de documentatie te controleren om te zien of er meer aan de hand is. In dit artikel bespreek ik een aantal functies van T-SQL die ofwel vaak volledig over het hoofd worden gezien, of die parameters of mogelijkheden ondersteunen die vaak over het hoofd worden gezien. Als je zelf voorbeelden hebt van T-SQL edelstenen die vaak over het hoofd worden gezien, deel die dan in de commentaren sectie van dit artikel.
voordat u begint met het lezen van dit artikel, vraag uzelf af wat u weet over de volgende functies van T-SQL: EOMONTH, TRANSLATE, TRIM, CONCAT en CONCAT_WS, LOG, cursor variabelen en MERGE with OUTPUT.
in mijn voorbeelden gebruik ik een voorbeelddatabase genaamd TSQLV5. Je kunt hier het script vinden dat deze database creëert en bevolkt, en het ER diagram hier.
EOMONTH heeft een tweede parameter
De functie EOMONTH werd geïntroduceerd in SQL Server 2012. Veel mensen denken dat het slechts één parameter ondersteunt die een invoerdatum bevat, en dat het gewoon de einddatum van de maand retourneert die overeenkomt met de invoerdatum.
overweeg een iets meer verfijnde behoefte om het einde van de vorige maand te berekenen. Bijvoorbeeld, stel dat je nodig hebt om de verkoop te vragen.Orders tabel, en retour bestellingen die werden geplaatst aan het einde van de vorige maand.
Eén manier om dit te bereiken is het toepassen van de LAATSTE functie te SYSDATETIME te krijgen van de einde-van-de-maand-date van de huidige maand, en vervolgens het toepassen van de functie DATEADD voor het aftrekken van een maand, te rekenen vanaf het resultaat, zoals:
USE TSQLV5; SELECT orderid, orderdateFROM Sales.OrdersWHERE orderdate = EOMONTH(DATEADD(month, -1, SYSDATETIME()));
Merk op dat als u daadwerkelijk uitvoeren van deze query in de TSQLV5 voorbeeld-database krijgt u een leeg resultaat sinds de laatste bestelling datum opgenomen in de tabel 6 Mei 2019. Echter, als de tabel had orders met een orderdatum die valt op de laatste dag van de vorige maand, de query zou hebben geretourneerd die.
wat veel mensen zich niet realiseren is dat EOMONTH een tweede parameter ondersteunt waarin je aangeeft hoeveel maanden je moet optellen of aftrekken. Hier is de syntaxis van de functie:
EOMONTH ( start_date )
onze taak kan gemakkelijker en natuurlijk worden bereikt door simpelweg -1 op te geven als de tweede parameter van de functie, zoals zo:
SELECT orderid, orderdateFROM Sales.OrdersWHERE orderdate = EOMONTH(SYSDATETIME(), -1);
vertalen is soms eenvoudiger dan vervang
veel mensen zijn bekend met de functie vervangen en hoe het werkt. U gebruikt het als u alle exemplaren van een substring wilt vervangen door een andere in een invoerstring. Soms echter, wanneer u meerdere vervangingen hebt die u moet toepassen, is het gebruik van vervangen een beetje lastig en resulteert in ingewikkelde uitdrukkingen.
als voorbeeld, stel dat u een invoerstring @s krijgt die een nummer met Spaanse opmaak bevat. In Spanje gebruiken ze een punt als scheidingsteken voor groepen van duizenden, en een komma als decimaal scheidingsteken. U moet de invoer converteren naar de Amerikaanse opmaak, waarbij een komma wordt gebruikt als het scheidingsteken voor groepen van duizenden en een punt als het decimale scheidingsteken.
met één aanroep naar de functie vervangen kunt u alleen alle exemplaren van een teken of substring vervangen door een andere. Om twee vervangingen toe te passen (perioden aan komma ’s en komma’ s aan periodes) moet u functieaanroepen nestelen. Het lastige is dat als je eenmaal vervangen gebruikt om perioden in komma ’s te veranderen, en dan een tweede keer tegen het resultaat om komma’ s in punten te veranderen, je eindigt met alleen punten. Probeer het:
DECLARE @s AS VARCHAR(20) = '123.456.789,00'; SELECT REPLACE(REPLACE(@s, '.', ','), ',', '.');
u krijgt de volgende uitvoer:
123.456.789.00
Als u de functie vervangen wilt gebruiken, hebt u drie functieaanroepen nodig. Een om perioden te vervangen door een neutraal karakter waarvan je weet dat het normaal niet in de gegevens kan verschijnen (zeg, ~). Een ander tegen het resultaat om alle komma ‘ s te vervangen door punten. Een ander tegen het resultaat om alle exemplaren van het tijdelijke karakter (~ in ons voorbeeld) te vervangen door komma ‘ s. Hier is de volledige expressie:
DECLARE @s AS VARCHAR(20) = '123.456.789,00';SELECT REPLACE(REPLACE(REPLACE(@s, '.', '~'), ',', '.'), '~', ',');
Deze keer krijgt u de juiste uitvoer:
123,456,789.00
het is een beetje te doen, maar het resulteert in een lange en ingewikkelde expressie. Wat als je meer vervangers had om toe te passen?
veel mensen zijn zich er niet van bewust dat SQL Server 2017 een nieuwe functie genaamd TRANSLATE introduceerde die dergelijke vervangingen veel vereenvoudigt. Hier is de syntaxis van de functie:
TRANSLATE ( inputString, characters, translations )
de tweede invoer (karakters) is een tekenreeks met de lijst van de individuele karakters die u wilt vervangen, en de derde invoer (Vertalingen) is een tekenreeks met de lijst van de corresponderende karakters waarmee u de bronkarakters wilt vervangen. Dit betekent natuurlijk dat de tweede en derde parameters hetzelfde aantal karakters moeten hebben. Wat belangrijk is aan de functie is dat het geen afzonderlijke passen doet voor elk van de vervangingen. Als dat zo was, zou het mogelijk hebben geresulteerd in dezelfde bug als in het eerste voorbeeld dat ik liet zien met behulp van de twee oproepen naar de functie vervangen. Daarom wordt het afhandelen van onze taak een no-brainer:
DECLARE @s AS VARCHAR(20) = '123.456.789,00';SELECT TRANSLATE(@s, '.,', ',.');
deze code genereert de gewenste uitvoer:
123,456,789.00
dat is vrij netjes!
TRIM is meer dan LTRIM (RTRIM ())
SQL Server 2017 introduceerde ondersteuning voor de functie TRIM. Veel mensen, waaronder ikzelf, gaan er in eerste instantie van uit dat het niet meer is dan een eenvoudige snelkoppeling naar LTRIM(RTRIM(input)). Echter, als je de documentatie controleert, realiseer je je dat het eigenlijk krachtiger is dan dat.
voordat ik in de details ga, overweeg dan de volgende taak: gegeven een invoerstring @s, Verwijder voor-en achterliggende slashes (achterwaarts en vooruit). Stel bijvoorbeeld dat @s de volgende tekenreeks bevat:
//\\ remove leading and trailing backward (\) and forward (/) slashes \\//
de gewenste uitvoer is:
remove leading and trailing backward (\) and forward (/) slashes
merk op dat de uitvoer de voor-en achterspaties moet behouden.
Als je niet weet van TRIM de volledige mogelijkheden, hier is een manier waarop je zou kunnen hebben de taak opgelost:
DECLARE @s AS VARCHAR(100) = '//\\ remove leading and trailing backward (\) and forward (/) slashes \\//'; SELECT TRANSLATE(TRIM(TRANSLATE(TRIM(TRANSLATE(@s, ' /', '~ ')), ' \', '^ ')), ' ^~', '\/ ') AS outputstring;
De oplossing begint door gebruik te VERTALEN naar de plaats van alle ruimten met een neutraal karakter (~) en slashes met spaties, vervolgens met behulp van TRIM trim spaties voor en na van het resultaat. Deze stap trimt in wezen voor-en achterwaartse schuine strepen, tijdelijk met behulp van ~ in plaats van originele spaties. Hier is het resultaat van deze stap:
\\~remove~leading~and~trailing~backward~(\)~and~forward~( )~slashes~\\
de tweede stap gebruikt dan TRANSLATE om alle spaties te vervangen door een ander neutraal teken (^) en achterwaartse schuine strepen met spaties, waarna TRIM wordt gebruikt om voor-en achterspaties van het resultaat te trimmen. Deze stap trimt in wezen voor-en achterwaartse slashes, tijdelijk met behulp van ^ in plaats van tussenliggende spaties. Hier is het resultaat van deze stap:
~remove~leading~and~trailing~backward~( )~and~forward~(^)~slashes~
de laatste stap gebruikt TRANSLATE om spaties te vervangen door achterwaartse slashes, ^ met voorwaartse slashes en ~ met spaties, waardoor de gewenste uitvoer wordt gegenereerd:
remove leading and trailing backward (\) and forward (/) slashes
probeer deze taak op te lossen met een pre-SQL Server 2017 compatibele oplossing waar u geen TRIM en TRANSLATE kunt gebruiken.
terug naar SQL Server 2017 en hoger, als u de moeite had genomen om de documentatie te controleren, zou u hebben ontdekt dat TRIM geavanceerder is dan wat u aanvankelijk dacht. Hier is de syntaxis van de functie:
TRIM ( string )
met de optionele karakters van part kunt u een of meer karakters opgeven die u wilt knippen vanaf het begin en het einde van de invoerstring. In ons geval hoeft u alleen ‘/\’ als dit deel op te geven, zoals:
DECLARE @s AS VARCHAR(100) = '//\\ remove leading and trailing backward (\) and forward (/) slashes \\//'; SELECT TRIM( '/\' FROM @s) AS outputstring;
dat is een aanzienlijke verbetering ten opzichte van de vorige oplossing!
CONCAT en CONCAT_WS
Als u al een tijdje met T-SQL werkt, weet u hoe lastig het is om met NULLs om te gaan wanneer u strings moet samenvoegen. Neem bijvoorbeeld de locatiegegevens voor werknemers in de HR.Employees tabel:
SELECT empid, country, region, cityFROM HR.Employees;
deze query genereert de volgende output:
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
merk op dat Voor sommige werknemers het deel van de regio irrelevant is en een irrelevante regio wordt weergegeven door een null. Stel dat je de locatiedelen (land, regio en stad) moet samenvoegen, met behulp van een komma als scheidingsteken, maar het negeren van NULL regio ‘ s. Als de regio relevant is, Wilt u dat het resultaat de vorm <coutry>,<region>,<city> heeft en als de regio niet relevant is, Wilt u dat het resultaat de vorm <country>,<city>heeft. Normaal gesproken levert het samenvoegen van iets met een NULL een NULL resultaat op. Je kunt dit gedrag veranderen door de CONCAT_NULL_YIELDS_NULL sessie optie uit te schakelen, maar ik zou niet aanraden om niet-standaard gedrag aan te zetten.
Als u wist niet van het bestaan van de CONCAT en CONCAT_WS functies, u zou waarschijnlijk hebben gebruikt ISNULL of SAMENGESMOLTEN te vervangen door een NULL-met een lege tekenreeks, zoals:
SELECT empid, country + ISNULL(',' + region, '') + ',' + city AS locationFROM HR.Employees;
Hier is de uitvoer van deze query:
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 introduceerde de functie CONCAT. Deze functie accepteert een lijst met ingangen van tekenreeksen en voegt ze samen, en terwijl dit gebeurt, negeert het NULLs. Dus met CONCAT kun je de oplossing zo vereenvoudigen:
SELECT empid, CONCAT(country, ',' + region, ',', city) AS locationFROM HR.Employees;
toch moet u de scheidingstekens expliciet opgeven als onderdeel van de ingangen van de functie. Om ons leven nog gemakkelijker te maken, introduceerde SQL Server 2017 een soortgelijke functie genaamd CONCAT_WS, waarbij je begint met het aangeven van het scheidingsteken, gevolgd door de items die je wilt samenvoegen. Met deze functie wordt de oplossing zo verder vereenvoudigd:
SELECT empid, CONCAT_WS(',', country, region, city) AS locationFROM HR.Employees;
de volgende stap is natuurlijk mindreading. Op 1 April 2020 is Microsoft van plan om CONCAT_MR vrij te geven. de functie zal een lege invoer accepteren en automatisch uitzoeken welke elementen u wilt samenvoegen door uw gedachten te lezen. De zoekopdracht ziet er dan zo uit:
SELECT empid, CONCAT_MR() AS locationFROM HR.Employees;
LOG heeft een tweede parameter
vergelijkbaar met de eomonth-functie, veel mensen realiseren zich niet dat het al starten met SQL Server 2012, ondersteunt de logfunctie een tweede parameter die u toelaat om de basis van de logaritme aan te geven. Daarvoor ondersteunde T – SQL de functie LOG(input) die de natuurlijke logaritme van de input retourneert (met behulp van de constante e als basis), en LOG10(input) die 10 als basis gebruikt.
zich niet bewust van het bestaan van de tweede parameter voor de logfunctie, toen mensen Logb(x) wilden berekenen, waar b een andere basis is dan e en 10, deden ze het vaak op de lange weg. U kunt vertrouwen op de volgende vergelijking:
als voorbeeld, om Log2(8) te berekenen, vertrouwt u op de volgende vergelijking:
vertaald naar T-SQL, past u de volgende berekening toe:
DECLARE @x AS FLOAT = 8, @b AS INT = 2;SELECT LOG(@x) / LOG(@b);
Zodra je je realiseert dat LOG ondersteunt een tweede parameter waar u aan de basis van de berekening wordt gewoon:
DECLARE @x AS FLOAT = 8, @b AS INT = 2;SELECT LOG(@x, @b);
Cursor – > variabele
Als je hebt gewerkt met T-SQL voor een tijdje, je had waarschijnlijk veel kansen om te werken met cursors. Zoals u weet, gebruikt u bij het werken met een cursor meestal de volgende stappen:
- Declare the cursor
- Open de cursor
- Itereer door de cursor records
- sluit de cursor
- Deallocate the cursor
als voorbeeld, stel dat u een taak per database in uw instantie moet uitvoeren. Met behulp van een cursor zou u normaal gesproken code gebruiken die vergelijkbaar is met het volgende:
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;
het sluiten commando geeft de huidige resultaatset vrij en maakt vergrendelingen vrij. De opdracht DEALLOCATE verwijdert een cursor-verwijzing en wanneer de laatste referentie wordt deallocated, bevrijdt de datastructuren die de cursor bevatten. Als u de bovenstaande code twee keer probeert uit te voeren zonder de commando ‘ s sluiten en DEALLOCATE, krijgt u de volgende fout:
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.
zorg ervoor dat u de commando ‘ s sluiten en DEALLOCATE uitvoert voordat u verder gaat.
veel mensen realiseren zich niet dat wanneer ze met een cursor in slechts één batch moeten werken, wat het meest voorkomende geval is, je in plaats van met een gewone cursor kunt werken met een cursor-variabele. Zoals elke variabele, is de reikwijdte van een cursor variabele alleen de batch waar het werd gedeclareerd. Dit betekent dat zodra een batch is voltooid, alle variabelen verlopen. Met behulp van een cursor variabele, zodra een batch is voltooid, SQL Server sluit en deallocates het automatisch, bespaart u de noodzaak om de opdracht sluiten en DEALLOCATE expliciet uit te voeren.
Hier is de herziene code met behulp van een cursor variabele deze keer:
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;
voer het meerdere keren uit en merk op dat u deze keer geen fouten krijgt. Het is gewoon schoner, en je hoeft je geen zorgen te maken over het houden van cursor bronnen als je vergeten bent om te sluiten en deallocate de cursor.
samenvoegen met OUTPUT
sinds het begin van de output-clausule voor modificatieverklaringen in SQL Server 2005, bleek het een zeer praktisch hulpmiddel te zijn wanneer u gegevens uit gewijzigde rijen wilde retourneren. Mensen gebruiken deze functie regelmatig voor doeleinden zoals archivering, auditing en vele andere use cases. Een van de vervelende dingen over deze functie is echter dat als u het gebruikt met INSERT statements, u alleen gegevens van de ingevoegde rijen mag retourneren, waarbij u de uitvoerkolommen vooraf invoert met ingevoegd. U hebt geen toegang tot de kolommen van de brontabel, hoewel u soms kolommen van de bron naast kolommen van het doel moet retourneren.
neem als voorbeeld de tabellen T1 en T2, die u maakt en invult door de volgende code uit te voeren:
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');
merk op dat een identiteitseigenschap wordt gebruikt om de sleutels in beide tabellen te genereren.
stel dat u enkele rijen van T1 naar T2 moet kopiëren; bijvoorbeeld de rijen waar keycol % 2 = 1. U wilt de output-clausule gebruiken om de nieuw gegenereerde sleutels in T2 terug te geven, maar u wilt ook naast die sleutels de respectieve bronsleutels van T1 retourneren. De intuïtieve verwachting is om het volgende INSERT statement te gebruiken:
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;
helaas staat de UITVOERCLAUSULE u niet toe om naar kolommen uit de brontabel te verwijzen, dus krijgt u de volgende fout:
de meerdelige identificatiecode “T1.keycol ” kon niet worden gebonden.
veel mensen realiseren zich niet dat vreemd genoeg deze beperking niet van toepassing is op het MERGE statement. Dus ook al is het een beetje onhandig, je kunt je INSERT statement converteren naar een MERGE statement, maar om dat te doen, moet het MERGE predicaat altijd onwaar zijn. Dit zal de wanneer niet overeenkomende clausule activeren en de enige ondersteunde actie invoegen daar toepassen. U kunt gebruik maken van een dummy valse voorwaarde zoals 1 = 2. Hier is de volledige geconverteerde 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;
Deze keer wordt de code succesvol uitgevoerd en levert de volgende uitvoer:
T1_keycol T2_keycol----------- -----------1 13 25 3