Introduzione a Transact SQL (T-SQL)

SQL è un linguaggio comune utilizzato nel mondo dei database. La maggior parte dei prodotti RDBMS moderni utilizza una sorta di dialetto SQL come linguaggio di query principale. SQL può essere utilizzato per creare o distruggere oggetti come tabelle su un server di database e per manipolare tali oggetti, ad esempio aggiungendo, modificando, eliminando dati su di essi o recuperando dati da essi.

Transact-SQL (T-SQL) è una versione di SQL ereditata e sviluppata da Microsoft, chiamata T-SQL, questo linguaggio è implementato per implementare un modo standardizzato di comunicare con i database.

Transact-SQL è un potente linguaggio che fornisce funzionalità come tipi di dati, oggetti temporanei e stored procedure estese. Transact-SQL supporta anche i cursori scorrevoli, la gestione condizionale, il controllo delle transazioni, le eccezioni e la gestione degli errori.

T-SQL in SQL Server 2019 migliora le prestazioni rispetto alle versioni precedenti, aumentando le funzionalità e supportando molte funzionalità avanzate. I miglioramenti includono funzioni scalari, impaginazione, sequenziamento, allocazione dei metadati e un migliore supporto per la gestione degli errori…

Gli esempi seguenti utilizzano tutti AdventureWorks2019, installano AdventureWorks2019 sul computer per testare gli esempi.

Si consideri l’istruzione Transact-SQL di seguito, che è un’istruzione SELECT, utilizzata per recuperare i dati della colonna loginID della tabella dipendenti con JobTitle come ‘Designer Engineer’ nella tabella Employee:

USE AdventureWorks2019 SELECT LoginID FROM HumanResources.Employee WHERE JobTitle = 'Design Engineer'

T-SQL include molti elementi sintattici utilizzati o che influenzano la maggior parte delle istruzioni. Questi elementi includono tipi di dati, predicati, variabili di funzione, espressioni, controllo di flusso, commenti e separatori di batch.

Elenco dei tipi di istruzioni T-SQL

SQL Server supporta 3 tipi di istruzioni T-SQL: DDL, DML e DCL

Linguaggio di definizione dei dati (DDL)

DDL, che fa parte dell’RDBMS, viene utilizzato per definire e gestire tutte le proprietà dei dati, inclusi il layout delle righe, le definizioni delle colonne, le colonne delle chiavi primarie, le posizioni dei file e l’archiviazione. I comandi DDL vengono utilizzati per costruire e modificare la struttura di tabelle e oggetti come trigger di viste, procedure di archiviazione… Per ogni oggetto ci sono le parole chiave CREATE , ALTER , DROP . Esempio: CREATE TABLE, ALTER TABLE, DROP TABLE

La maggior parte delle istruzioni DDL è conforme a uno standard, dove nome_oggetto è il nome della tabella, vista, trigger, procedura memorizzata… come segue:

• CREA nome_oggetto
• ALTER nome_oggetto
• DROP nome_oggetto

Linguaggio di manipolazione dei dati (DML)

DML viene utilizzato per selezionare (recuperare), inserire (aggiungere), aggiornare (aggiornare) o eliminare (eliminare) i dati negli oggetti (oggetti) definiti da DDL. Tutti gli utenti nel database possono utilizzare queste istruzioni nelle operazioni sul database.

Dichiarazioni DML che includono le seguenti parole chiave:

• SELEZIONARE
• INSERIRE
• AGGIORNARE
• ELIMINA

Linguaggio di controllo dati (DCL)

I dati sono una parte importante del database, quindi è necessario adottare misure appropriate per verificare che nessun utente non valido abbia accesso ai dati. Un linguaggio di controllo dei dati viene utilizzato per controllare le autorizzazioni sugli oggetti del database. Le autorizzazioni sono controllate tramite le istruzioni GRANT, REVOKE, DENY. Le istruzioni DCL vengono utilizzate anche per proteggere il database. Le tre istruzioni DCL di base sono le seguenti:

• CONCESSIONE (assegnare)
• REVOCA (richiamo)
• NEGARE (rifiutare)

Tipi di dati

Il tipo di dati è un attributo che definisce il tipo di dati o oggetti che possono essere contenuti. I tipi di dati devono essere forniti per colonne, parametri, variabili, valori di dati restituiti da funzioni e procedure memorizzate con ritorno. T-SQL include numerosi tipi di dati come varchar,text,int… Tutti i dati archiviati in SQL Server devono essere compatibili con uno dei tipi di dati di base.

Gli oggetti hanno tipi di dati:

• Le colonne (colonne) rappresentano in tabelle e viste
• parametri nelle procedure memorizzate
• Variabili (variabili)
• funzione che restituisce uno o più valori con un tipo di dati specificato
• Stored procedure che restituiscono codice di tipo intero

SQL Server supporta 3 tipi di tipi di dati come segue:

Tipi di dati definiti dal sistema

Questo tipo di dati è fornito da SQL Server, mostrato nella tabella seguente:

Categoria	Tipo di dati	Colonna di questo tipo di dati
Numerici esatti (numeri esatti/numeri naturali)	int	Occupa 4 byte di memoria, utilizzata per memorizzare valori interi, può memorizzare valori da -2^31(-2,147,483,648) a 2^31-1 (2,147,483,647)
	piccolo	Occupa 2 byte di memoria, può memorizzare valori interi da -32.768 a 32m.767
	minuscolo	Occupa 1 byte di memoria, contiene valori da 0 a 255
	grande	Occupa 8 byte di memoria. Contiene dati da -2^63 a 2^63-1
	numerico	ha una precisione e una scala fisse
	i soldi	occupa 8 byte di spazio di memoria. Rappresenta i valori dei dati di valuta compresi tra -2^63/1000 e 2^63-1
Numeri approssimativi (numeri approssimativi/reali)	galleggiante	Occupa 8 byte di memoria. Rappresentazione numerica dopo virgola mobile da -1,79E+308 a 1,79E+38
	vero	Occupa 4 byte di memoria. Rappresenta i numeri dopo la virgola mobile da -3,40E+38 a 3,40E+38
Data e ora	appuntamento	Rappresenta la data e l’ora, occupa 8 byte in memoria (2 volte 4 byte – intero)
	smalldatetime	Prestazioni data ora
Stringa di caratteri	car	Memorizza i dati dei caratteri con lunghezza specificata e non Unicode
	varcar	Memorizza dati di caratteri a lunghezza variabile e non Unicode fino a 8.000 caratteri
	testo	Memorizza dati di caratteri a lunghezza variabile e non Unicode con una lunghezza massima di 2^31 – 1(2.147.483.647) caratteri
	nchar	Memorizza i caratteri Unicode con una lunghezza specificata
	nvarchar	Memorizza i dati dei caratteri unicode variabili.
Altri tipi di dati	timestamp	Occupa 8 byte di memoria. I valori memorizzati possono essere generati automaticamente, valori numerici binari univoci generati e archiviati in un database di simulazione di timestamp unix in tempo reale.
	binario(n)	Memorizza dati binari di una lunghezza specificata, fino a 8000 byte.
	varbinary(n)	Memorizza dati binari con lunghezza variabile, fino a 8000 byte.
	Immagine	Memorizza un valore binario variabile con una lunghezza massima di 2^30-1 (1.073.741.823) byte.
	identificativo unico	Occupando 16 byte di memoria, genererà automaticamente un valore univoco che è l’identificatore univoco globale (GUID)

Tipi di dati alias (tipi di dati alias)

Questi tipi di dati si basano sui tipi di dati forniti dal sistema. Un tipo di dati alias viene utilizzato quando più tabelle archiviano lo stesso tipo di dati in una colonna e hanno caratteristiche simili come lunghezza, capacità di valori Null e tipo di dati. In questi casi, è possibile creare un tipo di dati alias che può essere utilizzato universalmente da tutte queste tabelle.

