SQL Server 2012: T-SQL Sequence Object

La nuova versione di SQL Server 2012 ha introdotto il nuovo oggetto Sequence.
Il sequence potrebbe essere visto come una sorta di identity a livello di database che incrementa o decrementa il valore restituito in base alle specifiche definite durante la creazione.
Immaginate di dover numerare le operazioni effettuate in generale all'interno del DB da tutte le vostre applicazioni... bhè il sequence potrebbe fare al caso vostro.
Come possiamo creare l'oggetto sequence?

E' molto semplice :

--Sintassi Completa
Create Sequence Dbo.MySequenceOBJ
As tinyint
Start with 0
 Minvalue 0
 Maxvalue 255
Increment by 1
cycle
cache 25

--Sintassi Semplificata
Create Sequence Dbo.MySequenceOBJ

Poniamo l'attenzione sulle proprietà CYCLE/NO CYCLE e CACHE/NO CACHE.

La proprietà CYCLE indica a SQL Server che l'oggetto dovrà essere re-inizializzato nel momento in cui esaurirà il range dei valori a sua disposizione ripartendo dal valore specificato nella proprietà Minvalue.
In caso non sia specificato il default è NO CYCLE che solleva un'errore qualora l'oggetto esaurisse i valori a sua disposizione.
L'errore generato in questo caso è :

Msg 11728, Level 16, State 1, Line 2
The sequence object 'MySequenceOBJ' has reached its minimum or maximum value. Restart the sequence object to allow new values to be generated.


Per effettuare il reset basta eseguire il comando RESTART

Alter Sequence Dbo.MySequenceOBJ Restart with 0

La proprietà CACHE è utile qualora si vogliano ottimizzare le performance delle applicazione che utilizzeranno l'oggetto appena creato.
Nel nostro esempio l'oggetto sequence alloca in memoria i numeri 0 e 24 ed il numero 24 verrà scritto anche sul disco.
Quando una connessione farà richiesta di un numero lo preleverà dalla cache invece di andare a leggerlo sul disco diminuendo in questo modo l'IO,la possibilità di accesso concorrenziale alla pagina contenente il dato e di conseguenza i problemi di Lock. Nel momento in cui il range contenuto in cache si esaurisce SQL Server ri-alloca la cache con i valori 25 e 49. 49verrà inoltre scritto anche sul disco.
In pratica in memoria abbiamo sempre un'allocazione pari al doppio dell'allocazione del tipo di dato selezionato perchè SQL Server mantiene in cache il valore corrente ed il numero di valori rimasti in cache.
Se in fase di creazione il parametro CACHE non è specificato SQL utilizza una cache di 50 valori (questo è valido per la CTP3, al momento non so se la RTM avrà ancora questa impostazione).
Se si specifica NO CACHE SQL Server ogni volta reperirà il valore dell'oggetto prelevandolo dalla tabella sys.sequences

Come facciamo ad ottenere un valore dall'oggetto sequence ?
Grazie al comando next value for

Select next value for Dbo.MySequenceOBJ

Possiamo utilizzare il sequence durante gli inserimenti ?
Assolutamente si !!
Ecco come :

create table dbo.MyTestTab
(
   id int identity(-32768,1),
   sequencevalue tinyint,
   valore varchar(25)
)

--Inserisco 5 righe
Insert into dbo.MyTestTab(sequencevalue,valore )
    Values(Next Value For Dbo.MySequenceOBJ,'Ciao')
          ,(Next Value For Dbo.MySequenceOBJ,'a')
          ,(Next Value For Dbo.MySequenceOBJ,'tutti')
          ,(Next Value For Dbo.MySequenceOBJ,'dal''oggetto')      
          ,(Next Value For Dbo.MySequenceOBJ,'sequence !!')

Select * from dbo.MyTestTab

Drop table dbo.MyTestTab

Il sequence inoltre potrebbe essere utilizzato come valore di default per un campo con funzionalità simili all'identity

Create Table dbo.TestIdentityWithDefault
(
 id int default(next value For Dbo.MySequenceOBJ),
 valore varchar(25)
)

Insert Into dbo.TestIdentityWithDefault (valore) values('Valore corrente Sequence')

Select * from dbo.TestIdentityWithDefault

Drop table dbo.TestIdentityWithDefault

Per eliminare l'oggetto sequence:

drop Sequence Dbo.MySequenceOBJ

Spero vi sia utile !!

Ciao