Il mio buon amico Aaron Bertrand mi ha ispirato a scrivere questo articolo. Mi ha ricordato di come a volte diamo le cose per scontate quando ci sembrano ovvie e non sempre ci preoccupiamo di controllare la storia completa dietro di loro. La rilevanza per T-SQL è che a volte assumiamo di sapere tutto ciò che c’è da sapere su alcune funzionalità di T-SQL e non sempre ci si preoccupa di controllare la documentazione per vedere se c’è di più. In questo articolo mi occupo di una serie di funzionalità T-SQL che sono spesso completamente trascurate o che supportano parametri o funzionalità spesso trascurate. Se si dispone di esempi propri di gemme T-SQL che sono spesso trascurati, si prega di condividere quelli nella sezione commenti di questo articolo.

Prima di iniziare a leggere questo articolo chiediti cosa sai delle seguenti funzionalità di T-SQL: EOMONTH, TRANSLATE, TRIM, CONCAT e CONCAT_WS, LOG, variabili cursore e MERGE con OUTPUT.

Nei miei esempi userò un database di esempio chiamato TSQLV5. Puoi trovare lo script che crea e popola questo database qui e il suo diagramma ER qui.

EOMONTH ha un secondo parametro

La funzione EOMONTH è stata introdotta in SQL Server 2012. Molte persone pensano che supporti solo un parametro che contiene una data di input e che restituisca semplicemente la data di fine mese corrispondente alla data di input.

Considera una necessità leggermente più sofisticata di calcolare la fine del mese precedente. Ad esempio, supponiamo che sia necessario interrogare le vendite.Tabella ordini, e gli ordini di ritorno che sono stati effettuati alla fine del mese precedente.

Un modo per ottenere questo è quello di applicare la funzione EOMONTH per SYSDATETIME per ottenere il fine mese data del mese corrente, e quindi applicare la funzione DATEADD per sottrarre un mese dal risultato, in questo modo:

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

si noti che, se effettivamente eseguire la query in TSQLV5 database di esempio si ottiene un risultato vuoto dall’ultimo ordine data registrata nella tabella 6 Maggio 2019. Tuttavia, se la tabella aveva ordini con una data dell’ordine che cade l’ultimo giorno del mese precedente, la query avrebbe restituito quelli.

Quello che molte persone non si rendono conto è che EOMONTH supporta un secondo parametro in cui si indica quanti mesi aggiungere o sottrarre. Ecco la sintassi della funzione:

EOMONTH ( start_date )

Il nostro compito può essere raggiunto più facilmente e naturalmente semplicemente specificando -1 come secondo parametro della funzione, in questo modo:

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

TRADURRE a volte è più semplice di SOSTITUIRE

Molte persone hanno familiarità con la funzione REPLACE e come funziona. Lo si utilizza quando si desidera sostituire tutte le occorrenze di una sottostringa con un’altra in una stringa di input. A volte, tuttavia, quando si hanno più sostituzioni che è necessario applicare, l’utilizzo di REPLACE è un po ‘ complicato e si traduce in espressioni contorte.

Ad esempio, supponiamo che ti venga data una stringa di input @s che contiene un numero con formattazione spagnola. In Spagna usano un punto come separatore per gruppi di migliaia e una virgola come separatore decimale. È necessario convertire l’input in formattazione US, in cui una virgola viene utilizzata come separatore per gruppi di migliaia e un punto come separatore decimale.

Utilizzando una chiamata alla funzione REPLACE, è possibile sostituire solo tutte le occorrenze di un carattere o sottostringa con un’altra. Per applicare due sostituzioni (periodi alle virgole e virgole ai periodi) è necessario nidificare le chiamate di funzione. La parte difficile è che se si utilizza SOSTITUISCI una volta per cambiare i periodi in virgole, e poi una seconda volta contro il risultato per cambiare le virgole in periodi, si finisce con solo periodi. Prova:

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

Si ottiene il seguente output:

123.456.789.00

Se si desidera attenersi a usare la funzione REPLACE, avete bisogno di tre chiamate di funzione. Uno per sostituire i periodi con un carattere neutro che sai che normalmente non può apparire nei dati (ad esempio,~). Un altro contro il risultato per sostituire tutte le virgole con i periodi. Un altro contro il risultato per sostituire tutte le occorrenze del carattere temporaneo (~nel nostro esempio) con virgole. Ecco il programma completo di espressione:

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

Questa volta si ottiene il diritto di uscita:

123,456,789.00

È fattibile, ma è il risultato di un lungo e contorto espressione. E se avessi più sostituzioni da applicare?

Molte persone non sono a conoscenza del fatto che SQL Server 2017 ha introdotto una nuova funzione chiamata TRANSLATE che semplifica notevolmente tali sostituzioni. Ecco la sintassi della funzione:

