NEXT VALUE FOR (Transact-SQL)

  • Article

Génère un numéro séquentiel de l'objet séquence spécifié.

Pour une description complète de la création et de l'utilisation de séquences, consultez Numéros de séquence. Utilisez sp_sequence_get_range pour réserver une plage de numéros séquentiels.

Icône Lien de rubrique Conventions de la syntaxe Transact-SQL

Syntaxe

NEXT VALUE FOR [ database_name . ] [ schema_name . ]  sequence_name
   [ OVER (<over_order_by_clause>) ]

Arguments

  • database_name
    Nom de la base de données qui contient l'objet séquence.

  • schema_name
    Nom du schéma qui contient l'objet séquence.

  • sequence_name
    Nom de l'objet séquence qui génère le nombre.

  • over_order_by_clause
    Détermine l'ordre dans lequel la valeur de séquence est affectée aux lignes d'une partition. Pour plus d'informations, consultez Clause OVER (Transact-SQL).

Types des valeurs retournées

Retourne un nombre à l'aide du type de la séquence.

Notes

La fonction NEXT VALUE FOR peut être utilisée dans les procédures stockées et les déclencheurs.

Lorsque la fonction NEXT VALUE FOR est utilisée dans une requête ou une contrainte par défaut, si le même objet séquence est utilisé plusieurs fois, ou si le même objet séquence est utilisé à la fois dans l'instruction qui fournit les valeurs et dans une contrainte par défaut en cours d'exécution, la même valeur sera retournée pour toutes les colonnes qui référencent la même séquence dans une ligne dans le jeu de résultats.

La fonction NEXT VALUE FOR est non déterministe et n'est autorisée que dans les contextes où le nombre de valeurs de séquence générées est bien défini. Vous trouverez ci-dessous la définition du nombre de valeurs qui sera utilisé pour chaque objet séquence référencé dans une instruction donnée :

  • SELECT - Pour chaque objet séquence référencé, une nouvelle valeur est générée une fois par ligne dans le résultat de l'instruction.

  • INSERT … VALUES - Pour chaque objet séquence référencé, une nouvelle valeur est générée une fois par ligne insérée dans l'instruction.

  • UPDATE - Pour chaque objet séquence référencé, une nouvelle valeur est générée pour chaque ligne mise à jour par l'instruction.

  • Instructions de procédure (telles que DECLARE, SET, etc.) - Pour chaque objet séquence référencé, une nouvelle valeur est générée pour chaque instruction.

Limitations et restrictions

La fonction NEXT VALUE FOR ne peut pas être utilisée dans les situations suivantes :

  • Lorsqu'une base de données est en mode en lecture seule.

  • Comme argument à une fonction table.

  • Comme argument à une fonction d'agrégation.

  • Dans les sous-requêtes, notamment les expressions de table communes et les tables dérivées.

  • Dans les vues, dans les fonctions définies par l'utilisateur ou dans les colonnes calculées.

  • dans une instruction qui utilise l'opérateur DISTINCT, UNION, UNION ALL, EXCEPT ou INTERSECT ;

  • dans une instruction qui utilise la clause ORDER BY à moins que NEXT VALUE FOR … OVER (ORDER BY …) ne soit utilisé ;

  • dans les clauses suivantes : FETCH, OVER, OUTPUT, ON, PIVOT, UNPIVOT, GROUP BY, HAVING, COMPUTE, COMPUTE BY,ou FOR XML ;

  • dans des expressions conditionnelles à l'aide de CASE, CHOOSE, COALESCE, IIF, ISNULL ou NULLIF ;

  • Dans une clause VALUES qui ne fait pas partie d'une instruction INSERT ;

  • Dans la définition d'une contrainte de validation.

  • Dans la définition d'une règle ou d'un objet par défaut. (Il peut être utilisé dans une contrainte par défaut.)

  • Comme valeur par défaut dans un type de table défini par l'utilisateur.

  • dans une instruction qui utilise TOP, OFFSET, ou lorsque l'option ROWCOUNT est définie ;

  • dans la clause WHERE d'une instruction ;

  • dans une instruction MERGE. (Sauf lorsque la fonction NEXT VALUE FOR est utilisée dans une contrainte par défaut dans la table cible et la valeur par défaut est utilisée dans l'instruction CREATE de l'instruction MERGE.)

Utilisation d'un objet séquence dans une contrainte par défaut

