Archivio Categorie: In evidenza

In questo articolo parleremo di tre nuove e utilissime funzioni disponibili dalla versione 2017 di Microsoft Sql Server rendendo ancora più semplice e immediata l’esperienza di sviluppo dei programmatori T-SQL.   Funzione TRIM Fino alla versione 2016 di Sql Server, per eliminare gli spazi bianchi a sinistra e a destra di una stringa, occorreva combinare le due funzione LTRIM e RTRIM come nell’esempio in basso: SELECT LTRIM(RTRIM(‘  string    ‘ )) Questa sintassi risulta spesso “pesante”, soprattutto in quei contesti dove tale funzione è piuttosto frequente, pensiamo ad esempio alle fasi di data quality e integration. Finalmente, possiamo raggiungere il medesimo risultato con la sola funzione TRIM, analogamente a quanto già possibile su altri DBMS come ad esempio su Oracle. SELECT TRIM(‘  string    ‘ ) Oltre agli spazi bianchi, la funzione TRIM può essere utilizzata per eliminare anche dei caratteri particolari.  Questo permette di risolvere agevolmente problematiche molto comuni come “eliminare gli zeri a sinistra della stringa”    che, ad esempio, emergono spesso da colonne che contengono indistintamente codici fiscali e partite iva. Vediamo uno script d’esempio CREATE TABLE #test (codice varchar(10)) INSERT INTO #test VALUES (’01’),(’02’),(‘2’),(‘0’),(‘001’) SELECT Codice,                 trim(‘0’ from codice) as AfterTrim FROM   #test Codice  AfterTrim 01 1 02 2 2 2 0 001 1   Funzione CONCAT_WS Molto spesso si ha l’esigenza di concatenare più colonne separandole con una virgola oppure un altro separatore. Creiamo un semplice caso d’esempio CREATE TABLE #test2 (nome varchar(50),                 cognome varchar(50),                 citta varchar(50),                 indirizzo varchar(50)) INSERT INTO #test2 values (‘nicola’,’iantomasi’,’torino’,null), (‘giovanni’,’rossi’,’milano’,’via montenapoleone’) Per concatenare le quattro colonne dell’esempio, occorre ripetere il carattere separatore tre volte. SELECT CONCAT(nome,’,’,cognome,’,’,citta,’,’,indirizzo) as AfterConcat FROM    #test2 Se dovessimo concatenare dieci colonne, occorrerebbe ripeterlo nove volte… Tramite la funzione CONCAT_WS possiamo invece indicare il carattere separatore una sola volta, in questo modo SELECT CONCAT_WS(‘,’,nome,cognome,citta,indirizzo) as AfterConcatWs FROM     #test2 Molto importante è notare il diverso comportamento delle funzioni CONCAT e CONCAT_WS in presenza di null. La funzione ConcatWS elimina il separatore nel caso in cui la colonna seguente contenga un null. Occorre essere consapevoli di questo comportamento: probabilmente questo non è il comportamento che vogliamo se ad esempio il nostro obiettivo è convertire in formato csv i dati della tabella. Tuttavia possiamo “porre rimedio” inglobando le colonne all’interno della funzione isnull SELECT CONCAT_WS(‘,’, isnull(nome,”),isnull(cognome,”),isnull(citta,”),isnull(indirizzo”)) as AfterConcatWs from #test2 Al contrario, se volessimo creare una stringa indirizzo con esattamente questa caratteristica di “saltare” le colonne,  l’implementazione con la funzione CONCAT risulterebbe molto più lunga e frustante. Funzione STRING_AGG In presenza di una clausola group by, spesso si ha la necessità di concatenare (eventualmente con un separatore) i valori di una colonna di tipo varchar relativi alla chiave di aggregazione. Prima del 2017, per risolvere questo problema gli sviluppatori T-SQL hanno dato sfoggio della loro abilità e creatività utilizzando le più svariate tecniche: funzioni xml path, conversioni in json, variabili che si concatenano con esse stesse, eccetera… Da SQL Server 2017 abbiamo a disposizione una funzione di aggrezione utilissima per questo scopo: STRING_AGG. Niente può essere più esplicativo di uno script d’esempio CREATE TABLE #test3 (IdConto int, IdCliente int) INSERT INTO #test3 VALUES (1,1),(1,2),(2,3),(3,4),(3,null) SELECT   IdConto,                   string_agg(IdCliente,’;’) as ClientiCointestatari FROM      #test3 GROUP BY IdConto IdCconto ClientiCointestatari 1 1;2 2 3 3 4   Notiamo inoltre la conversione implicita di colonne int in varchar e il comportamento in presenza di null.   Esercizio! Combinando le due funzioni string_agg e CONCAT_WS possiamo convertire facilmente una tabella relazionale nel contenuto di un file csv.   Bonus: Funzione APPROX_COUNT_DISTINCT Nel caso di elevate quantità di dati, l’utilizzo di una COUNT(DISTINCT ) potrebbe essere deleterio in termini di performance. APPROX_COUNT_DISTINCT fornisce un valore approssimato rispetto al COUNT(DISTINCT) con delle performance migliori. Per la precisione al 97% l”errore sarà inferiore del 2% SELECT IdFatture,                 APPROX_COUNT_DISTINCT(IdCliente) AS NumeroApprossimatoDiClienti FROM   Fatture GROUP BY IdFatture Tuttavia, occorre comunque fare massima attenzione alle performance come sottolineato da Pinal Dave in questo interessante articolo.

