Home > Articoli > About SEQUENCE (T-SQL) in SQL Server Code-Name “Denali”

About SEQUENCE (T-SQL) in SQL Server Code-Name “Denali”

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:

select
  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:

alter sequence dbo.MySequence
  restart with 20;
go

I prossimi valori restituiti, da dbo.MySequence, partiranno dal valore 20:

select
  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.

— View the table
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.

 

Chi è Sergio Govoni

Sergio Govoni è laureato in Scienze e Tecnologie Informatiche. Da oltre 16 anni lavora presso una software house che produce un noto sistema ERP, distribuito a livello nazionale ed internazionale, multi azienda client/server su piattaforma Win32. Attualmente si occupa di progettazione e analisi funzionale, coordina un team di sviluppo ed è responsabile tecnico di prodotto. Lavora con SQL Server dalla versione 7.0 e si è occupato d'implementazione e manutenzione di database relazionali in ambito gestionale, ottimizzazione delle prestazioni e problem solving. Nello staff di UGISS si dedica alla formazione e alla divulgazione in ambito SQL Server e tecnologie a esso collegate, scrivendo articoli e partecipando come speaker ai workshop e alle iniziative del primo e più importante User Group Italiano sulla tecnologia SQL Server. Ha conseguito la certificazione MCP, MCTS SQL Server. Per il suo contributo nelle comunità tecniche e per la condivisione della propria esperienza con altri, dal 2010 riceve il riconoscimento SQL Server MVP (Microsoft Most Valuable Professional). Nel corso dell'anno 2011 ha contribuito alla scrittura del libro SQL Server MVP Deep Dives Volume 2 (http://www.manning.com/delaney/).

Leggi Anche

Automazione delle attività di manutenzione in Azure SQL Database (2 Parte)

Introduzione Nell’articolo Automazione delle attività di manutenzione in Azure SQL Database abbiamo descritto le attività …