Min gode venn Aaron Bertrand inspirerte meg til å skrive denne artikkelen. Han minnet meg om hvordan noen ganger tar vi ting for gitt når de virker åpenbare for oss og ikke alltid bry å sjekke hele historien bak dem. Relevansen TIL T-SQL er at noen ganger antar vi at vi vet alt som er å vite om visse t-SQL-funksjoner, og ikke alltid bry å sjekke dokumentasjonen for å se om det er mer til dem. I denne artikkelen dekker jeg EN rekke t-SQL-funksjoner som enten ofte blir oversett, eller som stotter parametere eller evner som ofte overses. Hvis du har eksempler på din egen AV T-SQL gems som ofte overses, vennligst del dem i kommentarfeltet i denne artikkelen.før du begynner å lese denne artikkelen, spør deg selv hva vet du om følgende T-SQL-funksjoner: EOMONTH, TRANSLATE, TRIM, CONCAT og CONCAT_WS, LOG, markørvariabler og FUSJONERE med UTDATA.

I mine eksempler bruker jeg EN prøvedatabase KALT TSQLV5. Du kan finne skriptet som lager og fyller denne databasen her, og dens er diagram her.

EOMONTH har en andre parameter

eomonth-funksjonen ble introdusert I SQL Server 2012. Mange tror at den bare støtter en parameter som holder en inngangsdato, og at den bare returnerer datoen for slutten av måneden som tilsvarer inngangsdatoen.

Vurder et litt mer sofistikert behov for å beregne slutten av forrige måned. Anta for eksempel at Du må spørre Salget.Ordretabell, og returordrer som ble plassert på slutten av forrige måned.

En måte å oppnå dette på er å bruke eomonth-funksjonen TIL SYSDATETIME for å få månedens sluttdato for den nåværende måneden, og deretter bruke DATEADD-funksjonen til å trekke en måned fra resultatet, slik som:

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

merk at hvis du faktisk kjører denne spørringen i tsqlv5-eksempeldatabasen, FÅR du et tomt resultat siden siste bestillingsdato registrert i tabellen er 6.mai 2019. Men hvis tabellen hadde ordrer med en ordredato som faller på den siste dagen i forrige måned, ville spørringen ha returnert dem.

det mange ikke vet er AT EOMONTH støtter en andre parameter der du angir hvor mange måneder du skal legge til eller trekke fra. Her er syntaksen til funksjonen:

EOMONTH ( start_date )

vår oppgave kan oppnås lettere og naturlig ved å bare angi -1 som den andre parameteren til funksjonen, slik som:

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

hvis du vil holde deg til å bruke erstatt-funksjonen, trenger du tre funksjonssamtaler. En for å erstatte perioder med et nøytralt tegn som du vet som normalt ikke kan vises i dataene(si,~). En annen mot resultatet for å erstatte alle kommaer med perioder. En annen mot resultatet for å erstatte alle forekomster av midlertidig karakter (~i vårt eksempel) med kommaer. Her er det komplette uttrykket:

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

Denne gangen får du riktig utgang:

123,456,789.00

det er litt gjennomførbart, men det resulterer i et langt og innviklet uttrykk. Hva om du hadde flere erstatninger å søke?

MANGE er ikke klar over AT SQL Server 2017 introduserte en ny funksjon KALT TRANSLATE som forenkler slike erstatninger mye. Her er funksjonens syntaks:

TRANSLATE ( inputString, characters, translations )

den andre inngangen (tegn) er en streng med listen over de enkelte tegnene du vil erstatte, og den tredje inngangen (oversettelser) er en streng med listen over de tilsvarende tegnene du vil erstatte kildetegnene med. Dette betyr naturlig at andre og tredje parametere må ha samme antall tegn. Det som er viktig med funksjonen er at den ikke gjør separate passerer for hver av erstatningene. Hvis det gjorde det, ville det potensielt ha resultert i samme feil som i det første eksemplet jeg viste ved å bruke de to anropene TIL ERSTATT-funksjonen. Følgelig, håndtering vår oppgave blir en no-brainer:

– >

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

Denne koden genererer ønsket resultat:

123,456,789.00

det er ganske pent!

TRIM er MER ENN LTRIM (RTRIM ())

SQL Server 2017 introdusert støtte for FUNKSJONEN TRIM. Mange mennesker, inkludert meg selv, antar først at det ikke er mer enn en enkel snarvei til LTRIM(RTRIM(input)). Men hvis du sjekker dokumentasjonen, innser du at det faktisk er kraftigere enn det.

