UGISS User Group Italiano SQL Server
In questo articolo parleremo di un nuovo oggetto (user-defined schema-bound object) che sarà possibile utilizzare nell’implementazione di un DB, a partire dalla prossima versione di SQL Server, oggi in CTP1 con il Code-Name "Denali".
Parleremo di SEQUENCE, un nuovo oggetto, in grado di generare una sequenza di valori numerici in funzione delle specifiche definite in fase di creazione. La sequenza di valori numerici potrà essere generata con ordinamento crescente o decrescente partendo dal valore minimo o dal valore massimo (count-down), i valori numerici della sequenza potranno essere ripetuti in modo ciclico una volta raggiunto il limite inferiore o superiore.
A differenza di una colonna con proprietà IDENTITY, un oggetto SEQUENCE non è associato ad alcuna tabella, le eventuali relazioni con una o più tabelle del DB dovranno essere gestite dall’applicazione database.
Utilizzi Tipici
Un oggetto SEQUENCE può essere utilizzato in sostituzione di una colonna IDENTITY in questi scenari, quando:
-
L’applicazione richiede un numero (valore) prima che un comando di INSERT sia eseguito
-
L’applicazione richiede che sia condivisa una singola sequenza di valori numerici tra più tabelle o tra più colonne all’interno della stessa tabella
-
L’applicazione deve resettare la sequenza (valore corrente) quando si raggiunge un determinato numero. Ad esempio, dopo aver assegnato ad ogni nuova riga di un ordine di vendita valori compresi tra 1 e 20, le (eventuali) successive righe inserite dovranno ricevere ancora valori numerici compresi tra 1 e 20 e così via…
-
L’applicazione richiede una sequenza di valori ordinati in funzione di un’altra colonna
-
…
Limitazioni
-
Diversamente da una colonna IDENTITY, i cui valori (tipicamente) non possono essere modificati; i valori di una sequenza non sono protetti (by design) da eventuali UPDATE eseguiti ad esempio dopo aver inserito tali valori in un tabella
-
Non ci sono vincoli automatici di univocità sui valori di una sequenza
-
Gli oggetti di tipo SEQUENCE, dopo aver generato i valori, coerentemente con la loro definizione, non sono in grado di controllare come tali valori saranno utilizzati. I valori di una sequenza, inseriti ad esempio in una tabella, possono non essere contigui per effetto di eventuali ROLLBACK
Per creare un oggetto SEQUENCE (indipendente da qualsiasi tabella) è necessario utilizzare il nuovo comando CREATE SEQUENCE come riportato nel seguente frammento di codice T-SQL:
————————————————————————
— Setup DB
————————————————————————
use [master];
go
if exists(select * from sys.databases where name = ‘SSDenali_SEQUENCE’)
begin
alter database [SSDenali_SEQUENCE]
set single_user with rollback immediate;
drop database [SSDenali_SEQUENCE];
end
go
— Create database
create database [SSDenali_SEQUENCE];
go
— change database context
use [SSDenali_SEQUENCE];
go
————————————————————————
— Create new sequence
————————————————————————
— create new sequence object (drop if exists)
if object_id(‘dbo.MySequence’, ‘SO’) is not null
drop sequence dbo.MySequence;
go
create sequence dbo.MySequence
as
int
minvalue 1
no maxvalue
start with 1;
go
L’oggetto dbo.MySequence appena creato, sul database dimostrativo [SSDenali_SEQUENCE], è consultabile sia attraverso l’Object Explorer di SQL Server Management Studio che attraverso la vista di sistema sys.sequences, come illustrato in figura 1 e figura 2.
Figura 1 – SSMS Object Explorer –> [SSDenali_SEQUENCE]\Programmability\Sequence.
Figura 2 – Vista di sistema sys.sequences.
Il seguente frammento di codice T-SQL rappresenta un esempio di utilizzo della funzione NEXT VALUE FOR per l’oggetto SEQUENCE, in particolare il comando restituirà i prossimi due valori della sequenza numerica dbo.MySequence:
MySequence_Value = next value for dbo.MySequence
union all
select
next value for dbo.MySequence;
go
L’output è illustrato in figura 3:
Figura 3 – Utilizzo della funzione NEXT VALUE FOR per la sequenza dbo.MySequence.
Osserviamo che per creare l’oggetto dbo.MySequence non è stato necessario disporre di una tabella e non ci siamo neppure preoccupati di salvare su disco le informazioni relative alla sequenza (per il momento il DB di esempio [SSDenali_SEQUENCE] non contiene user-table).
Un problema tipico delle colonne IDENTITY è quello che si riferisce al reset dei valori della sequenza. Per resettare i valori di una sequenza numerica di un oggetto SEQUENCE si potrà utilizzare l’opzione RESTART WITH come riportata il seguente comando T-SQL:
restart with 20;
go
I prossimi valori restituiti, da dbo.MySequence, partiranno dal valore 20:
next_mysequence_value = next value for dbo.MySequence
union all
select
next value for dbo.MySequence;
go
L’output è illustrato in figura 4:
Figura 4 – Valori restituiti dalla sequenza dbo.MySequence dopo il comando RESTART WITH.
Ritengo sia importante segnalare che gli oggetti SEQUENCE non assicurano la transazionalità dei valori sequenziali e neppure la continuità degli stessi, ad esempio, quando una transazione viene respinta (ROLLBACK) oppure quando più tabelle condividono valori provenienti dalla stessa sequenza. Nel prossimo esempio dimostreremo che i valori della sequenza dbo.MySequence rimangono *consumati* anche dopo il ROLLBACK del contesto transazionale in cui sono stati generati.
— Transactionally consistent
begin transaction;
select next value for dbo.MySequence;
rollback;
select next value for dbo.MySequence;
go
La figura 5 illustra l’output del precedente frammento di codice T-SQL.
Figura 5 – Valori della sequenza dbo.MySequence in un contesto transazionale (con ROLLBACK)
Vediamo ora uno degli utilizzi più tipici per un oggetto SEQUENCE, nel prossimo esempio verrà creata, sul database di esempio [SSDenali_SEQUENCE], la tabella dbo.Orders in cui abbiamo evitato di utilizzare la proprietà IDENTITY per la colonna OrderID (primary key della tabella).
La generazione dei valori univoci, da assegnare alla colonna OrderID della tabella dbo.Orders, è gestita dalla sequenza dbo.SO_OrderID. Osserviamo l’utilizzo della funzione NEXT VALUE FOR nel comando di INSERT.
— Using of sequence instead of IDENTITY
— Create a table (drop if exists)
if object_id(‘dbo.Orders’, ‘U’) is not null
drop table dbo.Orders;
go
create table dbo.Orders
(
orderid int not null primary key
,ordernumber varchar(20) not null
,customerid int not null
,qty decimal(8, 3) not null
);
go
— Create a sequence (drop if exists)
if object_id(‘dbo.SO_OrderID’, ‘SO’) is not null
drop sequence dbo.SO_OrderID;
go
create sequence dbo.SO_OrderID
as
int
start with 1
increment by 1;
go
— Insert some records
insert into dbo.Orders
(
orderid
,ordernumber
,customerid
,qty
)
values
(
next value for dbo.SO_OrderID
,’N0000000001′
,1
,10
),
(
next value for dbo.SO_OrderID
,’N0000000002′
,5
,80
);
go
Visualizziamo ora il contenuto della tabella dbo.Orders, ci aspettiamo due record, per ognuno dei quali ci aspettiamo che la colonna OrderID sia stata valorizzata correttamente.
select * from dbo.Orders;
go
L’output ottenuto è illustrato in figura 6:
Figura 6 – Contenuto della tabella dbo.Orders.
Conclusioni
Spero che attraverso questa overview possiate valutare l’utilizzo di SEQUENCE (T-SQL) nell’implementazione dei vostri DB… in attesa della (nuova) versione ufficiale di SQL Server.
Per maggiori informazioni potete consultare la documentazione in linea Creating and Using Sequence Numbers.