Lors de l'utilisation de la fonction NEXT VALUE FOR dans une contrainte par défaut, les règles suivantes s'appliquent :

  • Un objet séquence unique peut être référencé à partir de contraintes par défaut dans plusieurs tables.

  • La table et l'objet séquence doivent résider dans la même base de données.

  • L'utilisateur qui ajoute la contrainte par défaut doit avoir l'autorisation REFERENCES sur l'objet séquence.

  • Un objet séquence référencé à partir d'une contrainte par défaut ne peut pas être supprimé avant la suppression de la contrainte par défaut.

  • Le même numéro séquentiel est retourné pour toutes les colonnes dans une ligne si plusieurs contraintes par défaut utilisent le même objet séquence, ou si le même objet séquence est utilisé à la fois dans l'instruction qui fournit les valeurs et dans une contrainte par défaut en cours d'exécution.

  • Les références à la fonction NEXT VALUE FOR dans une contrainte par défaut ne peuvent pas spécifier la clause OVER.

  • Un objet séquence référencé dans une contrainte par défaut peut être modifié.

  • Dans le cas d'une instruction INSERT … SELECT ou INSERT … EXEC où les données insérées proviennent d'une requête à l'aide d'une clause ORDER BY, les valeurs qui sont retournées par la fonction NEXT VALUE FOR seront générées dans l'ordre spécifié par la clause ORDER BY.

Utilisation d'un objet séquence avec une clause OVER ORDER BY

La fonction NEXT VALUE FOR prend en charge la génération de valeurs de séquence triées en appliquant la clause OVER à l'appel NEXT VALUE FOR. À l'aide de la clause OVER, un utilisateur a la certitude que les valeurs qui sont retournées sont générées dans l'ordre de la sous-clause ORDER B de la clause OVER. Les règles supplémentaires suivantes s'appliquent lors de l'utilisation de la fonction NEXT VALUE FOR avec la clause OVER :

  • Les appels multiples à la fonction NEXT VALUE FOR pour le même générateur de séquence dans une instruction unique doivent tous utiliser la même définition de clause OVER.

  • Les appels multiples à la fonction NEXT VALUE FOR qui référence différents générateurs de séquence dans une instruction unique peuvent avoir des définitions de clause OVER différentes.

  • Une clause OVER appliquée à la fonction NEXT VALUE FOR ne prend pas en charge la sous-clause PARTITION BY.

  • Si tous les appels à la fonction NEXT VALUE FOR dans une instruction SELECT spécifient la clause OVER, une clause ORDER BY peut être utilisée dans l'instruction SELECT.

  • La clause OVER est autorisée avec la fonction NEXT VALUE FOR en cas d'utilisation dans une instruction SELECT ou une instruction INSERT … SELECT …. La fonction NEXT VALUE FOR n'est pas autorisée dans les instructions UPDATE ou MERGE.

  • Si un autre processus accède simultanément à l'objet séquence, les nombres retournés peuvent comporter des espaces vides.

Métadonnées

Pour plus d'informations sur les séquences, interrogez l'affichage catalogue sys.sequences.

Sécurité

Autorisations

Requiert l'autorisation UPDATE sur l'objet séquence ou le schéma de la séquence. Pour obtenir un exemple d'octroi d'une autorisation, consultez l'exemple F dans la suite de cette rubrique.

Chaînage des propriétés

Les objets séquences prennent en charge le chaînage des propriétés. Si l'objet séquence a le même propriétaire que la procédure stockée appelante, le déclencheur ou la table (ayant un objet séquence comme contrainte par défaut), aucun contrôle d'autorisation n'est obligatoire sur l'objet séquence. Si l'objet séquence n'est pas détenu par le même utilisateur que la procédure stockée appelante, le déclencheur ou la table, un contrôle d'autorisation est obligatoire sur l'objet séquence.

Lorsque la fonction NEXT VALUE FOR est utilisée comme valeur par défaut dans une table, les utilisateurs ont besoin à la fois de l'autorisation INSERT sur la table et de l'autorisation UPDATE sur l'objet séquence, pour insérer des données à l'aide de la valeur par défaut.

  • Si la contrainte par défaut a le même propriétaire que l'objet séquence, aucune autorisation n'est obligatoire sur l'objet séquence lorsque la contrainte par défaut est appelée.

  • Si la contrainte par défaut et l'objet séquence ne sont pas détenus par le même utilisateur, des autorisations sont requises sur l'objet séquence même s'il est appelé via la contrainte par défaut.

Audit

Pour auditer la fonction NEXT VALUE FOR, surveillez SCHEMA_OBJECT_ACCESS_GROUP.

Exemples

Pour obtenir des exemples de création de séquences et d'utilisation de la fonction NEXT VALUE FOR pour générer des numéros séquentiels, consultez Numéros de séquence.