TRANSLATE ( inputString, characters, translations )

Il secondo ingresso (caratteri) è una stringa con l’elenco dei singoli caratteri che si desidera sostituire, e il terzo di ingresso (traduzioni) è una stringa con l’elenco dei caratteri che si desidera sostituire i caratteri di origine con. Ciò significa naturalmente che il secondo e il terzo parametro devono avere lo stesso numero di caratteri. La cosa importante della funzione è che non esegue passaggi separati per ciascuna delle sostituzioni. Se lo avesse fatto, avrebbe potenzialmente provocato lo stesso bug del primo esempio che ho mostrato usando le due chiamate alla funzione REPLACE. Di conseguenza, la manipolazione, il nostro compito diventa un gioco da ragazzi:

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

Questo codice genera l’output desiderato:

123,456,789.00

Che è abbastanza pulito!

TRIM è più di LTRIM (RTRIM ())

SQL Server 2017 ha introdotto il supporto per la funzione TRIM. Molte persone, me compreso, inizialmente presumono che non sia altro che una semplice scorciatoia per LTRIM(RTRIM(input)). Tuttavia, se controlli la documentazione, ti rendi conto che è in realtà più potente di così.

Prima di entrare nei dettagli, considera la seguente attività: data una stringa di input @s, rimuovi le barre iniziali e finali (indietro e avanti). Ad esempio, supponiamo che @s contenga la seguente stringa:

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

L’output desiderato è:

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

Si noti che l’output deve mantenere gli spazi iniziale e finale.

Se non sapete di TAGLIARE tutte le funzionalità, ecco un modo si potrebbe avere risolto il compito:

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

La soluzione inizia utilizzando TRADURRE per sostituire tutti gli spazi con un carattere neutro (~) e le barre con spazi, poi con TRIM per tagliare gli spazi iniziali e finali dal risultato. Questo passaggio taglia essenzialmente le barre iniziali e finali, usando temporaneamente ~ invece degli spazi originali. Ecco il risultato di questo passaggio:

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

Il secondo passaggio utilizza TRANSLATE per sostituire tutti gli spazi con un altro carattere neutro (^) e barre all’indietro con spazi, quindi utilizzando TRIM per tagliare gli spazi iniziali e finali dal risultato. Questo passaggio essenzialmente taglia le barre iniziali e finali all’indietro, usando temporaneamente ^ invece degli spazi intermedi. Ecco il risultato di questo passaggio:

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

L’ultimo passaggio utilizza TRANSLATE per sostituire gli spazi con barre all’indietro, ^ con barre in avanti e ~ con spazi, generando l’output desiderato:

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

Come esercizio, prova a risolvere questo compito con una soluzione compatibile pre-SQL Server 2017 in cui non puoi usare TRIM e TRANSLATE.

Torna a SQL Server 2017 e versioni successive, se ti fossi preoccupato di controllare la documentazione, avresti scoperto che TRIM è più sofisticato di quello che pensavi inizialmente. Ecco la sintassi della funzione:

TRIM ( string )

I caratteri opzionali DALLA parte consentono di specificare uno o più caratteri che si desidera ritagliare dall’inizio e dalla fine della stringa di input. Nel nostro caso, tutto quello che dovete fare è specificare ‘/\’ come questa parte, in questo modo:

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

questo è un bel miglioramento significativo rispetto alla soluzione precedente!

CONCAT e CONCAT_WS

Se hai lavorato con T-SQL per un po ‘ sai quanto sia imbarazzante gestire i NULL quando hai bisogno di concatenare le stringhe. Come esempio, si consideri la posizione dei dati registrati per i dipendenti di risorse umane.Tabella impiegati:

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

Questa query genera il seguente 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

si Noti che per alcuni dipendenti della regione parte è ininfluente e irrilevante regione è rappresentata da un valore NULL. Supponiamo che sia necessario concatenare le parti di posizione (paese, regione e città), utilizzando una virgola come separatore, ma ignorando le regioni NULLE. Quando la regione è rilevante, si desidera che il risultato abbia il modulo <coutry>,<region>,<city>e quando la regione è irrilevante si desidera che il risultato abbia il modulo <country>,<city>. Normalmente, concatenare qualcosa con un NULL produce un risultato NULL. È possibile modificare questo comportamento disattivando l’opzione di sessione CONCAT_NULL_YIELDS_NULL, ma non consiglierei di abilitare il comportamento non standard.

Se non sapevo dell’esistenza di CONCAT e CONCAT_WS funzioni, si sarebbe probabilmente utilizzato ISNULL o si UNISCONO per sostituire un NULL con una stringa vuota, in questo modo:

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

