Comment implémenter des séquences dans Microsoft SQL Server?

Sql Server 2012 a introduit SEQUENCE objets , qui vous permettent de générer des valeurs numériques séquentielles non associées à une table.

les créer est facile:

CREATE SEQUENCE Schema.SequenceName
AS int
INCREMENT BY 1 ;

exemple d'utilisation avant insertion:

DECLARE @NextID int ;
SET @NextID = NEXT VALUE FOR Schema.SequenceName;
-- Some work happens
INSERT Schema.Orders (OrderID, Name, Qty)
  VALUES (@NextID, 'Rim', 2) ;

Voir mon blog pour un regard en profondeur sur la façon d'utiliser les séquences:

http://sqljunkieshare.com/2011/12/11/sequences-in-sql-server-2012-implementingmanaging-performance /

comme sqljunkieshare correctement dit, à partir de SQL Server 2012 il ya un intégré SEQUENCE fonctionnalité.

la question initiale ne clarifie pas, mais je suppose que les exigences pour la séquence sont:

1. Il doit fournir un ensemble unique d'un nombre croissant de
2. si plusieurs utilisateurs demandent la valeur suivante de la séquence simultanément ils tous doit obtenir des valeurs différentes. En d'autres termes, l'unicité des valeurs générées est garantie quoi qu'il arrive.
3. en raison de la possibilité que certaines transactions peuvent être retranchées, il est possible que le résultat final des nombres générés aura des lacunes.

j'aimerais commenter l'énoncé de la question originale:

" D'ailleurs, en insérant une rangée et en demandant au DB ce que le nombre juste semble si hackish."

Eh bien, il n'y a pas grand chose que nous puissions faire ici. La base de données est un fournisseur de numéros séquentiels et la base de données gère tous ces problèmes de concurrence que vous ne pouvez pas gérer vous-même. Je ne vois pas d'autre alternative que de demander au DB la valeur suivante de la séquence. Il doit y avoir une atomique opération "donnez-moi la valeur suivante de la séquence" et seul DB peut fournir une telle atomique opération. Pas de code client peux vous garantir qu'il est le seul à travailler avec la séquence.

pour répondre à la question dans le titre" comment mettriez - vous en œuvre les séquences "- nous utilisons 2008, qui ne dispose pas de la fonctionnalité SEQUENCE , donc après quelques lectures sur ce sujet, je me suis retrouvé avec ce qui suit.