Les exemples suivants utilisent une séquence nommée CountBy1 dans un schéma nommé Test. Exécutez l'instruction suivante pour créer la séquence Test.CountBy1. Les exemples C et E utilisent la base de données AdventureWorks2012  ; par conséquent, la séquence CountBy1 est créée dans cette base de données.

USE AdventureWorks2012 ;
GO

CREATE SCHEMA Test;
GO

CREATE SEQUENCE Test.CountBy1
    START WITH 1
    INCREMENT BY 1 ;
GO

A.Utilisation d'une séquence dans une instruction SELECT

L'exemple suivant crée une séquence nommée CountBy1 qui augmente de 1 chaque fois qu'elle est utilisée.

SELECT NEXT VALUE FOR Test.CountBy1 AS FirstUse;
SELECT NEXT VALUE FOR Test.CountBy1 AS SecondUse;

Voici l'ensemble des résultats.

FirstUse

1

SecondUse

2

B.Définition d'une variable sur la valeur de séquence suivante

L'exemple suivant illustre trois façons d'affecter à une variable la valeur suivante d'un numéro séquentiel.

DECLARE @myvar1 bigint = NEXT VALUE FOR Test.CountBy1
DECLARE @myvar2 bigint ;
DECLARE @myvar3 bigint ;
SET @myvar2 = NEXT VALUE FOR Test.CountBy1 ;
SELECT @myvar3 = NEXT VALUE FOR Test.CountBy1 ;
SELECT @myvar1 AS myvar1, @myvar2 AS myvar2, @myvar3 AS myvar3 ;
GO

C.Utilisation d'une séquence avec une fonction de fenêtre de classement

USE AdventureWorks2012 ;
GO

SELECT NEXT VALUE FOR Test.CountBy1 OVER (ORDER BY LastName) AS ListNumber,
    FirstName, LastName
FROM Person.Contact ;
GO

D.Utilisation de la fonction NEXT VALUE FOR dans la définition d'une contrainte par défaut

L'utilisation de la fonction NEXT VALUE FOR dans la définition d'une contrainte par défaut est prise en charge. Pour obtenir un exemple d'utilisation NEXT VALUE FOR dans une instruction CREATE TABLE, consultez l'exemple c dans Numéros de séquence. L'exemple suivant utilise ALTER TABLE pour ajouter une séquence comme valeur par défaut à une table actuelle.

CREATE TABLE Test.MyTable
(
    IDColumn nvarchar(25) PRIMARY KEY,
    name varchar(25) NOT NULL
) ;
GO

CREATE SEQUENCE Test.CounterSeq
    AS int
    START WITH 1
    INCREMENT BY 1 ;
GO

ALTER TABLE Test.MyTable
    ADD 
        DEFAULT N'AdvWorks_' + 
        CAST(NEXT VALUE FOR Test.CounterSeq AS NVARCHAR(20)) 
        FOR IDColumn;
GO

INSERT Test.MyTable (name)
VALUES ('Larry') ;
GO

SELECT * FROM Test.MyTable;
GO

E.Utilisation de la fonction NEXT VALUE FOR dans une instruction INSERT

L'exemple suivant crée une table nommée TestTable, puis utilise la fonction NEXT VALUE FOR pour insérer une ligne.

CREATE TABLE Test.TestTable
     (CounterColumn int PRIMARY KEY,
    Name nvarchar(25) NOT NULL) ; 
GO

INSERT Test.TestTable (CounterColumn,Name)
    VALUES (NEXT VALUE FOR Test.CountBy1, 'Syed') ;
GO

SELECT * FROM Test.TestTable; 
GO

E.Utilisation de la fonction NEXT VALUE FOR avec SELECT … INTO

L'exemple suivant utilise l'instruction SELECT … INTO pour créer une table nommée Production.NewLocation et utilise la fonction NEXT VALUE FOR pour numéroter chaque ligne.

USE AdventureWorks2012 ; 
GO

SELECT NEXT VALUE FOR Test.CountBy1 AS LocNumber, Name 
    INTO Production.NewLocation
    FROM Production.Location ;
GO

SELECT * FROM Production.NewLocation ;
GO

F.Octroi d'une autorisation pour exécuter NEXT VALUE FOR

L'exemple suivant accorde l'autorisation UPDATE à un utilisateur nommé AdventureWorks\Larry afin de l'autoriser à exécuter NEXT VALUE FOR à l'aide de la séquence Test.CounterSeq.

GRANT UPDATE ON OBJECT::Test.CounterSeq TO [AdventureWorks\Larry] ;

Voir aussi

Référence

CREATE SEQUENCE (Transact-SQL)

ALTER SEQUENCE (Transact-SQL)

Concepts

Numéros de séquence