Usare funzioni scalari
Le funzioni scalari restituiscono un singolo valore e vengono in genere usate in una singola riga di dati. Il numero di valori di input accettati può essere zero (ad esempio GETDATE), uno (ad esempio UPPER) o più valori (ad esempio ROUND). Poiché le funzioni scalari restituiscono sempre un singolo valore, possono essere usate ovunque sia necessario un singolo valore (risultato). Vengono usate più comunemente nelle clausole SELECT e nei predicati della clausola WHERE. Possono essere usate anche nella clausola SET di un'istruzione UPDATE.
Le funzioni scalari predefinite possono essere organizzate in molte categorie, ad esempio stringa, conversione, logica, matematica e altre. In questo modulo verranno descritte alcune funzioni scalari comuni.
Di seguito sono riportate alcune considerazioni relative all'uso delle funzioni scalari:
- Determinismo: se la funzione restituisce lo stesso valore per lo stesso input e lo stesso stato del database ogni volta che viene chiamata, la funzione è definita deterministica. Ad esempio, ROUND(1.1, 0) restituisce sempre il valore 1.0. Numerose funzioni predefinite sono non deterministiche. Ad esempio, GETDATE() restituisce la data e l'ora correnti. I risultati delle funzioni non deterministiche non possono essere indicizzati, il che influisce sulla capacità di Query Processor di creare un piano valido per l'esecuzione della query.
- Regole di confronto: quando si usano funzioni che modificano dati di tipo carattere, quali regole di confronto verranno usate? Alcune funzioni usano le regole di confronto (ordinamento) del valore di input. Altre usano le regole di confronto del database se non vengono specificate regole di confronto di input.
Esempi di funzioni scalari
Al momento della stesura di questo articolo, la documentazione tecnica di SQL Server elenca più di 200 funzioni scalari che rientrano in più categorie, tra cui:
- Funzioni di configurazione
- Funzioni di conversione
- Funzioni cursori
- Funzioni di data e ora
- Funzioni matematiche
- Funzioni di metadati
- Funzioni di sicurezza
- Funzioni di stringa
- Funzioni di sistema
- Funzione di statistiche di sistema
- Funzione di testo e immagine
In questo corso non c'è tempo sufficiente per descrivere ogni funzione, ma gli esempi seguenti illustrano alcune funzioni di uso comune.
L'esempio ipotetico seguente usa diverse funzioni di data e ora:
SELECT SalesOrderID,
OrderDate,
YEAR(OrderDate) AS OrderYear,
DATENAME(mm, OrderDate) AS OrderMonth,
DAY(OrderDate) AS OrderDay,
DATENAME(dw, OrderDate) AS OrderWeekDay,
DATEDIFF(yy,OrderDate, GETDATE()) AS YearsSinceOrder
FROM Sales.SalesOrderHeader;
I risultati parziali sono mostrati di seguito:
SalesOrderID
OrderDate
OrderYear
OrderMonth
OrderDay
OrderWeekDay
YearsSinceOrder
71.774
2008-06-01T00:00:00
2008
Giugno
1
Domenica
13
...
...
...
...
...
...
...
L'esempio successivo include alcune funzioni matematiche:
SELECT TaxAmt,
ROUND(TaxAmt, 0) AS Rounded,
FLOOR(TaxAmt) AS Floor,
CEILING(TaxAmt) AS Ceiling,
SQUARE(TaxAmt) AS Squared,
SQRT(TaxAmt) AS Root,
LOG(TaxAmt) AS Log,
TaxAmt * RAND() AS Randomized
FROM Sales.SalesOrderHeader;
Risultati parziali:
TaxAmt
Rounded
Piano
Arrotonda eccesso
Squared
Radice
Log
Casuale
70.4279
70.0000
70.0000
71.0000
4960.089098
8.392133221
4.254589491
28.64120429
...
..
...
...
...
...
...
...
L'esempio seguente usa alcune funzioni di stringa:
SELECT CompanyName,
UPPER(CompanyName) AS UpperCase,
LOWER(CompanyName) AS LowerCase,
LEN(CompanyName) AS Length,
REVERSE(CompanyName) AS Reversed,
CHARINDEX(' ', CompanyName) AS FirstSpace,
LEFT(CompanyName, CHARINDEX(' ', CompanyName)) AS FirstWord,
SUBSTRING(CompanyName, CHARINDEX(' ', CompanyName) + 1, LEN(CompanyName)) AS RestOfName
FROM Sales.Customer;
Risultati parziali:
CompanyName
Maiuscolo
Minuscolo
Length
Reversed
FirstSpace
FirstWord
RestOfName
A Bike Store
A BIKE STORE
a bike store
12
erotS ekiB A
2
A
Bike Store
Progressive Sports
PROGRESSIVE SPORTS
progressive sports
18
stropS evissergorP
12
Progressiva
Sport
Componenti avanzati per biciclette
ADVANCED BIKE COMPONENTS
advanced bike components
24
stnenopmoC ekiB decnavdA
9
Avanzato
Bike Components
...
...
...
...
...
...
...
...
Funzioni logiche
Un'altra categoria di funzioni consente di determinare quale dei diversi valori deve essere restituito. Le funzioni logiche valutano un'espressione di input e restituiscono un valore appropriato in base al risultato.
IIF
La funzione IIF valuta un'espressione di input booleana e restituisce un valore specificato se l'espressione restituisce True e un valore alternativo se l'espressione restituisce False.
Ad esempio, si consideri la query seguente che valuta il tipo di indirizzo di un cliente. Se il valore è "Main Office", l'espressione restituisce "Billing". Per tutti gli altri valori di tipo indirizzo, l'espressione restituisce "Mailing".
SELECT AddressType,
IIF(AddressType = 'Main Office', 'Billing', 'Mailing') AS UseAddressFor
FROM Sales.CustomerAddress;
I risultati parziali di questa query potrebbero essere simili ai seguenti:
AddressType
UseAddressFor
Main Office
Fatturazione
Spedizione
Mailing
...
...
CHOOSE
La funzione CHOOSE valuta un'espressione integer e restituisce il valore corrispondente da un elenco in base alla relativa posizione ordinale (in base 1).
SELECT SalesOrderID, Status,
CHOOSE(Status, 'Ordered', 'Shipped', 'Delivered') AS OrderStatus
FROM Sales.SalesOrderHeader;
I risultati di questa query potrebbero essere simili ai seguenti:
SalesOrderID
Stato
OrderStatus
1234
3
Consegnati
1235
2
Consegnato
1236
2
Consegnato
1237
1
Quantità ordinata
...
...
...