før jeg går inn i detaljene, bør du vurdere følgende oppgave: gitt en inngangsstreng @s, fjern ledende og etterfølgende skråstreker (bakover og fremover). Som et eksempel, anta at @s inneholder følgende streng:

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

ønsket utgang er:

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

Merk at utgangen skal beholde ledende og etterfølgende mellomrom.

Hvis du ikke vet av TRIM er alle muligheter, her er en måte du kanskje har løst oppgaven:

– >

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

løsningen starter ved hjelp OVERSETT til å erstatte alle områder med en nøytral karakter (~) og frem skråstreker med mellomrom, så ved hjelp av TRIM for å trimme ledende og etterfølgende mellomrom fra resultatet. Dette trinnet trimmer i hovedsak ledende og etterfølgende skråstreker, midlertidig ved hjelp av ~ i stedet for originale mellomrom. Her er resultatet av dette trinnet:

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

DET andre trinnet bruker DERETTER TRANSLATE for å erstatte alle mellomrom med et annet nøytralt tegn (^) og bakover skråstreker med mellomrom, og deretter BRUKER TRIM til å trimme ledende og etterfølgende mellomrom fra resultatet. Dette trinnet trimmer i hovedsak ledende og etterfølgende skråstreker, midlertidig ved hjelp av ^ i stedet for mellomliggende mellomrom. Her er resultatet av dette trinnet:

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

DET siste trinnet bruker TRANSLATE til å erstatte mellomrom med bakover skråstreker, ^ med forover skråstreker, og ~ med mellomrom, genererer ønsket utgang:

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

prøv å løse denne oppgaven med en PRE-SQL Server 2017-kompatibel løsning der du ikke kan BRUKE TRIM og TRANSLATE.

Tilbake TIL SQL Server 2017 og over, hvis du plaget å sjekke dokumentasjonen, ville DU ha oppdaget AT TRIM er mer sofistikert enn det du trodde i utgangspunktet. Her er Funksjonens Syntaks:

TRIM ( string )

de valgfrie tegnene fra delen lar deg angi ett eller flere tegn du vil ha trimmet fra begynnelsen og slutten av inngangsstrengen. I vårt tilfelle, alt du trenger å gjøre er å spesifisere ‘/\’ som denne delen, som så:

– >

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

Det er en ganske betydelig forbedring i forhold til den tidligere løsningen!

CONCAT og CONCAT_WS

Hvis DU har jobbet MED T-SQL for en stund, vet du hvor vanskelig det er å håndtere NULLs når du må sammenkoble strenger. Som et eksempel, bør du vurdere plasseringen data som er registrert for ansatte i HR.Ansatte-tabellen:

– >

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

Dette søket genererer følgende resultat:

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

legg Merke til at for noen ansatte regionen delen er irrelevant, og en irrelevant regionen er representert ved en NULL. Anta at du må sammenkoble plasseringsdelene (land, region og by), ved hjelp av et komma som en separator, men ignorerer NULLREGIONER. Når regionen er relevant, vil du at resultatet skal ha skjemaet <coutry>,<region>,<city> og når regionen er irrelevant, vil du at resultatet skal ha skjemaet <country>,<city>. Normalt gir sammenkobling av NOE MED NULL ET NULLRESULTAT. Du kan endre denne virkemåten ved å slå AV ALTERNATIVET CONCAT_NULL_YIELDS_NULL økt, men jeg vil ikke anbefale å aktivere ikke-standard oppførsel.

Hvis du ikke visste om EKSISTENSEN av CONCAT-og CONCAT_WS-funksjonene, ville DU sannsynligvis ha brukt ISNULL eller COALESCE for å erstatte EN NULL med en tom streng, slik som:

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

her er resultatet AV DENNE SPØRRINGEN:

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 introduserte funksjonen concat. Denne funksjonen godtar en liste over tegnstrenginnganger og sammenkobler dem, og mens du gjør Det, ignorerer Den Null. Så ved Å BRUKE CONCAT kan du forenkle løsningen slik:

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

FOR å gjøre våre liv enda enklere, SQL Server 2017 innført en lignende funksjon kalt CONCAT_WS der du starter ved å angi skilletegn, etterfulgt av elementene du vil sette sammen. Med denne funksjonen blir løsningen ytterligere forenklet slik:

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

Den 1. April 2020 Planlegger Microsoft å frigjøre CONCAT_MR. funksjonen vil akseptere en tom inngang, og finne ut automatisk hvilke elementer du vil at den skal sammenkoble ved å lese tankene dine. Spørringen vil da se slik ut:

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

