bunul meu prieten Aaron Bertrand ma inspirat pentru a scrie acest articol. Mi-a amintit cum uneori luăm lucrurile de la sine atunci când ni se par evidente și nu ne deranjăm întotdeauna să verificăm întreaga poveste din spatele lor. Relevanța pentru T-SQL este că uneori presupunem că știm tot ce trebuie să știm despre anumite caracteristici T-SQL și nu ne deranjăm întotdeauna să verificăm documentația pentru a vedea dacă există mai multe. În acest articol am acoperi o serie de caracteristici T-SQL, care sunt fie adesea trecute cu vederea în întregime, sau că parametrii de sprijin sau capabilități care sunt adesea trecute cu vederea. Dacă aveți exemple proprii de pietre T-SQL care sunt adesea trecute cu vederea, vă rugăm să le împărtășiți în secțiunea de comentarii a acestui articol.

înainte de a începe să citiți acest articol, întrebați-vă ce știți despre următoarele caracteristici T-SQL: EOMONTH, TRANSLATE, TRIM, CONCAT și CONCAT_WS, log, variabile cursor și fuzionați cu ieșirea.

în exemplele mele Voi folosi o bază de date eșantion numit TSQLV5. Puteți găsi scriptul care creează și populează această bază de date aici și diagrama ER aici.

EOMONTH are un al doilea parametru

funcția EOMONTH a fost introdusă în SQL Server 2012. Mulți oameni cred că acceptă un singur parametru care deține o dată de intrare și că returnează pur și simplu Data de sfârșit de lună care corespunde datei de intrare.

luați în considerare o nevoie puțin mai sofisticată de a calcula sfârșitul lunii precedente. De exemplu, să presupunem că trebuie să interogați vânzările.Tabelul comenzilor și comenzile de returnare care au fost plasate la sfârșitul lunii precedente.

o modalitate de a realiza acest lucru este să aplicați funcția EOMONTH la SYSDATETIME pentru a obține data de sfârșit de lună a lunii curente și apoi să aplicați funcția DATEADD pentru a scădea o lună din rezultat, astfel:

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

veți obține următoarea ieșire:

123.456.789.00

dacă doriți să rămâneți la utilizarea funcției înlocuire, aveți nevoie de trei apeluri de funcții. Unul care să înlocuiască perioadele cu un caracter neutru despre care știți că nu poate apărea în mod normal în date (să zicem, ~). Un altul împotriva rezultatului pentru a înlocui toate virgulele cu perioade. Altul împotriva rezultatului pentru a înlocui toate aparițiile caracterului temporar (~în exemplul nostru) cu virgule. Iată expresia completă:

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

de data aceasta veți obține rezultatul corect:

123,456,789.00

este un fel de greu de realizat, dar rezultă într-o expresie lungă și complicată. Ce se întâmplă dacă ai avea mai multe înlocuiri de aplicat?

mulți oameni nu sunt conștienți de faptul că SQL Server 2017 a introdus o nouă funcție numită TRANSLATE care simplifică foarte mult astfel de înlocuiri. Iată sintaxa funcției:

TRANSLATE ( inputString, characters, translations )

a doua intrare (caractere) este un șir cu lista caracterelor individuale pe care doriți să le înlocuiți, iar a treia intrare (traduceri) este un șir cu lista caracterelor corespunzătoare cu care doriți înlocuiți caracterele sursă. Acest lucru înseamnă în mod natural că al doilea și al treilea parametru trebuie să aibă același număr de caractere. Ceea ce este important despre funcția este că nu face treceri separate pentru fiecare dintre înlocuitori. Dacă ar fi făcut-o, ar fi putut duce la aceeași eroare ca în primul exemplu pe care l-am arătat folosind cele două apeluri către funcția de înlocuire. În consecință, manipularea sarcinii noastre devine o no-brainer:

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

acest cod generează ieșirea dorită:

123,456,789.00

asta e destul de curat!

TRIM este mai mult decât LTRIM(RTRIM ())

SQL Server 2017 a introdus suport pentru funcția TRIM. Mulți oameni, inclusiv eu, presupun inițial că nu este decât o simplă comandă rapidă către ltrim(rtrim(input)). Cu toate acestea, dacă verificați documentația, vă dați seama că este de fapt mai puternic decât atât.

înainte de a intra în detalii, luați în considerare următoarea sarcină: având în vedere un șir de intrare @s, eliminați barele de conducere și de tracțiune (înapoi și înainte). De exemplu, să presupunem că @s conține următorul șir:

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