I tipi di dati alias possono essere creati tramite l’istruzione CREATE TYPE. La sintassi dell’istruzione CREATE TYPE è la seguente:

CREATE TYPE [schema_name.]type_name FROM base_type [NULL/NOTNULL];

Per esempio:

CREATE TYPE usertype from varchar(20) NOT NULL

Tipi definiti dall’utente

Gli utenti possono definire i propri tipi di dati utilizzando un linguaggio di programmazione supportato da .NET Framework.

Elementi del linguaggio Transact-SQL

Gli elementi del linguaggio Transact-SQL vengono usati in SQL Server 2019 per lavorare sui dati importati nei database di SQL Server. Gli elementi del linguaggio Transact-SQL includono predicati, operatori, variabili, funzioni, espressioni, controllo del flusso ed errori (errore) e transazioni, commenti e separatori batch.

Predicati (predicati)

I predicati possono essere intesi come un’espressione logica utilizzata per valutare se l’espressione restituisce VERO, FALSO o SCONOSCIUTO.

Le clausole in Transact-SQl sono utilizzate nei seguenti casi:

• Determina se un valore specificato corrisponde a qualsiasi valore in una sottoquery o in un elenco
• Specifica un intervallo di valori da controllare
• Utilizzato per abbinare i caratteri con un modello specificato
• Cerca corrispondenze esatte o meno esatte con singole parole e frasi, parole entro una certa distanza l’una dall’altra o corrispondenze ponderate

clausole predicative di esempio:

Predicato	Per esempio
STAMPA	SELECT PersonType, Title, FirstName, LastName FROM AdventureWorks2019.Person.Person WHERE PersonType IN (“EM”, “SC”)
FRA	SELEZIONA BusinessEntityID, NationalIDNumber, LoginID, JobTitle, HireDate DA AdventureWorks2019.HumanResources.Employee DOVE HireDate TRA ’01-01-2010′ E ’01-01-2013′
PIACE	SELECT DepartmentID, Name, GroupName, ModifiedDate FROM AdventureWorks2019.HumanResources.Department WHERE Nome LIKE ‘P%’
CONTIENE	SELECT * FROM AdventureWorks2019.Person.Address DOVE CONTIENE (AddressLine1,’Street’)

Operatori (operatori)

Gli operatori vengono utilizzati per eseguire operazioni aritmetiche, confronti, concatenazioni o assegnazioni di valori. Ad esempio, i dati possono essere controllati per verificare che la colonna PAESE per i dati del cliente sia compilata (o abbia un valore NOT NULL). Nelle query, chiunque possa vedere i dati nella tabella che richiede l’operatore può eseguire operazioni. Le autorizzazioni appropriate sono necessarie prima che i dati possano essere modificati correttamente. SQL Server dispone di sette tipi di operatori:

Operatore	descrizione	Esempio
Confronto	Confronta un valore con un altro valore un’espressione	=,<,>,>=,<=,!=,!>
Logico	Verifica del risultato vero (logico) di una condizione	E, O, NON
Aritmetica	Eseguire operazioni aritmetiche come addizione, sottrazione, moltiplicazione e divisione	+,-,*,/,%
Concatenazione	Unisci 2 catenelle in una corda	+
Incarico	Assegna un valore a una variabile	=

L’ordine di precedenza degli operatori:

Ordine	Operatore
primo	() Parentesi
2	*, / , %
3	+ , –
4	= , < , > , >= , <= , != , !>
5	NON
6	E
7	TRA, IN , CONTIENE, COME O
8	=

Esempio di precedenza dell’operatore:

DECLARE @Number int; SET @Number = 2 + 2 * (4 + (5 - 3)) SELECT @Number

Il risultato visualizzerà 14, con l’ordine di esecuzione come segue:

1. 2 + 2*(4+(5-3)) 2. 2 + 2*(4+2) 3. 2 + 2*6 4. 2 + 12 5. 14

