min gode ven Aaron Bertrand inspirerede mig til at skrive denne artikel. Han mindede mig om, hvordan vi nogle gange tager ting for givet, når de synes indlysende for os og ikke altid gider at kontrollere den fulde historie bag dem. Relevansen for TCL er, at vi nogle gange antager, at vi ved alt, hvad der er at vide om visse TCL-funktioner, og ikke altid gider at kontrollere dokumentationen for at se, om der er mere til dem. I denne artikel dækker jeg en række funktioner, der enten ofte overses, eller som understøtter parametre eller funktioner, der ofte overses. Hvis du har eksempler på dine egne perler, der ofte overses, del dem venligst i kommentarfeltet i denne artikel.
før du begynder at læse denne artikel, spørg dig selv, hvad ved du om følgende funktioner: EOMONTH, oversæt, TRIM, sammenkædning og sammenkædning, LOG, markørvariabler og fusionere med OUTPUT.
i mine eksempler bruger jeg en prøvedatabase kaldet TSKLV5. Du kan finde det script, der opretter og udfylder denne database her, og dens ER diagram her.
EOMONTH har en anden parameter
eomonth-funktionen blev introduceret i Server 2012. Mange mennesker tror, at den kun understøtter en parameter, der holder en inputdato, og at den simpelthen returnerer datoen for slutningen af måneden, der svarer til inputdatoen.
overvej et lidt mere sofistikeret behov for at beregne slutningen af den foregående måned. Antag for eksempel, at du skal forespørge salget.Ordrer tabel, og returnere ordrer, der blev placeret i slutningen af den foregående måned.
en måde at opnå dette på er at anvende eomonth-funktionen på SYSDATETIME for at få datoen for slutningen af måneden for den aktuelle måned og derefter anvende DATEADD-funktionen for at trække en måned fra resultatet, som sådan:
bemærk, at hvis du rent faktisk kører denne forespørgsel i tsklv5-prøvedatabasen, får du et tomt resultat, da den sidste ordredato, der er registreret i tabellen, er 6.maj 2019. Men hvis tabellen havde ordrer med en ordredato, der falder på den sidste dag i den foregående måned, ville forespørgslen have returneret dem.
hvad mange mennesker ikke er klar over, er, at EOMONTH understøtter en anden parameter, hvor du angiver, hvor mange måneder der skal tilføjes eller trækkes fra. Her er syntaksen for funktionen:
EOMONTH ( start_date )
vores opgave kan opnås lettere og naturligt ved blot at angive -1 som den anden parameter til funktionen, som sådan:
SELECT orderid, orderdateFROM Sales.OrdersWHERE orderdate = EOMONTH(SYSDATETIME(), -1);
Oversæt er undertiden enklere end erstat
mange mennesker er bekendt med udskiftningsfunktionen og hvordan den fungerer. Du bruger det, når du vil erstatte alle forekomster af en substring med en anden i en inputstreng. Nogle gange, selvom, når du har flere udskiftninger, som du har brug for at anvende, ved hjælp af erstatte er en smule tricky og resulterer i indviklede udtryk.
Antag som et eksempel, at du får en inputstreng @s, der indeholder et tal med spansk formatering. I Spanien bruger de en periode som separator for grupper på tusinder og et komma som decimalseparator. Du skal konvertere input til Amerikansk formatering, hvor et komma bruges som separator for grupper på tusinder og en periode som decimalseparator.
ved hjælp af et opkald til udskiftningsfunktionen kan du kun erstatte alle forekomster af et tegn eller substring med et andet. For at anvende to udskiftninger (perioder til kommaer og kommaer til perioder) skal du rede funktionsopkald. Den vanskelige del er, at hvis du bruger erstat en gang for at ændre perioder til kommaer, og derefter en anden gang mod resultatet for at ændre kommaer til perioder, ender du kun med perioder. Prøv det:
Du får følgende output:
123.456.789.00
Hvis du vil holde dig til at bruge funktionen erstat, skal du bruge tre funktionsopkald. En til at erstatte perioder med et neutralt tegn, som du ved, som normalt ikke kan vises i dataene (siger ~). En anden mod resultatet for at erstatte alle kommaer med perioder. En anden mod resultatet for at erstatte alle forekomster af den midlertidige karakter (~ i vores eksempel) med kommaer. Her er det komplette udtryk:
denne gang får du den rigtige output:
123,456,789.00
det er lidt gennemførligt, men det resulterer i et langt og indviklet udtryk. Hvad hvis du havde flere udskiftninger at anvende?
mange mennesker er ikke klar over, at
TRANSLATE ( inputString, characters, translations )
den anden indgang (tegn) er en streng med listen over de enkelte tegn, du vil erstatte, og den tredje indgang (oversættelser) er en streng med listen over de tilsvarende tegn, du vil erstatte kildetegnene med. Dette betyder naturligvis, at den anden og tredje parameter skal have det samme antal tegn. Det vigtige ved funktionen er, at den ikke gør separate pas for hver af udskiftningerne. Hvis det gjorde det, ville det potentielt have resulteret i den samme fejl som i det første eksempel, Jeg viste ved hjælp af de to opkald til udskiftningsfunktionen. Derfor håndterer vores opgave bliver en no-brainer:
DECLARE @s AS VARCHAR(20) = '123.456.789,00';SELECT TRANSLATE(@s, '.,', ',.');
Denne kode, der genererer den ønskede effekt:
123,456,789.00
det er ret pænt!
TRIM er mere end LTRIM(RTRIM ())
Mange mennesker, selv inkluderet, antager oprindeligt bare, at det ikke er mere end en simpel genvej til LTRIM(rtrim(input)). Men hvis du tjekker dokumentationen, indser du, at den faktisk er mere kraftfuld end det.
før jeg går ind i detaljerne, skal du overveje følgende opgave: giv en inputstreng @s, Fjern førende og efterfølgende skråstreger (bagud og fremad). Antag som et eksempel, at @S indeholder følgende streng:
//\\ remove leading and trailing backward (\) and forward (/) slashes \\//
den ønskede udgang er:
remove leading and trailing backward (\) and forward (/) slashes
Bemærk, at udgangen skal bevare de førende og efterfølgende mellemrum.
Hvis du ikke kender TRIM ‘ s fulde kapacitet, her er en måde, du kunne have løst opgaven:
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 hjælp af OVERSÆTTER til at erstatte alle rum med en neutral karakter (~) og skråstreger med mellemrum, så ved hjælp af TRIM på trim indledende og afsluttende mellemrum fra resultatet. Dette trin beskærer i det væsentlige førende og efterfølgende skråstreg, midlertidigt ved hjælp af ~ i stedet for originale mellemrum. Her er resultatet af dette trin:
\\~remove~leading~and~trailing~backward~(\)~and~forward~( )~slashes~\\
det andet trin bruger derefter TRANSLATE til at erstatte alle mellemrum med et andet neutralt tegn (^) og bagud skråstreger med mellemrum og derefter bruge TRIM til at trimme førende og efterfølgende mellemrum fra resultatet. Dette trin beskærer i det væsentlige førende og bageste skråstreger, midlertidigt ved hjælp af ^ i stedet for mellemliggende rum. Her er resultatet af dette trin:
~remove~leading~and~trailing~backward~( )~and~forward~(^)~slashes~
det sidste trin bruger TRANSLATE til at erstatte mellemrum med bagudgående skråstreger, ^ med skråstreger fremad og ~ med mellemrum, der genererer det ønskede output:
remove leading and trailing backward (\) and forward (/) slashes
som en øvelse kan du prøve at løse denne opgave med en kompatibel løsning til Server 2017, hvor du ikke kan bruge TRIM og TRANSLATE.hvis du gider at kontrollere dokumentationen, ville du have opdaget, at TRIM er mere sofistikeret end det, du troede oprindeligt. Her er funktionens syntaks:
TRIM ( string )
de valgfrie tegn fra del giver dig mulighed for at specificere et eller flere tegn, du vil beskære fra begyndelsen og slutningen af inputstrengen. I vores tilfælde, er alt du skal gøre er at angive ‘/\’ som denne del, som så:
DECLARE @s AS VARCHAR(100) = '//\\ remove leading and trailing backward (\) and forward (/) slashes \\//'; SELECT TRIM( '/\' FROM @s) AS outputstring;
Der er en ganske betydelig forbedring i forhold til den tidligere løsning!
sammenkædning og sammenkædning
Hvis du har arbejdet med T-KVL i et stykke tid, ved du, hvor akavet det er at håndtere nuller, når du skal sammenkæde strenge. Som et eksempel skal du overveje de placeringsdata, der er registreret for medarbejdere i HR.Employees-tabellen:
denne forespørgsel genererer følgende 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
bemærk, at regiondelen for nogle medarbejdere er irrelevant, og en irrelevant region er repræsenteret af en null. Antag, at du skal sammenkæde placeringsdelene (land, region og by) ved hjælp af et komma som en separator, men ignorere NULL-regioner. Når regionen er relevant, vil du have, at resultatet skal have formularen <coutry>,<region>,<city> og når regionen er irrelevant, vil du have, at resultatet skal have formularen <country>,<city>. Normalt giver sammenkædning af noget med en NULL et NULL-resultat. Du kan ændre denne adfærd ved at slukke for CONCAT_NULL_YIELDS_NULL session mulighed, men jeg vil ikke anbefale at aktivere ikke-standard adfærd.
Hvis du ikke vidste om eksistensen af funktionerne sammenkædning og sammenkædning, ville du sandsynligvis have brugt ISNULL eller COALESCE til at erstatte en NULL med en tom streng, som sådan:
Her er udgangen af denne forespørgsel:
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
SDL Server 2012 introducerede funktionen concat. Denne funktion accepterer en liste over tegnstrengindgange og sammenkæder dem, og mens den gør det, ignorerer den nuller. Så ved hjælp af CONCAT kan du forenkle løsningen som denne:
SELECT empid, CONCAT(country, ',' + region, ',', city) AS locationFROM HR.Employees;
stadig skal du eksplicit angive separatorerne som en del af funktionens indgange. For at gøre vores liv endnu nemmere, introducerede vi en lignende funktion, der hedder
SELECT empid, CONCAT_WS(',', country, region, city) AS locationFROM HR.Employees;
det næste trin er selvfølgelig mindreading. Den 1. April 2020 planlægger Microsoft at frigive CONCAT_MR.funktionen accepterer et tomt input og finder automatisk ud af, hvilke elementer du vil have det til at sammenkæde ved at læse dit sind. Forespørgslen vil så se sådan ud:
LOG har en anden parameter
I lighed med EOMONTH-funktionen er mange mennesker ikke klar over, at start allerede med log 2012 understøtter logfunktionen en anden parameter, der giver dig mulighed for at angive logaritmens base. Før det understøttede T-kvm funktionen LOG (input), der returnerer den naturlige logaritme af input (ved hjælp af konstanten e som base) og LOG10(input), der bruger 10 som base.
ikke at være opmærksom på eksistensen af den anden parameter til LOGFUNKTIONEN, når folk ønskede at beregne Logb(h), hvor b er en anden base end e og 10, gjorde de det ofte langt. For at beregne Log2(8), anvender du følgende beregning:
:
Når du er klar over, at LOG understøtter en anden parameter, hvor du angiver basen, bliver beregningen simpelthen:
DECLARE @x AS FLOAT = 8, @b AS INT = 2;SELECT LOG(@x, @b);
cursor variabel
Hvis du har arbejdet med T-KVL i et stykke tid, har du sandsynligvis haft mange chancer for at arbejde med markører. Som du ved, når du arbejder med en markør, bruger du typisk følgende trin:
- erklære markøren
- Åbn markøren
- gentag gennem markørposterne
- Luk markøren
- Deallokere markøren
som et eksempel, antag at du skal udføre en opgave pr.database i din forekomst. Ved hjælp af en markør bruger du normalt kode, der ligner følgende:
kommandoen Luk frigiver det aktuelle resultatsæt og frigør låse. Kommandoen DEALLOCATE fjerner en markørreference, og når den sidste reference er deallokeret, frigør de datastrukturer, der omfatter markøren. Hvis du prøver at køre ovenstående kode to gange uden kommandoerne luk og DEALLOKERE, får du følgende fejl:
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.
sørg for at køre kommandoerne luk og DEALLOKERE, før du fortsætter.
mange mennesker er ikke klar over, at når de har brug for at arbejde med en markør i kun en batch, hvilket er det mest almindelige tilfælde, i stedet for at bruge en almindelig markør, kan du arbejde med en markørvariabel. Som enhver variabel er omfanget af en markørvariabel kun det parti, hvor det blev erklæret. Dette betyder, at så snart en batch er færdig, udløber alle variabler. Ved hjælp af en markørvariabel, når en batch er færdig, lukker og deallokerer serveren automatisk, hvilket sparer dig behovet for at køre kommandoen Luk og DEALLOKERE eksplicit.
Her er den reviderede kode ved hjælp af en markørvariabel denne gang:
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;
du er velkommen til at udføre det flere gange og bemærke, at denne gang får du ingen fejl. Det er bare renere, og du behøver ikke bekymre dig om at holde markørressourcer, hvis du har glemt at lukke og deallokere markøren.
Flet med OUTPUT
siden starten af OUTPUTKLAUSULEN for modifikationsopgørelser i Server 2005 viste det sig at være et meget praktisk værktøj, når du ønskede at returnere data fra ændrede rækker. Folk bruger denne funktion regelmæssigt til formål som arkivering, revision og mange andre brugssager. En af de irriterende ting ved denne funktion er dog, at hvis du bruger den med Indsæt udsagn, har du kun lov til at returnere data fra de indsatte rækker, idet du sætter outputkolonnerne med indsat. Du har ikke adgang til kildetabellens kolonner, selvom du nogle gange skal returnere kolonner fra kilden sammen med kolonner fra målet.
som et eksempel skal du overveje tabellerne T1 og T2, som du opretter og udfylder ved at kø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');
Bemærk, at en identitet ejendommen er anvendt til at generere nøgler i begge tabeller.
Antag at du skal kopiere nogle rækker fra T1 til T2; sig dem, hvor keycol % 2 = 1. Du vil bruge OUTPUTKLAUSULEN til at returnere de nyligt genererede taster i T2, men du vil også vende tilbage sammen med disse taster de respektive kildetaster fra T1. Den intuitive forventning er at bruge følgende indsæt erklæring:
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;
desværre tillader OUTPUTKLAUSULEN som nævnt ikke at henvise til kolonner fra kildetabellen, så du får følgende fejl:
flerdelsidentifikatoren “T1.keycol ” kunne ikke være bundet.
mange mennesker er ikke klar over, at mærkeligt denne begrænsning ikke gælder for FLETNINGSERKLÆRINGEN. Så selvom det er lidt akavet, kan du konvertere din indsæt-sætning til en FLETNINGSERKLÆRING, men for at gøre det skal du bruge FLETNINGSPRÆDIKATET til altid at være falsk. Dette aktiverer klausulen, når den ikke matches, og anvender den eneste understøttede indsats der. Du kan bruge en falsk falsk tilstand som 1 = 2. Her er den komplette konverterede kode:
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;
denne gang kører koden med succes og producerer følgende output:
T1_keycol T2_keycol----------- -----------1 13 25 3