SQL Server Denali – Sequence

Une des nouveautés attendue de SQL11 ou SQL Server 2011 (pour l’instant seul le nom de code est “officiel” : Denali) est la séquence.

Une séquence est un objet indépendant (pas de liaison avec une table comme la propriété identity sur une colonne de type integer) qui permet de générer une suite de valeurs numériques (ordre croissant ou décroissant). Une séquence peut aussi être partagée par plusieurs tables.

CREATE SEQUENCE [schema_name . ] sequence_name         
[ <sequence_property_assignment> [ ,…n ] ]   
[ ; ]   
<sequence_property_assignment>::= 
{        
[ AS { built_in_integer_type | user-defined_integer_type } ]   
    | START WITH <constant>    
        | INCREMENT BY <constant>   
        | { MINVALUE <constant> | NO MINVALUE }  
        | { MAXVALUE <constant> | NO MAXVALUE }  
        | { CYCLE | NO CYCLE }  
        | { CACHE [<constant> ] | NO CACHE }  
} 

Il est possible de spécifier la valeur de départ, l’incrément, de gérer un cache (attention toutefois, toutes les valeurs contenues dans la séquence seront perdues en cas d’arrêt du serveur SQL …)

Exemple de séquence simple :

CREATE SEQUENCE dbo.SequenceSimple
AS int
START     WITH 1
INCREMENT BY   1;

On peut légitimement se poser la question : Dois-je utiliser une séquence ou bien un integer avec propriété identity ? Voici  quelques scenarii possibles qui privilégient la séquence :

1. L’application a besoin d’une valeur numérique pour des traitements avant l’insertion en base de données

Il est aussi possible d’obtenir la prochaine valeur avant d’effectuer un insert dans une table :

CREATE TABLE dbo.Orders
    (OrderID int PRIMARY KEY,
    Name varchar(20) NOT NULL,
    Qty int NOT NULL);
GO

DECLARE @NextID int ;
SET @NextID = NEXT VALUE FOR dbo.SequenceSimple;
-- 
-- d'autres actions sont possible ....
--
INSERT dbo.Orders (OrderID, Name, Qty)
VALUES (@NextID, 'CBR XX', 1) ;
GO

Si l’insert n’avait pas eu lieu, le numéro alloué dans la séquence, même non utilisé dans un insert, n’est plus disponible, sauf recyclage.

L’opération “Next Value” sur la séquence peut être utilisée dans une valeur par défaut sur une colonne.

2. La série de nombres (la séquence) est partagée entre plusieurs tables ou répétée plusieurs fois dans plusieurs colonnes sur une seule et même table

3. L’application a besoin de recycler les valeurs au bout d’un certain temps : Il est aussi possible de “recycler” les valeurs de la séquence (de 1 à 10 par exemple), ce qui permet de facilement ajouter une colonne à une table et créer N partitions sur cette table.

CREATE SEQUENCE dbo.Sequence_1_10 
AS tinyint 
START     WITH 1 
INCREMENT BY   1 
MINVALUE       1 
MAXVALUE       10 
CYCLE ; 

4. Récupérer un ensemble de valeurs :

Vu que la séquence est un objet indépendant, il est possible d’obtenir la prochaine valeur ou un groupe de valeurs :

sp_sequence_get_range [ @sequence_name = ] N'<sequence>'    
	, [ @range_size = ] range_size   
	, [ @range_first_value = ] range_first_value OUTPUT    
	[, [ @range_last_value = ] range_last_value OUTPUT ]   
	[, [ @range_cycle_count = ] range_cycle_count OUTPUT ]   
	[, [ @sequence_increment = ] sequence_increment OUTPUT ]   
	[, [ @sequence_min_value = ] sequence_min_value OUTPUT ]   
	[, [ @sequence_max_value = ] sequence_max_value OUTPUT ]   
	[ ; ]

 

Ce qui nus donne :

DECLARE  
  @FirstSeqNum sql_variant
, @LastSeqNum  sql_variant
, @CycleCount  int
, @SeqIncr     sql_variant
, @SeqMinVal   sql_variant
, @SeqMaxVal   sql_variant ;

EXEC sys.sp_sequence_get_range
  @sequence_name      = N'dbo.Sequence_1_10'
, @range_size         = 5
, @range_first_value  = @FirstSeqNum OUTPUT 
, @range_last_value   = @LastSeqNum  OUTPUT 
, @range_cycle_count  = @CycleCount  OUTPUT
, @sequence_increment = @SeqIncr     OUTPUT
, @sequence_min_value = @SeqMinVal   OUTPUT
, @sequence_max_value = @SeqMaxVal   OUTPUT ;


