DECLARE @local_variable (Transact-SQL)
Si applica a:
SQL Server database SQL di Azure Istanza gestita di SQL di Azure endpoint di analisi SQL di Azure Synapse Analytics Platform System (PDW) in Microsoft Fabric Warehouse nel database SQL di Microsoft Fabric in Microsoft Fabric
Le variabili vengono dichiarate nel corpo di un batch o di una routine tramite l'istruzione DECLARE e i relativi valori vengono assegnati tramite un'istruzione SET o SELECT. È possibile dichiarare variabili di cursore con questa istruzione e utilizzarle insieme ad altre istruzioni correlate ai cursori. Dopo la dichiarazione, tutte le variabili vengono inizializzate con valore NULL, a meno che non venga fornito un valore nella dichiarazione.
Convenzioni relative alla sintassi Transact-SQL
Sintassi
La sintassi seguente è per SQL Server e database SQL di Azure:
DECLARE
{
{ @local_variable [AS] data_type [ = value ] }
| { @cursor_variable_name CURSOR }
} [ ,...n ]
| { @table_variable_name [AS] <table_type_definition> }
<table_type_definition> ::=
TABLE ( { <column_definition> | <table_constraint> | <table_index> } } [ ,...n ] )
<column_definition> ::=
column_name { scalar_data_type | AS computed_column_expression }
[ COLLATE collation_name ]
[ [ DEFAULT constant_expression ] | IDENTITY [ (seed, increment ) ] ]
[ ROWGUIDCOL ]
[ <column_constraint> ]
[ <column_index> ]
<column_constraint> ::=
{
[ NULL | NOT NULL ]
{ PRIMARY KEY | UNIQUE }
[ CLUSTERED | NONCLUSTERED ]
[ WITH FILLFACTOR = fillfactor
| WITH ( < index_option > [ ,...n ] )
[ ON { filegroup | "default" } ]
| [ CHECK ( logical_expression ) ] [ ,...n ]
}
<column_index> ::=
INDEX index_name [ CLUSTERED | NONCLUSTERED ]
[ WITH ( <index_option> [ ,... n ] ) ]
[ ON { partition_scheme_name (column_name )
| filegroup_name
| default
}
]
[ FILESTREAM_ON { filestream_filegroup_name | partition_scheme_name | "NULL" } ]
<table_constraint> ::=
{
{ PRIMARY KEY | UNIQUE }
[ CLUSTERED | NONCLUSTERED ]
( column_name [ ASC | DESC ] [ ,...n ]
[ WITH FILLFACTOR = fillfactor
| WITH ( <index_option> [ ,...n ] )
| [ CHECK ( logical_expression ) ] [ ,...n ]
}
<table_index> ::=
{
{
INDEX index_name [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ]
(column_name [ ASC | DESC ] [ ,... n ] )
| INDEX index_name CLUSTERED COLUMNSTORE
| INDEX index_name [ NONCLUSTERED ] COLUMNSTORE ( column_name [ ,... n ] )
}
[ WITH ( <index_option> [ ,... n ] ) ]
[ ON { partition_scheme_name ( column_name )
| filegroup_name
| default
}
]
[ FILESTREAM_ON { filestream_filegroup_name | partition_scheme_name | "NULL" } ]
}
<index_option> ::=
{
PAD_INDEX = { ON | OFF }
| FILLFACTOR = fillfactor
| IGNORE_DUP_KEY = { ON | OFF }
| STATISTICS_NORECOMPUTE = { ON | OFF }
| STATISTICS_INCREMENTAL = { ON | OFF }
| ALLOW_ROW_LOCKS = { ON | OFF }
| ALLOW_PAGE_LOCKS = { ON | OFF }
| OPTIMIZE_FOR_SEQUENTIAL_KEY = { ON | OFF }
| COMPRESSION_DELAY = { 0 | delay [ Minutes ] }
| DATA_COMPRESSION = { NONE | ROW | PAGE | COLUMNSTORE | COLUMNSTORE_ARCHIVE }
[ ON PARTITIONS ( { partition_number_expression | <range> }
[ , ...n ] ) ]
| XML_COMPRESSION = { ON | OFF }
[ ON PARTITIONS ( { <partition_number_expression> | <range> }
[ , ...n ] ) ] ]
}
La sintassi seguente riguarda Azure Synapse Analytics e Parallel Data Warehouse e Microsoft Fabric:
DECLARE
{ { @local_variable [AS] data_type } [ = value [ COLLATE <collation_name> ] ] } [ ,...n ]
Argomenti
@local_variable
Nome di una variabile. I nomi di variabile devono iniziare con un simbolo di chiocciola (@). I nomi delle variabili locali devono essere conformi alle regole per gli identificatori.
data_type
Qualsiasi tipo di tabella di sistema, CLR (Common Language Runtime) definito dall'utente o tipo di dati alias. Una variabile non può essere di un tipo di dati text, ntext o image.
Per altre informazioni sui tipi di dati di sistema, vedere Tipi di dati (Transact-SQL). Per altre informazioni sui tipi CLR definiti dall'utente o sui tipi di dati alias, vedere CREATE TYPE (Transact-SQL).
= value
Assegna un valore alla variabile inline. Il valore può essere una costante o un'espressione, ma deve corrispondere al tipo di dichiarazione di variabile o deve supportare la conversione implicita in tale tipo. Per altre informazioni, vedere Espressioni (Transact-SQL).
@cursor_variable_name
Nome di una variabile di cursore. I nomi delle variabili di cursore devono iniziare con un simbolo di chiocciola (@) e devono essere conformi alle regole per gli identificatori.
CURSOR
Specifica che si tratta di una variabile di cursore locale.
@table_variable_name
Nome di una variabile di tipo table. I nomi delle variabili devono iniziare con un simbolo di chiocciola (@) e devono essere conformi alle regole per gli identificatori.
<table_type_definition>
Definisce il tipo di dati table. La dichiarazione di tabella include definizioni di colonna, nomi, tipi di dati e vincoli. Gli unici tipi di vincolo consentiti sono PRIMARY KEY, UNIQUE, NULL e CHECK. Non è possibile usare un tipo di dati alias come tipo di dati scalare di una colonna se al tipo è associata una regola o una definizione di valore predefinito.
<table_type_definition>
Subset di informazioni usate per definire una tabella in CREATE TABLE. Queste informazioni includono elementi e definizioni essenziali. Per altre informazioni, vedere CREATE TABLE (Transact-SQL).
n
Segnaposto che indica che è possibile specificare più variabili e assegnare i relativi valori. Quando si dichiarano variabili di tipo table, la variabile table deve essere l'unica dichiarata nell'istruzione DECLARE.
column_name
Nome della colonna della tabella.
scalar_data_type
Specifica che il tipo di dati della colonna è scalare.
computed_column_expression
Espressione che definisce il valore di una colonna calcolata. Il valore viene calcolato in base a un'espressione che utilizza altre colonne della stessa tabella. La definizione di una colonna calcolata potrebbe ad esempio essere cost AS price * qty. L'espressione può essere un nome di colonna non calcolata, una costante, una funzione predefinita, una variabile o qualsiasi combinazione di questi elementi uniti da uno o più operatori. Non può invece essere una sottoquery o una funzione definita dall'utente. Non può inoltre fare riferimento a un tipo CLR definito dall'utente.
[ COLLATE collation_name ]
Specifica le regole di confronto per la colonna. collation_name può essere un nome delle regole di confronto di Windows o un nome di regole di confronto SQL ed è applicabile solo per le colonne dei tipi di dati char, varchar, text, nchar, nvarchar e ntext. Se viene omesso, alla colonna vengono assegnate le regole di confronto del tipo di dati definito dall'utente, se il tipo di dati della colonna è definito dall'utente, oppure le regole di confronto del database corrente.
Per altre informazioni sui nomi delle regole di confronto Windows e SQL, vedere COLLATE (Transact-SQL).
DEFAULT
Specifica il valore assegnato alla colonna quando non viene specificato un valore in modo esplicito durante un inserimento. È possibile applicare le definizioni DEFAULT a qualsiasi colonna, ad eccezione di quelle definite come timestamp o con la proprietà IDENTITY. Le definizioni DEFAULT vengono rimosse quando la tabella viene eliminata. Come valore predefinito è possibile utilizzare solo una costante, ad esempio una stringa di caratteri, una funzione di sistema, ad esempio SYSTEM_USER(), oppure NULL. Per garantire la compatibilità con le versioni precedenti di SQL Server, è possibile assegnare un nome di vincolo a una definizione DEFAULT.
constant_expression
Costante, valore NULL o funzione di sistema usata come valore predefinito della colonna.
IDENTITY
Indica che la nuova colonna è una colonna Identity. Quando si aggiunge una nuova riga alla tabella, SQL Server assegna un valore univoco e incrementale alla colonna. Le colonne Identity vengono in genere usate in combinazione con vincoli PRIMARY KEY come identificatori di riga univoci per la tabella. La proprietà IDENTITY può essere assegnata a colonne tinyint, smallint, int, decimal(p,0) o numeric(p,0). Ogni tabella può includere una sola colonna Identity. Non è consentito associare valori predefiniti e vincoli DEFAULT alle colonne Identity. È necessario specificare sia il valore di inizializzazione che l'incremento oppure è possibile omettere entrambi questi valori. In questo secondo caso, il valore predefinito è (1,1).
seed
Valore usato per la prima riga caricata nella tabella.
increment
Valore incrementale aggiunto al valore Identity della riga caricata in precedenza.
ROWGUIDCOL
Specifica che la nuova colonna funge da identificatore di riga univoco globale. È possibile designare come colonna ROWGUIDCOL una sola colonna di tipo uniqueidentifier per ogni tabella. La proprietà ROWGUIDCOL può essere assegnata solo a una colonna uniqueidentifier.
NULL | NOT NULL
Indica se il valore Null è ammesso nella variabile. Il valore predefinito è NULL.
PRIMARY KEY
Vincolo che impone l'integrità di entità per una o più colonne specificate tramite un indice univoco. È possibile creare un solo vincolo PRIMARY KEY per ogni tabella.
UNIQUE
Vincolo che fornisce l'integrità di entità per una o più colonne specificate tramite un indice univoco. Una tabella può includere più vincoli UNIQUE.
CLUSTERED | NONCLUSTERED
Definisce la creazione di un indice cluster o non cluster per il vincolo PRIMARY KEY o UNIQUE. I vincoli PRIMARY KEY usano l'opzione CLUSTERED, mentre i vincoli UNIQUE usano l'opzione NONCLUSTERED.
L'opzione CLUSTERED può essere specificata solo per un vincolo. Se per un vincolo UNIQUE si specifica CLUSTERED e viene specificato anche un vincolo PRIMARY KEY, quest'ultimo usano l'opzione NONCLUSTERED.
CHECK
Vincolo che impone l'integrità di dominio tramite la limitazione dei valori che è possibile inserire in una o più colonne.
logical_expression
Espressione logica che restituisce TRUE o FALSE.
<index_option>
Specifica una o più opzioni per l'indice. Non è possibile creare indici in modo esplicito su variabili di tipo table e per le variabili di tipo table non vengono mantenute statistiche. A partire da SQL Server 2014 (12.x), è stata introdotta una nuova sintassi che consente di creare determinati tipi di indice inline con la definizione della tabella. Usando questa nuova sintassi, è possibile creare indici su variabili tabella come parte della definizione della tabella. In alcuni casi, è possibile ottenere un miglioramento delle prestazioni usando tabelle temporanee, che offrono statistiche e supporto completo per l'indice.
Per una descrizione completa di queste opzioni, vedere CREATE TABLE.
Variabili di tabella e stime delle righe
Le variabili di tipo table non includono statistiche di distribuzione In molti casi, l'utilità di ottimizzazione creerà un piano di query basandosi sul presupposto che la variabile di tabella contenga zero righe o una riga. Per altre informazioni, vedere Tipo di dati table - Limitazioni e restrizioni.
Per questo motivo, è necessario prestare attenzione in caso di utilizzo di una variabile di tabella se si prevede un numero elevato di righe (maggiore di 100). Considerare una delle alternative seguenti:
- Le tabelle temporanee possono essere una soluzione migliore rispetto alle variabili di tabella quando è possibile che il conteggio delle righe sia elevato (maggiore di 100).
- Per le query che uniscono in join la variabile di tabella con altre tabelle, usare l'hint RECOMPILE, con cui l'utilità di ottimizzazione userà la cardinalità corretta per la variabile di tabella.
- In Database SQL di Azure e a partire da SQL Server 2019 (15.x), la funzionalità di compilazione posticipata della variabile di tabella propaga stime di cardinalità basate sui conteggi effettivi delle righe delle variabili di tabella, fornendo un conteggio delle righe più accurato per ottimizzare il piano di esecuzione. Per altre informazioni, vedere Elaborazione di query intelligenti nei database SQL.
Osservazioni:
Le variabili spesso vengono utilizzate in un batch o una procedura come contatori per istruzioni WHILE e LOOP oppure per un blocco IF...ELSE.
Le variabili possono essere utilizzate solo nelle espressioni e non in sostituzione di parole chiave o nomi di oggetto. Per creare istruzioni SQL dinamiche, utilizzare EXECUTE.
L'ambito di una variabile locale è il batch in cui viene dichiarata.
Una variabile di tabella non è necessariamente residente in memoria. In situazioni di utilizzo elevato di memoria è possibile che per le pagine appartenenti a una variabile di tabella venga eseguito il push a tempdb.
È possibile definire un indice inline in una variabile di tabella.
Nelle seguenti istruzioni è possibile fare riferimento come origine a una variabile di cursore a cui è assegnato un cursore:
- Istruzione CLOSE
- DEALLOCATE - istruzione
- FETCH - istruzione
- OPEN - istruzione
- Istruzione DELETE o UPDATE posizionata
- Istruzione SET CURSOR con variabile (nella parte destra)
Se la variabile di cursore a cui viene fatto riferimento in queste istruzioni esiste ma non le è stato assegnato un cursore, SQL Server genera un errore. Se la variabile di cursore a cui viene fatto riferimento non esiste, SQL Server restituisce lo stesso errore generato per le variabili non dichiarate di altro tipo.
Una variabile di cursore:
Può essere la destinazione di un tipo di cursore o di un'altra variabile di cursore. Per ulteriori informazioni, vedere SET @local_variable (Transact-SQL).
Può essere specificata come destinazione di un parametro di cursore di output in un'istruzione EXECUTE se alla variabile di cursore non è attualmente assegnato un cursore.
Deve essere considerata come puntatore al cursore.
Esempi
R. Utilizzo di DECLARE
Nell'esempio seguente viene utilizzata una variabile locale denominata @find per recuperare le informazioni sul contatto per tutti i cognomi che iniziano con Man.
USE AdventureWorks2022;
GO
DECLARE @find VARCHAR(30);
/* Also allowed:
DECLARE @find VARCHAR(30) = 'Man%';
*/
SET @find = 'Man%';
SELECT p.LastName, p.FirstName, ph.PhoneNumber
FROM Person.Person AS p
JOIN Person.PersonPhone AS ph ON p.BusinessEntityID = ph.BusinessEntityID
WHERE LastName LIKE @find;
Il set di risultati è il seguente.
LastName FirstName Phone
------------------- ----------------------- -------------------------
Manchepalli Ajay 1 (11) 500 555-0174
Manek Parul 1 (11) 500 555-0146
Manzanares Tomas 1 (11) 500 555-0178
(3 row(s) affected)
B. Utilizzo di DECLARE con due variabili
Nell'esempio seguente vengono recuperati i nomi dei rappresentanti di Adventure Works Cycles che svolgono la propria attività in America del Nord e per cui l'ammontare delle vendite annue è pari almeno a $ 2.000.000.
USE AdventureWorks2022;
GO
SET NOCOUNT ON;
GO
DECLARE @Group nvarchar(50), @Sales MONEY;
SET @Group = N'North America';
SET @Sales = 2000000;
SET NOCOUNT OFF;
SELECT FirstName, LastName, SalesYTD
FROM Sales.vSalesPerson
WHERE TerritoryGroup = @Group and SalesYTD >= @Sales;
C. Dichiarazione di una variabile di tipo table
Nell'esempio seguente viene creata una variabile di tipo table in cui vengono archiviati i valori specificati nella clausola OUTPUT dell'istruzione UPDATE. Questa variabile è seguita da due istruzioni SELECT che restituiscono i valori in @MyTableVar e i risultati dell'operazione di aggiornamento nella tabella Employee. I risultati nella colonna INSERTED.ModifiedDate sono diversi rispetto ai valori nella colonna ModifiedDate della tabella Employee. Questo perché nella tabella AFTER UPDATE è stato definito il trigger ModifiedDate, che aggiorna il valore di Employee in base alla data corrente. Le colonne restituite da OUTPUT, tuttavia, riflettono i dati prima dell'attivazione dei trigger. Per altre informazioni, vedere Clausola OUTPUT (Transact-SQL).
USE AdventureWorks2022;
GO
DECLARE @MyTableVar TABLE (
EmpID INT NOT NULL,
OldVacationHours INT,
NewVacationHours INT,
ModifiedDate DATETIME);
UPDATE TOP (10) HumanResources.Employee
SET VacationHours = VacationHours * 1.25
OUTPUT INSERTED.BusinessEntityID,
DELETED.VacationHours,
INSERTED.VacationHours,
INSERTED.ModifiedDate
INTO @MyTableVar;
--Display the result set of the table variable.
SELECT EmpID, OldVacationHours, NewVacationHours, ModifiedDate
FROM @MyTableVar;
GO
--Display the result set of the table.
--Note that ModifiedDate reflects the value generated by an
--AFTER UPDATE trigger.
SELECT TOP (10) BusinessEntityID, VacationHours, ModifiedDate
FROM HumanResources.Employee;
GO
D. Dichiarazione di una variabile di tipo table, con indici inline
Nell'esempio seguente viene creata una variabile table con un indice inline cluster e due indici inline non cluster.
DECLARE @MyTableVar TABLE (
EmpID INT NOT NULL,
PRIMARY KEY CLUSTERED (EmpID),
UNIQUE NONCLUSTERED (EmpID),
INDEX CustomNonClusteredIndex NONCLUSTERED (EmpID)
);
GO
La query che segue restituisce informazioni sugli indici creati nella query precedente.
SELECT *
FROM tempdb.sys.indexes
WHERE object_id < 0;
GO
E. Dichiarazione di una variabile di tipo di tabella definito dall'utente
Nell'esempio seguente viene creato un parametro con valori di tabella o una variabile di tabella denominata @LocationTVP. A tale scopo è necessario un tipo di tabella definito dall'utente corrispondente denominato LocationTableType. Per altre informazioni sulla creazione di un tipo di tabella definito dall'utente, vedere CREATE TYPE (Transact-SQL). Per altre informazioni sui parametri con valori di tabella, vedere Usare parametri con valori di tabella (motore di database).
DECLARE @LocationTVP
AS LocationTableType;
Esempi: Azure Synapse Analytics e Piattaforma di strumenti analitici (PDW)
F. Utilizzo di DECLARE
Nell'esempio seguente viene utilizzata una variabile locale denominata @find per recuperare le informazioni sul contatto per tutti i cognomi che iniziano con Walt.
-- Uses AdventureWorks
DECLARE @find VARCHAR(30);
/* Also allowed:
DECLARE @find VARCHAR(30) = 'Man%';
*/
SET @find = 'Walt%';
SELECT LastName, FirstName, Phone
FROM DimEmployee
WHERE LastName LIKE @find;
G. Utilizzo di DECLARE con due variabili
Nell'esempio seguente vengono recuperate le variabili utente per specificare i nomi e i cognomi dei dipendenti inclusi nella tabella DimEmployee.
-- Uses AdventureWorks
DECLARE @lastName VARCHAR(30), @firstName VARCHAR(30);
SET @lastName = 'Walt%';
SET @firstName = 'Bryan';
SELECT LastName, FirstName, Phone
FROM DimEmployee
WHERE LastName LIKE @lastName AND FirstName LIKE @firstName;