Tout savoir sur SEQUENCE avec SQL Server 2012

Publié le par

Utilisation en combinaison avec des requêtes

Voyons si l’on peut utiliser la SEQUENCE avec UNION ALL :

1
2
3
SELECT  NEXT VALUE FOR dbo.ma_sequence_tinyint, 'Toto'
UNION ALL SELECT NEXT VALUE FOR dbo.ma_sequence_tinyint, 'Titi'
UNION ALL SELECT NEXT VALUE FOR dbo.ma_sequence_tinyint, 'Tutu'

Donc SEQUENCE ne serait à priori pas ensembliste.
Essayons avec d’autres exemples pour confirmer cette hypothèse :

1
2
3
4
5
6
7
8
9
10
;WITH
  CTE(nom) AS
  (
    SELECT 'Toto'
    UNION ALL SELECT 'Titi'
    UNION ALL SELECT 'Tutu'
  )
SELECT  (SELECT NEXT VALUE FOR dbo.mequence_tinyint) AS sq
  , nom
FROM  CTE

Nous découvrons ici un autre jeu de limitations.
Essayons de voir si l’on peut contourner un peu celles-ci avec un row constructor :

Ajoutons un UPDATE au lot précédent, puis exécution le lot complet :

1
2
UPDATE  @tb_test_sequence_set_based
SET  i = NEXT VALUE FOR dbo.maSequence

Et si la SEQUENCE est cyclique ?

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
CREATE SEQUENCE cycling_sequence
AS tinyint
MAXVALUE 5
CYCLE
GO

DECLARE @test_cycling_sequence TABLE
(
        name varchar(15) NOT NULL
        , i tinyint NOT NULL DEFAULT 0
)

INSERT  INTO @test_cycling_sequence (name)
VALUES  ('Bugs Bunny'), ('Daffy Duck'), ('Elmer Fudd'), ('Porky Pig'), ('Titi')
        , ('Sylvestre'), ('Coyote'), ('Road Runner'), ('Foghorn Leghorn'), ('Marvin Martian')

UPDATE  @test_cycling_sequence
SET     i = NEXT VALUE FOR dbo.cycling_sequence

SELECT  name
        , i
FROM    @test_cycling_sequence

On peut également utiliser les SEQUENCE pour définit une contrainte de valuation par défaut :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
CREATE TABLE test_default_constraint_with_sequence
(
  un_caractere char(1) NOT NULL
  , une_valeur tinyint NOT NULL
    CONSTRAINT DF_test_default_constraint_with_sequence__une_valeur
      DEFAULT (NEXT VALUE FOR dbo.ma_sequence_tinyint)
)
GO
 
INSERT  INTO dbo.test_default_constraint_with_sequence (un_caractere)
VALUES  ('A'), ('B'), ('C'), ('D'), ('E')
GO
 
SELECT  un_caractere
  , une_valeur
FROM  dbo.test_default_constraint_with_sequence

On peut continuer de jouer avec cette même SEQUENCE :

Un peu plus marrant : on peut aussi spécifier l’ordre dans lequel les valeurs de la SEQUENCE sont retournés :

1
2
3
SELECT  NEXT VALUE FOR dbo.ma_sequence_tinyint OVER(ORDER BY NEWID())
  , un_caractere
FROM  dbo.test_default_constraint_with_sequence

Ici on a réalisé un tri aléatoire à l’aide de la clause ORDER BY NEWID(), mais on aurait plus remplacer NEWID() par une colonne.

Attention cependant : si la SEQUENCE est appelée dans le même ordre SELECT, le nombre généré est identique :

1
2
SELECT  NEXT VALUE FOR dbo.ma_sequence_smallint AS a
  , NEXT VALUE FOR dbo.ma_sequence_smallint AS b</code>

Ce comportement caractéristique de SQL est le même que celui qu’on obtient lorsqu’on souhaite échanger les valeurs de deux colonnes dans une instruction UPDATE : on n’a pas besoin d’une table intermédiaire.

Pages : 1 2 3 4 5 6

Laisser un commentaire