SELECT
  @FirstSeqNum AS FirstVal
, @LastSeqNum  AS LastVal
, @CycleCount  AS CycleCount
, @SeqIncr     AS SeqIncrement
, @SeqMinVal   AS MinSeq
, @SeqMaxVal   AS MaxSeq ;

image

Une application cliente peut tout à fait faire appel à l’objet séquence sans nécessairement faire ensuite une opération dans la base de données :

SqlCommand cmd = new SqlCommand();   
cmd.Connection = conn;  
cmd.CommandType = CommandType.StoredProcedure;   
cmd.CommandText = "sys.sp_sequence_get_range";   
cmd.Parameters.AddWithValue("@sequence_name", "dbo.Sequence_1_10");   
cmd.Parameters.AddWithValue("@range_size", 5);  
   
SqlParameter firstValueInRange = new SqlParameter("@range_first_value", SqlDbType.Variant);  
firstValueInRange.Direction = ParameterDirection.Output;  
cmd.Parameters.Add(firstValueInRange);  
conn.Open(); 
cmd.ExecuteNonQuery();  
Console.WriteLine(firstValueInRange.Value);

5. Un cas un peu tordu : l’application doit récupérer une séquence de nombre dont les valeurs sont triées suivant une autre colonne !!! (le order by de la clause over …).

SELECT NEXT VALUE FOR dbo.SequenceSimple OVER (ORDER BY Name) AS NutID, 
	ProductID, 
	Name, 
	ProductNumber 
FROM AdventureWorks2008.Production.Product
WHERE Name LIKE '%nut%' ;

 

image

Cependant il ne faut pas se méprendre, la séquence ne prémunit pas contre les doublons. Ce n’est pas une contrainte. Il faut passer par un index unique pour assurer l’application de l’unicité. De même il est possible de faire un update sur ce champ, si ce n’est pas autorisé il faut prévoir un trigger after update et faire un rollback de la transaction.

De plus, en cas de rollback sur une transaction insert, la colonne recevant les valeurs de la séquence présentera des “trous, tout comme si cette séquence est partagée entre plusieurs tables ou utilisée à d’autres fins …

Si vous souhaitez rechercher des informations sur les séquences créées dans votre base de données :

SELECT * FROM sys.sequences;

 

Notez en particulier les colonnes de valeur de début d’intervalle, d’incrément, les propriétés is_cycling et is_cached , current_Value, …

SELECT  name, 
	    SCHEMA_NAME(schema_id) as 'Schema name',
		start_value ,
		increment   ,
		minimum_value,
		maximum_value,
		is_cycling  ,
		is_cached   , 
		current_Value
FROM sys.sequences;

image

Il est aussi possible de réinitialiser la valeur d’une séquence :

ALTER SEQUENCE dbo.SequenceSimple
RESTART WITH 1 ;

image

Lors des formations que je dispense, souvent des DBA Oracle me posaient la question sur l’existence de cet objet de base de données, je pourrais enfin leur répondre “Oui, la séquence existe sur SQL Server”.

Bons tests, d’autres billets vont suivre sur les nouveautés de SQL Server vNext, Denali ….

PS : un problème avec mon syntax highlighter m’a obligé à supprimer ce billet initialement publier hier soir et à faire une nouvelle tentative ce matin. Désolé.

A propos Christophe

Consultant SQL Server Formateur certifié Microsoft MVP SQL Server MCM SQL Server 2008
Cet article, publié dans SQL Server, est tagué , , . Ajoutez ce permalien à vos favoris.

Un commentaire pour SQL Server Denali – Sequence

  1. Ping : SQL Server Denali CTP3 – Nouveautés T-SQL | Christophe LAPORTE – Consultant SQL Server

Laisser un commentaire

Entrez vos coordonnées ci-dessous ou cliquez sur une icône pour vous connecter:

Logo WordPress.com

Vous commentez à l'aide de votre compte WordPress.com. Déconnexion / Changer )

Image Twitter

Vous commentez à l'aide de votre compte Twitter. Déconnexion / Changer )

Photo Facebook

Vous commentez à l'aide de votre compte Facebook. Déconnexion / Changer )

Photo Google+

Vous commentez à l'aide de votre compte Google+. Déconnexion / Changer )

Connexion à %s