pour chaque séquence dont j'ai besoin je crée une table d'aide séparée avec seulement une colonne IDENTITY (de la même manière qu'en 2012 vous créeriez une séquence séparée objet.)

CREATE TABLE [dbo].[SequenceContractNumber]
(
    [ContractNumber] [int] IDENTITY(1,1) NOT NULL,

    CONSTRAINT [PK_SequenceContractNumber] PRIMARY KEY CLUSTERED ([ContractNumber] ASC)
)

vous pouvez spécifier la valeur de départ et l'incrément pour elle. Puis-je créer une procédure stockée qui retourne la valeur suivante de la séquence. La procédure démarrerait une transaction, insérerait une ligne dans la table helper, rappellerait la valeur d'identité générée et retournerait la transaction. Ainsi la table d'aide reste toujours vide.

CREATE PROCEDURE [dbo].[GetNewContractNumber]
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
    SET XACT_ABORT ON;

    DECLARE @Result int = 0;

    IF @@TRANCOUNT > 0
    BEGIN
        -- Procedure is called when there is an active transaction.
        -- Create a named savepoint
        -- to be able to roll back only the work done in the procedure.
        SAVE TRANSACTION ProcedureGetNewContractNumber;
    END ELSE BEGIN
        -- Procedure must start its own transaction.
        BEGIN TRANSACTION ProcedureGetNewContractNumber;
    END;

    INSERT INTO dbo.SequenceContractNumber DEFAULT VALUES;

    SET @Result = SCOPE_IDENTITY();

    -- Rollback to a named savepoint or named transaction
    ROLLBACK TRANSACTION ProcedureGetNewContractNumber;

    RETURN @Result;
END

peu de notes sur la procédure.

D'abord, il n'était pas évident comment insérer une ligne dans une table qui n'a qu'une colonne d'identité. La réponse est DEFAULT VALUES .

alors, je voulais que la procédure fonctionne correctement si elle était appelée dans une autre transaction. Le simple ROLLBACK retrace tout s'il y a des transactions imbriquées. Dans mon cas, je dois retourner Seulement INSERT dans la table d'aide, donc j'ai utilisé SAVE TRANSACTION .

opération de retour sans savepoint_name ou transaction_name renvoie au début de la transaction. Lors de la nidification transactions, ce même relevé renvoie toutes les transactions internes à la déclaration de transaction de départ la plus éloignée.

C'est ainsi que j'utilise la procédure (dans une autre grande procédure qui, par exemple, crée un nouveau contrat):

DECLARE @VarContractNumber int;
EXEC @VarContractNumber = dbo.GetNewContractNumber;

tout fonctionne très bien si vous avez besoin de générer des valeurs séquentielles un à la fois. Dans le cas de contrats, chaque contrat est créé individuellement, de sorte que cette approche fonctionne parfaitement. Je peux être sûr que tous les contrats ont toujours un numéro de contrat unique.

NB: juste pour éviter les questions possibles. Ces numéros de contrat s'ajoutent à la clé d'identité de substitution de ma table de contrats. La clé de substitution est une clé interne qui est utilisée pour l'intégrité référentielle. Le numéro de contrat généré est un numéro convivial qui est imprimé sur le contrat. En outre, le même tableau de contrats contient à la fois des contrats finaux et des propositions, qui peuvent devenir des contrats ou peuvent rester comme des propositions pour toujours. Tant les propositions que les contrats contiennent des données très similaires, c'est pourquoi ils sont conservés dans le même tableau. La proposition peut devenir un contrat en changeant simplement le drapeau dans une rangée. Les propositions sont numérotées à l'aide d'une séquence de chiffres séparée, pour laquelle j'ai un deuxième tableau SequenceProposalNumber et une deuxième procédure GetNewProposalNumber .

---

récemment, cependant, je suis tombé sur un problème. J'avais besoin de générer des valeurs de séquence dans un lot, plutôt qu'un par un.

j'ai besoin d'une procédure qui traiterait tous les paiements qui ont été reçus pendant un trimestre donné en une seule fois. Le résultat d'un tel traitement pourrait être ~20 000 transactions que je veux enregistrer dans le tableau Transactions . J'ai une conception similaire ici. Le tableau Transactions comporte la colonne interne IDENTITY que l'utilisateur final ne voit jamais et il a un numéro de Transaction human-friendly qui serait imprimé sur la déclaration. Donc, j'ai besoin d'un moyen de générer un certain nombre de valeurs uniques dans un lot.

essentiellement, j'ai utilisé la même approche, mais il ya peu de particularités.

Premièrement, il n'y a aucun moyen direct d'insérer plusieurs lignes dans un tableau avec une seule colonne IDENTITY . Bien qu'il y ait une solution de contournement par (ab)en utilisant MERGE , Je ne l'ai pas fait. l'utiliser à la fin. J'ai décidé qu'il était plus facile d'ajouter une colonne factice Filler . Ma table de séquence sera toujours vide, donc la colonne supplémentaire n'a pas vraiment d'importance.

la Table helper ressemble à ceci:

CREATE TABLE [dbo].[SequenceS2TransactionNumber]
(
    [S2TransactionNumber] [int] IDENTITY(1,1) NOT NULL,
    [Filler] [int] NULL,
    CONSTRAINT [PK_SequenceS2TransactionNumber] 
    PRIMARY KEY CLUSTERED ([S2TransactionNumber] ASC)
)

la procédure ressemble à ceci:

-- Description: Returns a list of new unique S2 Transaction numbers of the given size
-- The caller should create a temp table #NewS2TransactionNumbers,
-- which would hold the result
CREATE PROCEDURE [dbo].[GetNewS2TransactionNumbers]
    @ParamCount int -- not NULL
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
    SET XACT_ABORT ON;

    IF @@TRANCOUNT > 0
    BEGIN
        -- Procedure is called when there is an active transaction.
        -- Create a named savepoint
        -- to be able to roll back only the work done in the procedure.
        SAVE TRANSACTION ProcedureGetNewS2TransactionNos;
    END ELSE BEGIN
        -- Procedure must start its own transaction.
        BEGIN TRANSACTION ProcedureGetNewS2TransactionNos;
    END;

    DECLARE @VarNumberCount int;
    SET @VarNumberCount = 
    (
        SELECT TOP(1) dbo.Numbers.Number
        FROM dbo.Numbers
        ORDER BY dbo.Numbers.Number DESC
    );

    -- table variable is not affected by the ROLLBACK, so use it for temporary storage
    DECLARE @TableTransactionNumbers table
    (
        ID int NOT NULL
    );

    IF @VarNumberCount >= @ParamCount
    BEGIN
        -- the Numbers table is large enough to provide the given number of rows
        INSERT INTO dbo.SequenceS2TransactionNumber
        (Filler)
        OUTPUT inserted.S2TransactionNumber AS ID INTO @TableTransactionNumbers(ID)
        -- save generated unique numbers into a table variable first
        SELECT TOP(@ParamCount) dbo.Numbers.Number
        FROM dbo.Numbers
        OPTION (MAXDOP 1);

    END ELSE BEGIN
        -- the Numbers table is not large enough to provide the given number of rows
        -- expand the Numbers table by cross joining it with itself
        INSERT INTO dbo.SequenceS2TransactionNumber
        (Filler)
        OUTPUT inserted.S2TransactionNumber AS ID INTO @TableTransactionNumbers(ID)
        -- save generated unique numbers into a table variable first
        SELECT TOP(@ParamCount) n1.Number
        FROM dbo.Numbers AS n1 CROSS JOIN dbo.Numbers AS n2
        OPTION (MAXDOP 1);

    END;

    /*
    -- this method can be used if the SequenceS2TransactionNumber
    -- had only one identity column
    MERGE INTO dbo.SequenceS2TransactionNumber
    USING
    (
        SELECT *
        FROM dbo.Numbers
        WHERE dbo.Numbers.Number <= @ParamCount
    ) AS T
    ON 1 = 0
    WHEN NOT MATCHED THEN
    INSERT DEFAULT VALUES
    OUTPUT inserted.S2TransactionNumber
    -- return generated unique numbers directly to the caller
    ;
    */

    -- Rollback to a named savepoint or named transaction
    ROLLBACK TRANSACTION ProcedureGetNewS2TransactionNos;

    IF object_id('tempdb..#NewS2TransactionNumbers') IS NOT NULL
    BEGIN
        INSERT INTO #NewS2TransactionNumbers (ID)
        SELECT TT.ID FROM @TableTransactionNumbers AS TT;
    END

END

et c'est ainsi qu'il est utilisé (à l'intérieur d'une procédure stockée qui calcule les transactions):

-- Generate a batch of new unique transaction numbers
-- and store them in #NewS2TransactionNumbers
DECLARE @VarTransactionCount int;
SET @VarTransactionCount = ...

CREATE TABLE #NewS2TransactionNumbers(ID int NOT NULL);

EXEC dbo.GetNewS2TransactionNumbers @ParamCount = @VarTransactionCount;

-- use the generated numbers...
SELECT ID FROM #NewS2TransactionNumbers AS TT;

Il y peu de choses ici que d'exiger une explication.

je dois insérer un nombre donné de lignes dans le tableau SequenceS2TransactionNumber . J'utilise un helper Numbers table pour cela. Cette table tient simplement des nombres entiers de 1 à 100.000. Il est utilisé dans d'autres endroits du système. Je vérifie s'il y a assez de lignes dans la table Numbers et l'étends à 100.000 * 100.000 en se joignant à lui-même si nécessaire.

je dois sauvegarder le résultat du insérez en masse quelque part et passez-le à l'appelant d'une façon ou d'une autre. Une façon de passer une table en dehors de la procédure stockée est d'utiliser une table temporaire. Je ne peux pas utiliser le paramètre table-valued ici, parce qu'il est en lecture seule malheureusement. De plus, Je ne peux pas insérer directement les valeurs de séquence générées dans la table temporaire #NewS2TransactionNumbers . Je ne peux pas utiliser #NewS2TransactionNumbers dans la clause OUTPUT , parce que ROLLBACK va le nettoyer. Heureusement, les variables de la table ne sont pas affectées par le ROLLBACK .

ainsi, j'utilise la variable de table @TableTransactionNumbers comme destination de la clause OUTPUT . Puis je ROLLBACK le mouvement pour nettoyer la table de séquence. Ensuite, copiez les valeurs de séquence générées à partir de la variable de table @TableTransactionNumbers à la table temporaire #NewS2TransactionNumbers , parce que seule la table temporaire #NewS2TransactionNumbers peut être visible à l'appelant de la procédure stockée. La variable de table @TableTransactionNumbers n'est pas visible à l'appelant de la procédure stockée.

aussi, il est possible d'utiliser la clause OUTPUT pour envoyer la séquence générée directement à l'appelant (comme vous pouvez le voir dans la variante commentée qui utilise MERGE ). Il fonctionne bien par lui-même, mais j'ai eu besoin des valeurs générées dans une certaine table pour le traitement ultérieur dans la procédure stockée d'appel. Quand j'ai essayé quelque chose comme ceci:

INSERT INTO @TableTransactions (ID)
EXEC dbo.GetNewS2TransactionNumbers @ParamCount = @VarTransactionCount;

je recevais un message d'erreur

ne peut pas utiliser la déclaration de retour dans une instruction INSERT-EXEC.

mais, j'ai besoin de ROLLBACK à l'intérieur du EXEC , c'est pourquoi j'ai fini par avoir tant de tables temporaires.

après tout cela, comme ce serait agréable de passer à la dernière version de SQL server qui a un objet SEQUENCE propre.

une colonne D'identité est à peu près analogue à une séquence.

vous pouvez simplement utiliser de vieilles tables et les utiliser comme séquences. Cela signifie que vos inserts seraient toujours:

BEGIN TRANSACTION  
SELECT number from plain old table..  
UPDATE plain old table, set the number to be the next number  
INSERT your row  
COMMIT  

mais ne faites pas ça. Le verrouillage serait mauvais...

j'ai commencé sur SQL Server et pour moi, le schéma "sequence" D'Oracle ressemblait à un piratage. Je suppose que vous venez de l'autre côté et pour vous, et scope_identity() ressemble à un piratage.

oublie ça. Quand vous êtes à Rome, faites comme les Romains faire.

la façon dont j'ai utilisé pour résoudre ce problème était une table 'séquences' qui stocke toutes mes séquences et une procédure stockée 'nextval'.

Table Sql:

CREATE TABLE Sequences (  
    name VARCHAR(30) NOT NULL,  
    value BIGINT DEFAULT 0 NOT NULL,  
    CONSTRAINT PK_Sequences PRIMARY KEY (name)  
);

le PK_Sequences est utilisé juste pour être sûr qu'il n'y aura jamais de séquences avec le même nom.

De La Procédure Stockée Sql:

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'nextVal') AND type in (N'P', N'PC')) DROP PROCEDURE nextVal;  
GO  
CREATE PROCEDURE nextval  
    @name VARCHAR(30)  
