min gode vän Aaron Bertrand inspirerade mig att skriva den här artikeln. Han påminde mig om hur vi ibland tar saker för givet när de verkar uppenbara för oss och inte alltid bry sig om att kontrollera hela historien bakom dem. Relevansen för T-SQL är att vi ibland antar att vi vet allt som finns att veta om vissa T-SQL-funktioner, och stör inte alltid att kontrollera dokumentationen för att se om det finns mer för dem. I den här artikeln täcker jag ett antal T-SQL-funktioner som antingen ofta helt förbises, eller som stöder parametrar eller funktioner som ofta förbises. Om du har exempel på din egen av T-SQL pärlor som ofta förbises, Vänligen dela dem i kommentarerna i denna artikel.

innan du börjar läsa den här artikeln, fråga dig själv vad du vet om följande T-SQL-funktioner: EOMONTH, TRANSLATE, TRIM, CONCAT och CONCAT_WS, LOG, cursor variables och MERGE with OUTPUT.

i mina exempel använder jag en provdatabas som heter TSQLV5. Du kan hitta skriptet som skapar och fyller denna databas här, och dess ER diagram här.

EOMONTH har en andra parameter

eomonth-funktionen introducerades i SQL Server 2012. Många tror att det bara stöder en parameter som håller ett inmatningsdatum och att det helt enkelt returnerar månadsdatumet som motsvarar inmatningsdatumet.

Tänk på ett något mer sofistikerat behov av att beräkna slutet av föregående månad. Antag till exempel att du måste fråga försäljningen.Ordertabell och returorder som placerades i slutet av föregående månad.

ett sätt att uppnå detta är att tillämpa EOMONTH-funktionen på SYSDATETIME för att få månadens slutdatum för den aktuella månaden och sedan tillämpa DATEADD-funktionen för att subtrahera en månad från resultatet, så här:

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

EOMONTH ( start_date )

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

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

123.456.789.00

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

den här gången får du rätt utgång:

123,456,789.00

det är ganska genomförbart, men det resulterar i ett långt och invecklat uttryck. Vad händer om du hade fler ersättare att ansöka?

många är inte medvetna om att SQL Server 2017 introducerade en ny funktion som heter TRANSLATE som förenklar sådana ersättningar mycket. Här är funktionens syntax:

TRANSLATE ( inputString, characters, translations )

den andra ingången (tecken) är en sträng med listan över de enskilda tecknen som du vill ersätta, och den tredje ingången (översättningar) är en sträng med listan över motsvarande tecken som du vill ersätta källtecknen med. Detta betyder naturligtvis att den andra och tredje parametrarna måste ha samma antal tecken. Vad som är viktigt med funktionen är att det inte gör separata pass för var och en av ersättningarna. Om det gjorde det skulle det potentiellt ha resulterat i samma fel som i det första exemplet som jag visade med de två samtalen till ersätt-funktionen. Följaktligen blir hanteringen av vår uppgift en no-brainer:

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

denna kod genererar önskad utgång:

123,456,789.00

det är ganska snyggt!

TRIM är mer än LTRIM(RTRIM())

SQL Server 2017 infört stöd för funktionen TRIM. Många människor, inklusive mig själv, antar initialt bara att det inte är mer än en enkel genväg till LTRIM(RTRIM(input)). Men om du kontrollerar dokumentationen inser du att den faktiskt är kraftfullare än så.

innan jag går in i detaljerna, överväga följande uppgift: ge en inmatningssträng @s, ta bort Ledande och efterföljande snedstreck (bakåt och framåt). Anta till exempel att @s innehåller följande sträng:

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

önskad utgång är:

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

Observera att utgången ska behålla de ledande och bakre utrymmena.

Om du inte kände till trims fulla kapacitet, är det här ett sätt du kanske har löst uppgiften:

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

lösningen börjar med att använda TRANSLATE för att ersätta alla mellanslag med ett neutralt tecken (~) och snedstreck framåt med mellanslag, använd sedan trim för att trimma inledande och efterföljande mellanslag från resultatet. Detta steg trimmar i huvudsak ledande och efterföljande snedstreck, tillfälligt med ~ istället för ursprungliga mellanslag. Här är resultatet av detta steg:

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

det andra steget använder sedan TRANSLATE för att ersätta alla mellanslag med ett annat neutralt tecken (^) och bakåt snedstreck med mellanslag, sedan använder TRIM för att trimma inledande och efterföljande mellanslag från resultatet. Detta steg trimmar i huvudsak ledande och bakre snedstreck, tillfälligt med ^ istället för mellanliggande utrymmen. Här är resultatet av detta steg:

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

det sista steget använder TRANSLATE för att ersätta mellanslag med bakåt snedstreck, ^ med framåt snedstreck och ~ med mellanslag, vilket genererar önskad utgång:

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