ieșirea dorită este:

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

rețineți că ieșirea trebuie să păstreze spațiile de conducere și de la sfârșit.

dacă nu știați de capacitățile complete ale TRIM, iată o modalitate prin care ați rezolvat sarcina:

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

soluția începe prin utilizarea traducerii pentru a înlocui toate spațiile cu un caracter neutru (~) și slash-uri înainte cu spații, apoi folosind Trim pentru a tăia spațiile de conducere și la final din rezultat. Acest pas, în esență, ornamente de conducere și trailing slashes înainte, folosind temporar ~ în loc de spații originale. Iată rezultatul acestui pas:

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

al doilea pas folosește TRANSLATE pentru a înlocui toate spațiile cu un alt caracter neutru (^) și slash-uri înapoi cu spații, apoi folosind TRIM pentru a tăia spațiile de conducere și de la final din rezultat. Acest pas, în esență, ornamente de conducere și trailing înapoi slashes, temporar folosind ^ în loc de spații intermediare. Iată rezultatul acestui pas:

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

ultimul pas folosește TRANSLATE pentru a înlocui spațiile cu slash-uri înapoi, ^ cu slash-uri înainte și ~ cu spații, generând ieșirea dorită:

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

ca exercițiu, încercați să rezolvați această sarcină cu o soluție compatibilă pre-SQL Server 2017 în care nu puteți utiliza TRIM și TRANSLATE.

înapoi la SQL Server 2017 și mai sus, dacă te-ai deranjat verificarea documentației, ar fi descoperit că TRIM este mai sofisticat decât ceea ce ai crezut inițial. Iată sintaxa funcției:

TRIM ( string )

caracterele opționale din parte vă permit să specificați unul sau mai multe caractere pe care doriți să le tăiați de la începutul și sfârșitul șirului de intrare. În cazul nostru, tot ce trebuie să faceți este să specificați ‘/ \ ‘ ca această parte, așa:

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

aceasta este o îmbunătățire destul de semnificativă în comparație cu soluția anterioară!

CONCAT și CONCAT_WS

dacă ați lucrat cu T-SQL pentru un timp știi cât de ciudat este de a face cu NULLs atunci când aveți nevoie pentru a concatena siruri de caractere. De exemplu, luați în considerare datele de locație înregistrate pentru angajați în tabelul HR.Employees:

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

această interogare generează următoarea ieșire:

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

observați că pentru unii angajați partea regiunii este irelevantă și o regiune irelevantă este reprezentată de un nul. Să presupunem că trebuie să concatenați părțile de locație (țară, regiune și oraș), folosind o virgulă ca separator, dar ignorând regiunile nule. Când regiunea este relevantă, doriți ca rezultatul să aibă forma <coutry>,<region>,<city> și când regiunea este irelevantă doriți ca rezultatul să aibă forma <country>,<city>. În mod normal, concatenarea a ceva cu un nul produce un rezultat nul. Puteți schimba acest comportament dezactivând opțiunea de sesiune CONCAT_NULL_YIELDS_NULL, dar nu aș recomanda activarea comportamentului nestandard.

dacă nu știați de existența funcțiilor CONCAT și CONCAT_WS, probabil că ați fi folosit ISNULL sau COALESCE pentru a înlocui un NULL cu un șir gol, așa:

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

iată rezultatul acestei interogări:

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 a introdus funcția concat. Această funcție acceptă o listă de intrări șir de caractere și le concatenează, și în timp ce face acest lucru, ignoră nuluri. Deci, folosind CONCAT puteți simplifica soluția astfel:

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

totuși, trebuie să specificați în mod explicit separatoarele ca parte a intrărilor funcției. Pentru a ne face viața și mai ușoară, SQL Server 2017 a introdus o funcție similară numită CONCAT_WS unde începeți prin indicarea separatorului, urmată de elementele pe care doriți să le concatenați. Cu această funcție, soluția este simplificată în continuare:

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

următorul pas este desigur mindreading. Pe 1 aprilie 2020 Microsoft intenționează să lanseze CONCAT_MR. funcția va accepta o intrare goală și va afla automat ce elemente doriți să concateneze citindu-vă mintea. Interogarea va arăta astfel:

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

LOG are un al doilea parametru