AS  
    BEGIN  
        DECLARE @value BIGINT  
        BEGIN TRANSACTION  
            UPDATE Sequences  
            SET @value=value=value + 1  
            WHERE name = @name;  
            -- SELECT @value=value FROM Sequences WHERE name=@name  
        COMMIT TRANSACTION  
        SELECT @value AS nextval  
    END;  

insérer quelques séquences:

INSERT INTO Sequences(name, value) VALUES ('SEQ_Workshop', 0);
INSERT INTO Sequences(name, value) VALUES ('SEQ_Participant', 0);
INSERT INTO Sequences(name, value) VALUES ('SEQ_Invoice', 0);  

enfin obtenir la valeur suivante d'une séquence,

execute nextval 'SEQ_Participant';

quelques codes c# pour obtenir la valeur suivante de la table de séquence,

public long getNextVal()
{
    long nextval = -1;
    SqlConnection connection = new SqlConnection("your connection string");
    try
    {
        //Connect and execute the select sql command.
        connection.Open();

        SqlCommand command = new SqlCommand("nextval", connection);
        command.CommandType = CommandType.StoredProcedure;
        command.Parameters.Add("@name", SqlDbType.NVarChar).Value = "SEQ_Participant";
        nextval = Int64.Parse(command.ExecuteScalar().ToString());

        command.Dispose();
    }
    catch (Exception) { }
    finally
    {
        connection.Dispose();
    }
    return nextval;
}