Le librerie e il web sono piene di manuali sul linguaggio SQL, molti dei quali estremamente precisi e approfonditi. Noi vogliamo provare ad approcciarci alla didattica in maniera diversa, non soffermandoci sulla sintassi, ma sul ragionamento necessario per tradurre del linguaggio naturale in una query. Riteniamo che la natura dichiarativa dell’SQL richieda un approccio didattico differente rispetto agli altri linguaggi di programmazione. Negli anni ’70, prima di una disputa legale per ragioni di brevetto, il nome del nostro linguaggio preferito era SEQUEL. Purtroppo la caduta della “E” ha tolto evidenza ad un aspetto molto significativo del nome: la lettera “E” stava per “English” per sottolineare la stretta vicinanza tra l’SQL e la lingua inglese parlata nel quotidiano.   Come parliamo e come programmiamo? Nel libro Training Kit (Exam 70-461) Querying Microsoft SQL Server 2012 (MCSA) troviamo un esempio chiarificatore: ad un essere umano ci rivolgiamo in modo naturale con una frase del tipo “Prendi una mela dal frigorifero”. Se parlassimo ad un robot (o programmassimo in un altro linguaggio) riformuleremmo la frase in questo modo più algoritmico “Vai al frigorifero e prendi una mela” in quanto sentiremmo l’esigenza di impartire le istruzioni con un ordine più rigido. Ebbene, la programmazione in Sql ricalca il primo modo di esprimersi, quello tra esseri umani. Quando scriviamo una query dobbiamo specificare prima i nomi delle colonne (all’interno della clausola select) e solo in seguito il nome delle tabelle (nella clausola from). Con un po’ di fantasia, la query esatta sarà SELECT mela FROM    frigorifero invece di una più algoritmica FROM    frigorifero SELECT mela   Come scrivere con l’SQL Partendo da queste premesse, abbiamo creato delle presentazioni su SlideShare che accompagnano il lettore nella scrittura delle prime query in SQL. Vi riportiamo in basso la prima presentazione sui costrutti di base Select, From e Where (consigliamo la visualizzazione a schermo intero). Abbiamo cercato di utilizzare i colori per sottolineare come le richieste in linguaggio naturale siano traducibili, senza troppa fatica, in clausole Sql. Linguaggio SQL: utilizzo di select, from e where from Nicola Iantomasi  