di seguito il risultato di questa 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 ha introdotto la funzione CONCAT. Questa funzione accetta un elenco di input di stringhe di caratteri e li concatena, e mentre lo fa, ignora i NULL. Quindi usando CONCAT puoi semplificare la soluzione in questo modo:

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

Tuttavia, è necessario specificare esplicitamente i separatori come parte della funzione di ingressi. Per rendere la nostra vita ancora più facile, SQL Server 2017 ha introdotto una funzione simile chiamata CONCAT_WS in cui si inizia indicando il separatore, seguito dagli elementi che si desidera concatenare. Con questa funzione la soluzione è ulteriormente semplificata in questo modo:

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

Il passo successivo è ovviamente mindreading. Il 1 ° aprile 2020 Microsoft sta pianificando di rilasciare CONCAT_MR. La funzione accetterà un input vuoto e capirà automaticamente quali elementi vuoi che concatenino leggendo la tua mente. La query sarà quindi simile a questa:

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

LOG ha un secondo parametro

Simile alla funzione EOMONTH, molte persone non si rendono conto che già a partire da SQL Server 2012, la funzione di registrazione supporta un secondo parametro che permette di indicare il logaritmo di base. Prima di ciò, T-SQL supportava la funzione LOG (input) che restituisce il logaritmo naturale dell’input (usando la costante e come base) e LOG10(input) che usa 10 come base.

Non essendo a conoscenza dell’esistenza del secondo parametro per la funzione di LOG, quando le persone volevano calcolare Logb(x), dove b è una base diversa da e e 10, spesso lo facevano a lungo. Si poteva contare sulla seguente equazione:

ad esempio, per calcolare Log2(8), ci si basa sulla seguente equazione:

Tradotto per T-SQL, si applica il seguente calcolo:

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

una Volta che ti rendi conto che il REGISTRO supporta un secondo parametro in cui si indica la base, il calcolo diventa semplicemente:

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

Cursore variabile

Se hai lavorato con T-SQL per un po’, probabilmente aveva un sacco di possibilità di lavorare con i cursori. Come sapete, quando si lavora con un cursore, in genere si utilizzano i seguenti passaggi:

• Dichiarare il cursore
• Aprire il cursore
• Scorrere i record del cursore
• Chiudere il cursore
• Deallocare il cursore

Ad esempio, supponiamo che sia necessario eseguire alcune attività per database nella tua istanza. Utilizzo di un cursore, è opportuno usare un codice simile al seguente:

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;

Il comando CHIUDI rilascia l’attuale set di risultati e libera i blocchi. Il comando DEALLOCATE rimuove un riferimento cursore e, quando l’ultimo riferimento viene deallocato, libera le strutture dati che compongono il cursore. Se provi a eseguire il codice precedente due volte senza i comandi CLOSE e DEALLOCATE, otterrai il seguente errore:

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.

Assicurati di eseguire i comandi CLOSE e DEALLOCATE prima di continuare.

Molte persone non si rendono conto che quando hanno bisogno di lavorare con un cursore in un solo batch, che è il caso più comune, invece di usare un cursore normale puoi lavorare con una variabile cursore. Come ogni variabile, l’ambito di una variabile cursore è solo il batch in cui è stato dichiarato. Ciò significa che non appena un batch termina, tutte le variabili scadono. Utilizzando una variabile cursore, una volta terminato un batch, SQL Server lo chiude e lo dealloca automaticamente, risparmiando la necessità di eseguire esplicitamente il comando CHIUDI e DEALLOCA.

Ecco il codice rivisto usando una variabile cursore questa volta:

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;

Sentitevi liberi di eseguirlo più volte e notare che questa volta non si ottiene alcun errore. È solo più pulito e non devi preoccuparti di mantenere le risorse del cursore se hai dimenticato di chiudere e deallocare il cursore.

UNISCI con OUTPUT

Dall’inizio della clausola OUTPUT per le istruzioni di modifica in SQL Server 2005, si è rivelato uno strumento molto pratico ogni volta che si desiderava restituire dati da righe modificate. Le persone utilizzano questa funzione regolarmente per scopi come l’archiviazione, il controllo e molti altri casi d’uso. Una delle cose fastidiose di questa funzione, tuttavia, è che se la si utilizza con le istruzioni INSERT, è consentito solo restituire i dati dalle righe inserite, precedendo le colonne di output con inserted. Non è possibile accedere alle colonne della tabella di origine, anche se a volte è necessario restituire colonne dall’origine insieme a colonne dalla destinazione.

Ad esempio, considera le tabelle T1 e T2, che crei e popoli eseguendo il seguente codice:

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

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;

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