dans SQL Server 2012, vous pouvez simplement utiliser

CREATE SEQUENCE

en 2005 et 2008, vous pouvez obtenir une liste arbitraire de nombres séquentiels en utilisant une expression de table commune.

voici un exemple (notez que L'option MAXRECURSION est importante):

DECLARE @MinValue INT = 1;
DECLARE @MaxValue INT = 1000;

WITH IndexMaker (IndexNumber) AS
(
    SELECT 
        @MinValue AS IndexNumber
    UNION ALL SELECT 
        IndexNumber + 1
    FROM
        IndexMaker
    WHERE IndexNumber < @MaxValue
)
SELECT
    IndexNumber
FROM
    IndexMaker
ORDER BY
    IndexNumber
OPTION 
    (MAXRECURSION 0)

Les séquences

mises en œuvre par Oracle nécessitent un appel à la base de données avant l'insertion. les identités mises en œuvre par SQL Server nécessitent un appel à la base de données après l'insertion.

L'un n'est pas plus hackish que l'autre. L'effet net est le même - une dépendance sur la mémoire de données pour fournir des valeurs clés artificielles uniques et (dans la plupart des cas) deux appels à la mémoire.

je suppose que votre modèle relationnel est basé sur artificiel touches, et, dans ce contexte, je vais vous proposer de faire l'observation suivante:

nous ne devrions jamais chercher à donner un sens à des clés artificielles; leur seul but devrait être de relier des documents connexes.

Quels sont vos besoins en ce qui concerne la commande de données? peut-elle être traitée dans la vue (présentation) ou est-ce un attribut réel de vos données qui doit être persisté?

crée une table d'étape avec un identifiant dessus.

avant de charger la table des étapes, tronquer et réensemencer l'identificateur pour commencer à 1.

Chargez votre table. Chaque ligne a maintenant une valeur unique de 1 à N.

créer une table qui contient des numéros de séquence. Il peut s'agir de plusieurs rangées, une pour chaque séquence.

recherche le numéro de séquence du tableau de séquence que vous avez créé. Mettre à jour le numéro seqence en ajoutant le nombre de lignes dans la table le numéro de séquence.

mettez à jour l'Identificateur de table d'étape en ajoutant le numéro de référence que vous avez recherché. C'est une question facile étape du processus. ou Chargez votre table cible, ajoutez le numéro de séquence à l'identificateur pendant que vous chargez dans ETL. Cela permet de profiter de la chargeuse en vrac et d'autres transformations.

Considérons l'extrait suivant.

CREATE TABLE [SEQUENCE](
    [NAME] [varchar](100) NOT NULL,
    [NEXT_AVAILABLE_ID] [int] NOT NULL,
 CONSTRAINT [PK_SEQUENCES] PRIMARY KEY CLUSTERED 
(
    [NAME] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE PROCEDURE CLAIM_IDS (@sequenceName varchar(100), @howMany int)
AS
BEGIN
    DECLARE @result int
    update SEQUENCE
        set
            @result = NEXT_AVAILABLE_ID,
            NEXT_AVAILABLE_ID = NEXT_AVAILABLE_ID + @howMany
        where Name = @sequenceName
    Select @result as AVAILABLE_ID
END
GO

comme sqljunkiesshare États , séquences ont été ajoutées à SQL Server 2012. Voici comment le faire dans l'interface graphique. C'est l'équivalent de:

CREATE SEQUENCE Schema.SequenceName
AS int
INCREMENT BY 1 ;

1. Dans le de l'Explorateur d'Objets , développez le Programmabilité dossier
2. sous le dossier programmabilité , clic droit sur les séquences 151980920" dossier comme indiqué ci-dessous:

3. sont soulignées les valeurs que vous mettriez à jour pour obtenir le équivalent de la déclaration SQL ci-dessus, cependant, je considère changer ces derniers en fonction de vos besoins (voir les notes ci-dessous).

Notes:

• la valeur de départ par défaut, la valeur minimale et la valeur maximale ont été déterminées par la plage du type de données qui était un int dans ce cas. voir ici pour plus de plages de types de données si vous voulez utiliser quelque chose d'autre qu'un int .

• il y a de fortes chances que vous vouliez que votre séquence commence à 1 et que vous vous devriez aussi avoir une valeur minimum de 1.