Riprendiamo il tema trattato nell’articolo precedente molto apprezzato da voi lettori, riportanto altre cinque differenze tra la programmazione SQL su ambiente Sql Server e su ambiente Oracle.   Confronti tra un campo varchar e una stringa contenente spazi Riprendiamo lo stesso script utilizzato nello scorso articolo per creare delle tabelle di test. Ricordiamo a tal proposito di fare attenzione all’utilizzo di varchar e varchar2. Su Sql server avremo: create table TabellaTest (        campo1 varchar (50) null,        campo2 decimal(18,2) null ); insert into TabellaTest values (‘test’, 35.23) Mentre su Oracle scriveremo: create table TabellaTest (        campo1 varchar2 (50) null,       campo2 decimal(18,2) null ); insert into TabellaTest values (‘test’, 35.23) In ambiente Sql Server, cosa succede se valutiamo un campo varchar in un filtro contenuto in una clausola where? Gli eventuali spazi a destra non saranno considerati. Ad esempio la query seguente avrà comunque come output l’unico record presente nella tabella, nonostante la presenza degli spazi dopo il carattere t. select      * from      TabellaTest where    campo1=’test    ‘ Al contrario, su Oracle tali spazi vengono presi in considerazione. Di conseguenza il filtro precedente risulterà falso e quindi la query restituirà zero righe. L’esempio può sembrare artificioso, in realtà non è raro trovarsi in una situazione del genere quando confrontiamo le colonne con variabili valorizzate precedentemente. Ricordiamo infine che gli spazi a sinistra sono valutati su entrambi gli ambienti, inoltre gli spazi a destra saranno comunque sempre “salvati” nelle operazioni di inserimento.   Righe non committate Nello scorso articolo avevamo visto che by default le operazioni DML su Oracle devono essere seguite da un commit per risultare persistenti su un database (ricordiamo di fare attenzione ai commit impliciti derivanti ad esempio da operazioni DDL). Su Sql Server, invece, il commit è necessario solo se viene dichiarata esplicitamente l’apertura di una transazione. Ma cosa succede se proviamo ad interrogare, prima del commit, una tabella modificata in un’altra sessione? Su Oracle vedremmo la tabella con i valori originali, come se l’operazione non fosse stato ancora eseguita. Al contrario, su Sql Server la tabella risulta lockata: tutte le query su di essa resteranno “in running” finché la transazione non verrà chiusa tramite un commit o un rollback. Per forzare l’esecuzione di una query su tabelle lockate occorre inserire l’hint WITH (NOLOCK) nella clausola FROM, subito dopo il nome della tabella. In questo caso, a differenza di Oracle, vediamo i dati come se il commit fosse già stato eseguito.   Divisione da interi Creiamo in entrambi gli ambienti una nuova tabella con un campo di tipo int e inseriamo due record con i valori 1 e 2. create table TestInteri ( campo1 int); insert into TestInteri values (1); insert into TestInteri values(2); La query seguente restituirà due risultati differenti: select   AVG (campo1) from   TestInteri; Su Oracle l’output è dato dal numero decimale 1.5, corrispondente alla media dei valori 1 e 2 trattati anch’essi come decimali. Al contrario, su Sql Server verrà restituito l’intero 1, a meno di convertire esplicitamente (o implicitamente tramite una moltiplicazione per 1.0) la colonna. Più in generale, tali considerazione valgono anche per la divisione tra numeri interi: a meno di cast, su Sql Server il risultato sarà sempre un intero (non arrotondato), mentre su Oracle otterremo comunque il decimale “esatto”.   Concatenazione tra stringhe e interi In ambiente Oracle se concateniamo una stringa e un intero tramite l’operatore ||, l’intero verrà convertito implicitamente in stringa. Ad esempio l’output di questa query sarà la stringa a1 select  ‘a’ || 1 from   dual Al contrario, in ambiente Sql Server, l’operatore di concatenazione tra stringhe + tenterà sempre di convertire il campo varchar in intero (nel caso le colonne abbiano questi due tipi alternati). Al netto di rimuovere la tabella Dual, la query precedente restituirà dunque un’errore di conversione.   Contatenazione con null By default, su Sql Server la concatenazione con l’operatore + tra varie stringhe di cui anche una sola è null, ha come risultato null. Viene seguita la logica per cui il risultante della concatenazione tra una stringa determinata e una stringa sconosciuta risulta essere una stringa sconosciuta. Perciò risulta fondamentale trattare questi casi utilizzando ad esempio la funzione coalesce. Su Oracle la sitazione cambia: il null lavora come elementro neutro della concatenazione, di conseguenza non impatta il valore degli altri campi.   Articoli correlati Leggi il prossimo articolo sull’analisi del piano di esecuzione di una query.