Tout savoir sur SEQUENCE avec SQL Server 2012

Publié le par

Implémentation de SQL dans SQL Server longtemps attendue, voici enfin SEQUENCE, qui permet d’obtenir un nombre augmenté d’un certain incrément à chaque appel, que l’on peut partager entre les tables et les utilisateurs d’une base de données, et bien d’autre possibilités encore …

Attention : cet article comporte 6 pages :)

On notera tout d’abord qu’un objet de type SEQUENCE n’est utilisable qu’avec des types numériques précis : tinyint, smallint, int, bigint, decimal et numéric, les deux derniers avec une échelle à zéro.

Commençons donc par créer une SEQUENCE de la façon la plus simple qui soit :

CREATE SEQUENCE dbo.maSequence

C’est aussi simple que cela, mais nous allons voir par la suite qu’on peut aller un peu plus loin.

Maintenant, comment obtenir la « première » valeur de la SEQUENCE dbo.maSequence ?
Là encore, c’est relativement simple :

SELECT NEXT VALUE FOR dbo.maSequence

Donc si l’on crée un objet SEQUENCE sans aucune option, le type par défaut est bigint, et la valeur initiale attribuée à cette SEQUENCE est la plus petite valeur que peut stocker le type de données bigint.

Est-ce le cas avec les autres types de données ?

1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE SEQUENCE dbo.ma_sequence_smallint
AS smallint
GO

SELECT NEXT VALUE FOR dbo.ma_sequence_smallint AS next_value_ma_sequence_smallint
GO

CREATE SEQUENCE dbo.ma_sequence_tinyint
AS tinyint
GO

SELECT NEXT VALUE FOR dbo.ma_sequence_tinyint AS next_value_ma_sequence_tinyint
GO

Rien de surprenant par rapport au comportement par défaut.
En revanche le fait que la SEQUENCE ne commence pas à 1 comme avec les colonnes de type entier auxquelles on confère la propriété d’auto-incrémentation à l’aide de la clause IDENTITY est appréciable.

Avant de continuer à nous éclairer sur SEQUENCE, voyons comment connaître les propriétés d’un tel objet : la vue système sys.sequences est là pour cela :

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT          S.name AS sequence_name
                , T.name AS sequence_data_type_name
                , S.start_value
                , S.increment
                , S.minimum_value
                , S.maximum_value
                , S.is_cycling
                , S.cache_size
                , S.current_value
                , S.is_exhausted
FROM            sys.sequences AS S
INNER JOIN      sys.types AS T
                        ON S.system_type_id = T.system_type_id

Ce qui retourne :

Je passerai sur la description des colonnes jusqu’à maximum_value, et sur current_value puisque leur nom est suffisamment explicite.

La colonne is_cycling nous montre qu’il est possible de faire boucler la SEQUENCE, c’est à dire de la faire revenir à sa valeur de départ.
La colonne cache_size indique le nombre de valeurs qui est mise en cache. Nous verrons la notion de cache de SEQUENCE par la suite.
Enfin la colonne is_exhausted indique s’il est encore possible de générer de nouveaux nombres à partir de celle-ci.

On retrouve les objets SEQUENCE dans l’Explorateur d’Objets, avec les fonctionnalités accessibles par clic-droit que l’on connaît :

Après un clic sur Propriétés :

On obtient la fenêtre suivante :

On peut aussi créer une SEQUENCE décrémentielle, simplement en spécifiant un incrément négatif :

1
2
3
4
5
6
7
8
9
10
11
CREATE SEQUENCE uneSequenceDecrementielle
AS int
START WITH 0
INCREMENT BY -5
GO

SELECT NEXT VALUE FOR uneSequenceDecrementielle
SELECT NEXT VALUE FOR uneSequenceDecrementielle
SELECT NEXT VALUE FOR uneSequenceDecrementielle
SELECT NEXT VALUE FOR uneSequenceDecrementielle
SELECT NEXT VALUE FOR uneSequenceDecrementielle

On peut également changer l’incrément en cours de route à l’aide d’une instruction ALTER SEQUENCE :

1
2
3
4
5
ALTER SEQUENCE dbo.uneSequenceDecrementielle
INCREMENT BY -2
GO

SELECT NEXT VALUE FOR uneSequenceDecrementielle

Il est également possible, comme le suggère la colonne maximum_value de la vue sys.sequences, mettre une valeur maximale à cette séquence.
Il faut bien sûr dans ce cas que la valeur maximale de la séquence prenne en compte la valeur de départ, sinon celle-ci ne serait pas générable :

1
2
3
ALTER SEQUENCE dbo.uneSequenceDecrementielle
MAXVALUE -30
GO

Ici comme la séquence est décrémentielle, SQL Server rejette l’instruction ALTER SEQUENCE puisque nous avions spécifié que la SEQUENCE démarre à zéro.

Si nous changeons par MINVALUE :

1
2
3
ALTER SEQUENCE dbo.uneSequenceDecrementielle
MAXVALUE -30
GO

Voici un message d’avertissement intéressant : nous n’avons pas spécifié de valeur pour la taille du cache de la SEQUENCE, mais SQL Server en a mis une par défaut …
J’ai tenté de savoir quelle est la taille du cache par défaut, et comment SQL Server stocke celle-ci en utilisant une connexion d’administrateur dédiée (DAC), mais il semble que SQL Server réalise cela dans des tables internes.
Si quelqu’un découvre cela, merci de le poster en commentaire :)

Pages : 1 2 3 4 5 6

Laisser un commentaire