Funzioni (funzioni)

Una funzione è un insieme di istruzioni, un’istruzione T-SQL consiste in un insieme di funzioni molto utili per calcolare o lavorare con i dati. In SQL, la funzione lavora con i dati, raggruppa i dati per restituire il valore richiesto e quindi può utilizzare la clausola SELECT per ottenere i dati restituiti dall’espressione

Esistono 4 tipi di funzioni in SQL Server come segue:

Funzioni di set di righe

in transact-SQL, la funzione rowet viene utilizzata per restituire un oggetto che può essere utilizzato al posto di un riferimento a una tabella. Ad esempio, OPENDATASOURCE, OPENQUERY, OPENROWSET e OPENXML sono funzioni di set di righe.

Funzioni aggregate

T-SQL fornisce una serie di funzioni per supportare il riepilogo di grandi volumi di dati, ad esempio SUM, MIN, MAX, AVG, COUNT, COUNTBIG…

Funzioni di classifica

L’elaborazione di molte attività, come la creazione di array, la generazione di numeri ordinali, la ricerca di ranghi, ecc. può essere eseguita in modo più semplice e veloce utilizzando le funzioni di classificazione. Ad esempio, RANK,DENSE_RANK,NTILE, ROW_NUMBER sono funzioni di classificazione.

Funzioni scalari

Nelle funzioni scalari, l’input è un valore singolo e anche l’output è un valore singolo

Alcune funzioni scalari in SQL:

Tipo di funzione	Descrizione	Esempio _
Funzione di conversione	La funzione di conversione viene utilizzata per convertire il valore di un tipo di dati in un altro. Inoltre, può essere utilizzato per ottenere formati di data speciali.	CONVERTIRE
Funzione data e ora	La funzione datetime viene utilizzata per lavorare con i dati di data e ora, utili per calcolare il tempo	GETDATE, SYSDATETIME, GETUTCDATE, DATEADD, DATEDIFF, ANNO, MESE, GIORNO
Funzioni matematiche	Le funzioni matematiche eseguono operazioni algebriche su valori numerici.	RAND, ROUND, POWER, ABS, CELLING, PAVIMENTO
Funzioni di sistema	SQL Server fornisce funzioni di sistema che restituiscono metadati o impostazioni di configurazione	HOST_ID, HOST_NAME, ISNULL
Funzioni di stringa	Le funzioni stringa vengono utilizzate per gestire input come char o nvarchar. L’output può essere una stringa o un valore numerico	SUBSTRING, SINISTRA, DESTRA, LEN, DATALENGTH, SOSTITUZIONE, REPLICA, SUPERIORE, INFERIORE, RTRIM, LTRIM

Ci sono anche altre funzioni scalari in SQL Server come funzioni cursore, funzioni logiche, funzioni di metadati, funzioni di sicurezza…

Variabili (variabili)

Una variabile è un oggetto che può memorizzare valori di dati. In T-SQL, le variabili possono essere suddivise in variabili locali e variabili globali.

In T-SQL le variabili locali vengono create e utilizzate per l’archiviazione temporanea quando vengono eseguite le istruzioni SQL. I dati possono essere passati tramite istruzioni SQL tramite variabili locali. Il nome di una variabile locale è sempre preceduto dalla parola chiave ‘@’.

Per esempio:

DECLARE @Search NVARCHAR(30) SET @Search = N'hello'

Nelle versioni precedenti di SQL Server esisteva un concetto chiamato variabili globali, che fa riferimento a variabili predefinite definite e gestite dal sistema. In SQL Server 2019 le sostituzioni sono classificate come funzioni. Sono preceduti da due segni “@”. I valori di ritorno di queste funzioni possono essere recuperati con una semplice query SELECT.

Per esempio

SELECT @@LANGUAGE as 'Language'

Restituiranno la lingua utilizzata da SQL Server

Alcune delle funzioni che si possono incontrare:

• @@DATEFIRST
• @@LINGUAGGIO
• @@LOCK_TIMEOUT
• @@MAX_CONNECTIONS
• @@NOME DEL SERVER
• @@VERSIONE

Espressione (espressione)

Un’espressione è una combinazione di identificatori, valori e operatori che SQL Server può valutare per ottenere un risultato. Le espressioni possono essere utilizzate in diversi luoghi durante l’accesso o la modifica dei dati.

Esempio di un’espressione che combina la clausola SELECT, ottenendo l’anno corrente e calcolando l’anno successivo

USE AdventureWorks2019 SELECT SalesOrderID, CustomerID, SalesPersonID, TerritoryID, YEAR(OrderDate) AS CurrentYear, YEAR(OrderDate) + 1 AS NextYear FROM Sales.SalesOrderHeader

Controllo di Flusso, Errori, Transazioni

Sebbene transact-SQL sia principalmente un linguaggio di accesso ai dati, supporta il controllo del flusso per l’esecuzione e il rilevamento degli errori.Il controllo del flusso definisce il flusso di esecuzione di istruzioni transact-SQL, blocchi di codici, funzioni definite dall’utente e procedure di archiviazione.

Controlli comuni di flusso in T-SQL

Dichiarazione di controllo del flusso	descrizione
SE ALTRO	Controllo della ramificazione in base alla condizione logica
MENTRE	Ripetere comandi o blocchi di istruzioni mentre la condizione di test è vera
INIZIO… FINE	Definizione dell’ambito di un blocco T-SQL
PROVA A PRENDERE	Definizione della struttura per la gestione delle eccezioni e degli errori
INIZIA LA TRANSAZIONE	Contrassegna un blocco di istruzioni come parte di una transazione esplicita

Esempio di utilizzo di SE ELSE in T-SQL:

IF DATENAME(weekday, GETDATE()) IN (N'Saturday',N'Sunday') SELECT 'It is a Weekend'; ELSE SELECT 'It is Weekday';

Commenti

I commenti sono stringhe di testo descrittivo, note anche come commenti, nel codice del programma che verranno ignorate dal compilatore. I commenti possono essere inseriti all’interno del codice sorgente di un’istruzione, un blocco di codice o una procedura del negozio. I commenti spiegano lo scopo del programma, le condizioni speciali di esecuzione e forniscono informazioni sulla cronologia delle revisioni…. Sintassi:

-- Day la comment inline -- Day la comment inline /*day la comment khoi lenh*/

Separatori di lotti

Batch è un insieme di una o più istruzioni T-SQL inviate in una singola esecuzione dell’applicazione. Le istruzioni in T-SQL in un batch vengono riconfezionate in un’unità di esecuzione (un’esecuzione), denominata piano di esecuzione. Il processo di esecuzione dei set di istruzioni all’interno del batch è chiamato elaborazione batch.

Un separatore batch è controllato dagli strumenti client di SQL Server, ad esempio SSMS, per eseguire il comando. Ad esempio, definisci GO come separatore batch in SSMS.

Esempio di separatore batch:

USE AdventureWorks2019 SELECT * FROM HumanResources.Employee GO -- cau lenh tiep theo

Insiemi e logica predicativa (insiemi e logica predicata)

Gli insiemi e la logica predicativa sono 2 fondamenti matematici utilizzati in SQL Server 2019. Entrambe queste teorie vengono utilizzate per eseguire query sui dati in SQL Sever 2019

Teoria degli insiemi (teoria degli insiemi)

La teoria degli insiemi è una base matematica utilizzata nel modello di database relazionale. Un set è una raccolta di oggetti distinti considerati nel loro insieme. Ad esempio, tutti i dipendenti in una tabella Dipendenti possono essere considerati come un insieme.

Applicazioni di teoria degli insiemi	Applicazioni nelle query di SQL Server
Agisci sull’intero set in una volta	Interroga l’intera tabella in una volta
Gestione e dichiarazione basata su insiemi	Utilizzare le proprietà nel server SQL per recuperare dati specifici
Gli elementi nel set devono essere univoci	Definisci una chiave univoca per la tabella
Nessuna istruzione di smistamento	I risultati della query non vengono recuperati in alcun ordine