som en övning kan du försöka lösa den här uppgiften med en pre-SQL Server 2017-kompatibel lösning där du inte kan använda TRIM och TRANSLATE.

tillbaka till SQL Server 2017 och senare, om du störde att kontrollera dokumentationen, skulle du ha upptäckt att TRIM är mer sofistikerat än vad du trodde ursprungligen. Här är funktionens Syntax:

TRIM ( string )

de valfria tecknen från delen låter dig ange ett eller flera tecken som du vill klippa från början och slutet av inmatningssträngen. I vårt fall är allt du behöver göra att ange ’/ \ ’ som den här delen, så här:

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

det är en ganska signifikant förbättring jämfört med den tidigare lösningen!

CONCAT och CONCAT_WS

Om du har arbetat med T-SQL ett tag vet du hur besvärligt det är att hantera NULLs när du behöver sammanfoga strängar. Tänk exempel på platsdata som registrerats för anställda i HR.Employees-tabellen:

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

denna fråga genererar följande utdata:

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

Observera att för vissa anställda är regiondelen irrelevant och en irrelevant Region representeras av en null. Antag att du måste sammanfoga platsdelarna (land, region och stad), med ett komma som separator, men ignorera NULL-regioner. När regionen är relevant vill du att resultatet ska ha formen <coutry>,<region>,<city> och när regionen är irrelevant vill du att resultatet ska ha formen <country>,<city>. Normalt ger sammanslagning av något med ett NULL ett NULL-resultat. Du kan ändra detta beteende genom att stänga av sessionsalternativet CONCAT_NULL_YIELDS_NULL, men jag skulle inte rekommendera att aktivera icke-standardbeteende.

Om du inte visste om förekomsten av CONCAT-och CONCAT_WS-funktionerna, skulle du antagligen ha använt ISNULL eller COALESCE för att ersätta en NULL med en tom sträng, så här:

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

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

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

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

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

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

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;

stäng-kommandot släpper den aktuella resultatuppsättningen och frigör lås. Kommandot AVALLOKERA tar bort en markörreferens, och när den sista referensen avallokeras frigörs datastrukturerna som består av markören. Om du försöker köra ovanstående kod två gånger utan kommandona stäng och AVFÖRDELA, får du följande felmeddelande:

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.

se till att du kör kommandona stäng och AVFÖRDELA innan du fortsätter.

många människor inser inte att när de behöver arbeta med en markör i endast en sats, vilket är det vanligaste fallet, istället för att använda en vanlig markör kan du arbeta med en markörvariabel. Liksom alla variabler är omfattningen av en markörvariabel endast den sats där den deklarerades. Detta innebär att så snart en sats är klar löper alla variabler ut. Med hjälp av en markörvariabel, när en sats är klar, stängs SQL Server och avallokerar den automatiskt, vilket sparar behovet av att köra kommandot Stäng och AVALLOKERA explicit.

här är den reviderade koden med en markörvariabel den här gången:

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;

kör gärna det flera gånger och märka att den här gången får du inga fel. Det är bara renare, och du behöver inte oroa dig för att hålla markörresurser om du glömde att stänga och avfördela markören.

sammanfoga med OUTPUT

Sedan starten av output-klausulen för modifieringssatser i SQL Server 2005 visade det sig vara ett mycket praktiskt verktyg när du ville returnera data från modifierade rader. Människor använder den här funktionen regelbundet för ändamål som arkivering, revision och många andra användningsfall. En av de irriterande sakerna med den här funktionen är dock att om du använder den med INSERT-satser får du bara returnera data från de infogade raderna och prefixa utgångskolumnerna med infogad. Du har inte tillgång till källtabellens kolumner, även om du ibland behöver returnera kolumner från källan tillsammans med kolumner från målet.

tänk exempel på tabellerna T1 och T2, som du skapar och fyller i genom att köra följande 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');

Observera att en identitetsegenskap används för att generera nycklarna i båda tabellerna.

Antag att du måste kopiera några rader från T1 till T2; säg de där keycol % 2 = 1. Du vill använda output-klausulen för att returnera de nyligen genererade nycklarna i T2, men du vill också returnera respektive källtangenter från T1 tillsammans med dessa nycklar. Den intuitiva förväntan är att använda följande infoga uttalande:

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;

div > msg 4104, nivå 16, tillstånd 1, Linje 2
Den flerdelade identifieraren ”T1.keycol ” kunde inte vara bunden.

många människor inser inte att konstigt denna begränsning inte gäller för sammanslagningen. Så även om det är lite besvärligt kan du konvertera ditt INSERT-uttalande till ett MERGE-uttalande, men för att göra det måste du sammanfoga predikat för att alltid vara falskt. Detta aktiverar satsen när den inte matchas och tillämpar den enda stödda insatsen där. Du kan använda ett falskt falskt tillstånd som 1 = 2. Här är den fullständiga konverterade koden:

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;

den här gången körs koden framgångsrikt och producerar följande utgång:

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