Similar cu funcția EOMONTH, mulți oameni nu-și dau seama că începând deja cu SQL Server 2012, funcția log acceptă un al doilea parametru care vă permite să indicați baza logaritmului. Înainte de aceasta, T-SQL a acceptat funcția LOG (intrare) care returnează logaritmul natural al intrării(folosind Constanta e ca bază) și LOG10 (intrare) care folosește 10 ca bază.

nefiind conștienți de existența celui de-al doilea parametru al funcției jurnal, Atunci când oamenii doreau să calculeze Logb(x), unde b este o altă bază decât e și 10, au făcut-o adesea pe drum lung. Vă puteți baza pe următoarea ecuație:

ca exemplu, pentru a calcula Log2(8), vă bazați pe următoarea ecuație:

tradus în T-SQL, aplicați următorul calcul:

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

odată ce vă dați seama că jurnalul acceptă un al doilea parametru în care indicați baza, calculul devine pur și simplu:

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;

comanda CLOSE eliberează setul de rezultate curent și eliberează încuietori. Comanda DEALLOCARE elimină o referință a cursorului și, atunci când ultima referință este dezalocată, eliberează structurile de date care cuprind cursorul. Dacă încercați să rulați codul de mai sus de două ori fără comenzile CLOSE și delocate, veți primi următoarea eroare:

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.

asigurați-vă că executați comenzile CLOSE și delocate înainte de a continua.

mulți oameni nu își dau seama că atunci când trebuie să lucreze cu un cursor într-un singur lot, care este cel mai frecvent caz, în loc să utilizați un cursor obișnuit, puteți lucra cu o variabilă cursor. Ca orice variabilă, domeniul de aplicare al unei variabile cursor este doar lotul în care a fost declarată. Aceasta înseamnă că, de îndată ce un lot se termină, toate variabilele expiră. Folosind o variabilă cursor, odată ce un lot se termină, SQL Server se închide și se delocalizează automat, economisind necesitatea de a rula comanda CLOSE and delocate Explicit.

Iată codul revizuit folosind o variabilă cursor de data aceasta:

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;

Simțiți-vă liber să o executați de mai multe ori și observați că de data aceasta nu primiți erori. Este doar mai curat și nu trebuie să vă faceți griji cu privire la păstrarea resurselor cursorului dacă ați uitat să închideți și să alocați cursorul.

MERGE cu ieșire

de la începutul clauzei de ieșire pentru declarațiile de modificare în SQL Server 2005, sa dovedit a fi un instrument foarte practic ori de câte ori ai vrut să se întoarcă date din rânduri modificate. Oamenii folosesc această caracteristică în mod regulat în scopuri precum arhivarea, auditarea și multe alte cazuri de utilizare. Unul dintre lucrurile enervante despre această caracteristică, totuși, este că, dacă îl utilizați cu instrucțiuni de inserare, aveți voie să returnați date din rândurile inserate, prefixând coloanele de ieșire cu inserate. Nu aveți acces la coloanele tabelului sursă, chiar dacă uneori trebuie să returnați coloane din sursă alături de coloane din țintă.

de exemplu, luați în considerare tabelele T1 și T2, pe care le creați și le populați rulând următorul cod:

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

observați că o proprietate de identitate este utilizată pentru a genera cheile din ambele tabele.

Să presupunem că trebuie să copiați câteva rânduri de la T1 la T2; să zicem, cele în care keycol % 2 = 1. Doriți să utilizați clauza de ieșire pentru a returna cheile nou generate în T2, dar doriți, de asemenea, să returnați alături de aceste chei cheile sursă respective de la T1. Așteptarea intuitivă este de a utiliza următoarea declarație de inserare:

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;

Din păcate, totuși, așa cum am menționat, clauza de ieșire nu vă permite să vă referiți la coloanele din tabelul sursă, astfel încât să obțineți următoarea eroare:

mulți oameni nu-și dau seama că, în mod ciudat, această limitare nu se aplică Declarației de îmbinare. Deci, chiar dacă este un pic ciudat, puteți converti Declarația de inserare într-o declarație de îmbinare, dar pentru a face acest lucru, aveți nevoie ca predicatul de îmbinare să fie întotdeauna fals. Aceasta va activa clauza când nu se potrivește și va aplica acolo singura acțiune de inserare acceptată. Puteți utiliza o condiție falsă falsă, cum ar fi 1 = 2. Iată codul complet convertit:

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;

de data aceasta codul rulează cu succes, producând următoarea ieșire:

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