Uno degli operatori di insiemi è l’operatore INTERSECT. Restituisce le righe distinte generate dagli operatori di query di input sinistro e destro.

Esempio di utilizzo di INTERSECT:

USE AdventureWorks2019 GO SELECT ProductID FROM Production.Product INTERSECT SELECT ProductID FROM Production.WorkOrder;

Logica predicata (logica predicata)

Vedi più concetto di predicato logico qui: https://en.wikipedia.org/wiki/Logic_b%E1%BA%ADc_nh%E1%BA%A5t

La logica predicata è un quadro matematico costituito da test logici che danno un risultato. Il risultato viene sempre visualizzato come vero o falso. In T-SQL, espressioni come WHERE e CASE si basano sulla logica dei predicati. La logica predicata viene utilizzata anche in altre situazioni in T-SQL

Alcune logiche predicate in T-SQL sono le seguenti:

• imporre la riservatezza dei dati utilizzando un vincolo CHECK
• Controllo del flusso tramite l’istruzione IF
• Unisci le tabelle usando il filtro ON
• Filtra i dati nelle query utilizzando le clausole WHERE e HAVING
• Fornisce la logica condizionale per le espressioni CASE
• Definisci sottoquery

Ordine logico degli operatori in un’istruzione SELECT

Oltre alla sintassi dei vari elementi di SQL Server, gli utenti di SQL Server devono anche sapere come viene eseguita l’intera query. Questa procedura è un processo logico che interrompe la query ed esegue la query in una sequenza predefinita. L’istruzione SELECT è una query che verrà utilizzata per spiegare il processo logico di esecuzione della query.

Sintassi dell’istruzione SELECT:

SELECT <select list> FROM <table source> WHERE <search condition> GROUP BY <group by list> HAVING <search condition> ORDER BY <order by list>

Descrizione degli elementi nell’istruzione SELECT:

Elemento	Descrivere
SELEZIONA <seleziona elenco>	Definire le colonne da recuperare
DA <fonte tabella>	Definizione di tabella interrogata
WHERE <condizione di ricerca>	Filtra le righe per predicato
GROUP BY <raggruppa per elenco>	Ordina le righe per gruppo
AVENDO <condizione di ricerca>	Filtra i gruppi per predicato
ORDINA PER <ordina per lista>	Ordina output.

Considera l’esempio seguente:

USE AdventureWorks2019 SELECT SalesPersonID,YEAR(OrderDate) AS OrderYear FROM Sales.SalesOrderHeader WHERE CustomerID=30084 GROUP BY SalesPersonID, YEAR(OrderDate) HAVINg COUNT(*) > 1 ORDER BY SalesPersonID,OrderYear;

Nell’esempio precedente, la sequenza di esecuzione dell’istruzione SELECT è la seguente:

1. La clausola FROM viene valutata per determinare la tabella di origine da interrogare
2. La clausola WHERE viene valutata per filtrare le righe nella tabella di origine, definita dal predicato menzionato dopo la clausola WHERE.
3. Successivamente, viene valutata la clausola GROUP BY. Questa clausola ordina i dati filtrati nella clausola WHERE
4. Viene valutata la clausola HAVING
5. La clausola SELECT viene eseguita per specificare quali colonne devono restituire lo stesso risultato della query
6. Infine, l’istruzione ORDER BY viene eseguita per visualizzare l’output

USE AdventureWorks2019 -- thu tu thuc thi cua cau lenh select 5.SELECT SalesPersonID,YEAR(OrderDate) AS OrderYear FROM 1.Sales.SalesOrderHeader 2.WHERE CustomerID=30084 3.GROUP BY SalesPersonID, YEAR(OrderDate) 4.HAVINg COUNT(*) > 1 6.ORDER BY SalesPersonID,OrderYear;