Logg har en andre parameter

ligner på eomonth-funksjonen, mange innser ikke at det allerede starter MED SQL Server 2012, støtter loggfunksjonen en andre parameter som lar deg angi logaritmens base. FØR det støttet T-SQL funksjonsloggen (input) som returnerer den naturlige logaritmen til inngangen (ved hjelp av konstant e som base) og LOG10(input) som bruker 10 som base.

Ikke å være klar over eksistensen av DEN andre parameteren TIL LOGGFUNKSJONEN, da folk ønsket å beregne Logb (x), hvor b er en annen base enn e og 10, gjorde de det ofte langt. Du kan stole på følgende ligning:

Som et eksempel, for å beregne Log2(8), stole du på følgende ligning:

Oversatt TIL T-SQL, bruker du følgende beregning:

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

div>

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

hvis du Har Jobbet med t-SQL for en stund, har du sannsynligvis hatt mange sjanser til å jobbe med pekere. Som du vet, når du arbeider med en markør, bruker du vanligvis følgende trinn:

• Deklarere markøren
• Åpne markøren
• Iterere gjennom markørpostene
• Lukk markøren
• Tilordne markøren

som et eksempel, anta at du må utføre en oppgave per database i din forekomst. Ved hjelp av en markør, vil du vanligvis bruker kode som ligner på følgende:

– >

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;

LUKK kommando utgivelser dagens resultat og frigjør låser. KOMMANDOEN DEALLOCATE fjerner en markørreferanse, og når den siste referansen er deallokert, frigjør datastrukturene som omfatter markøren. Hvis du prøver å kjøre koden ovenfor to ganger uten KOMMANDOENE LUKK og FJERN TILDELING, får du følgende feil:

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.

Pass på at du kjører KOMMANDOENE LUKK OG FJERN TILDELING før du fortsetter.

Mange innser ikke at når de trenger å jobbe med en markør i bare ett parti, som er det vanligste tilfellet, i stedet for å bruke en vanlig markør, kan du jobbe med en markørvariabel. Som enhver variabel er omfanget av en markørvariabel bare batchen der den ble deklarert. Dette betyr at så snart en batch er ferdig, utløper alle variabler. VED hjelp av en markørvariabel, når en batch er ferdig, LUKKER SQL Server og deallokerer den automatisk, og sparer deg for behovet for å kjøre KOMMANDOEN LUKK og DEALLOKERE eksplisitt.

her er den reviderte koden ved hjelp av en markørvariabel denne gangen:

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;

Det er bare renere, og du trenger ikke å bekymre deg for å holde markøren ressurser hvis du glemte å lukke og fordele markøren.

MERGE with OUTPUT

Siden begynnelsen AV OUTPUT-klausulen For modifikasjonserklæringer I SQL Server 2005, viste det seg å være et veldig praktisk verktøy når du ønsket å returnere data fra endrede rader. Folk bruker denne funksjonen regelmessig for formål som arkivering, revisjon og mange andre brukstilfeller. En av de irriterende tingene med denne funksjonen er imidlertid at hvis du bruker DEN MED INSERT-setninger, har du bare lov til å returnere data fra de innsatte radene, og prefikser utgangskolonnene med inserted. Du har ikke tilgang til kildetabellens kolonner, selv om du noen ganger må returnere kolonner fra kilden sammen med kolonner fra målet.

som et eksempel, vurder tabellene T1 Og T2, som du oppretter og fyller ut ved å kjøre følgende kode:

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');

Anta at du må kopiere noen rader Fra T1 Til T2; si de der keycol % 2 = 1. Du vil bruke OUTPUT-klausulen til å returnere de nylig genererte nøklene I T2, men du vil også returnere sammen med disse tastene de respektive kildetastene Fra T1. Den intuitive forventningen er å bruke FØLGENDE INSERT-setning:

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, tilstand 1, linje 2
flerdelte identifikatoren «t1.keycol » kunne ikke være bundet.

Mange innser ikke at merkelig denne begrensningen ikke gjelder FOR MERGE-setningen. Så selv om DET er litt vanskelig, kan du konvertere INSERT-setningen til EN MERGE-setning, men for å gjøre det, må DU FLETTE-predikatet alltid være falskt. Dette vil aktivere når ikke MATCHET-klausulen og bruke den eneste STØTTEDE INSERT-handlingen der. Du kan bruke en dummy falsk tilstand som 1 = 2. Her er den komplette konverterte 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;

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