Séquences de numérotation flexibles dans t-sql – codeproject invest bitcoins

Souvent, une application a besoin d’une méthode robuste pour générer des nombres uniques. Quelques exemples seraient les numéros de facture, les numéros de commande et numéros de reçus de dons. Ces séquences de numérotation contiennent souvent une partie non numérique, par exemple, en ligne reçu de don des nombres comme ‘WD-00000121’. Contexte

Dans les «anciens» jours (lorsque nous utilisions du papier), il y avait des blocs de papier préimprimés avec une séquence de chiffres, par exemple des chèques de compte bancaire. Lorsque vous retirez la feuille la plus haute (ou vérifiez), vous revendiquez effectivement ce numéro de séquence – vous le retirez du clavier, et personne d’autre ne peut l’utiliser. Dans cette solution SQL flexible, lorsque nous revendiquons le numéro suivant dans la séquence, nous faisons essentiellement la même chose.


Bien que le code de l’article soit écrit pour Microsoft SQL Server TSQL, cette technique peut être appliquée à la plupart des bases de données relationnelles modernes. Par exemple, Oracle a une fonctionnalité appelée ‘Sequence’ qui est bonne mais qui n’est pas abordée dans cet article (j’ai utilisé le produit de base de données Oracle et je le respecte énormément en tant que produit, mais dernièrement, tout mon travail a été MS Serveur SQL). La solution simple

Une solution simple consiste à insérer dans une table qui a une colonne IDENTITY définie, et SQL Server affectera automatiquement le numéro suivant dans la séquence à votre nouvel enregistrement, par exemple: – Ceci est un code partiel, n’essayez pas d’exécuter cet extrait de code c’est juste pour

L’avantage de cette séquence est qu’elle est automatique, facile à mettre en œuvre et garantie par SQL Server pour être unique. Je n’ai rien contre l’utilisation des colonnes IDENTITY en soi; et bien que je les aie souvent utilisés là où cela a du sens, je préfère une clé significative, lisible par l’homme et pertinente pour l’être humain, lorsque cela est possible.

Aussi, par exemple, si l’application exigeait que reçu de don les chiffres commencent à 00001 chaque mois, une colonne d’identité serait difficile à utiliser; Par exemple, les dons de février 2009 commenceraient à "W-200902-00001", Mars 2009 commencerait à "W-200903-00001". La solution flexible

Une solution plus flexible à ce problème devrait maintenir la séquence réelle en dehors de la table où elle est utilisée. Certaines métadonnées pour la séquence devraient également être stockées quelque part. Une autre exigence pour certaines applications est qu’elles nécessitent plus d’une séquence ou d’un type de séquence.

La colonne " LastSequence" détient le dernier numéro de séquence utilisé. Il est incrémenté dans la procédure stockée lorsqu’il calcule et revendique ensuite le numéro suivant. Pour démarrer une séquence à 1 (et incrémenter de 1), la valeur initiale serait zéro.

La colonne " SequenceFormat" contient un modèle de la façon dont nous voulons que cette séquence ressemble à la fin. Il peut contenir un certain nombre de balises remplaçables. Il est obligatoire d’avoir le "[#]" tag car c’est là que l’entier incrémenté sera placé. Les autres balises possibles que vous pourriez ajouter sont les parties date et heure ou l’utilisateur SQL.

Si nous avons besoin de produire une nouvelle séquence chaque mois, nous le coderions pour tenter un INSERT dans la table SequenceControl pour ce mois (par exemple, pour février 2009), puis l’utiliserions. INSERT INTO SequenceControl (SequenceKey, LastSequence, SequenceFormat, ZeroPadToDigits)

Pour cet exemple (chaque mois, une séquence de 00001), cela n’a pas d’importance si l’INSERT a échoué en raison d’une violation de clé primaire; de toute façon, la clé serait dans la table et nous l’utiliserions simplement: EXEC DBO.P_GetNextInSequence ‘donations 2009 02’; Points d’interêts

La procédure stockée peut également être refactorisée dans une fonction de base de données (UDF) qui renvoie simplement le numéro de séquence varchar final formaté. Cela serait utile si vous avez besoin de demander le numéro suivant à partir de votre code SQL, par exemple, si vous avez une procédure stockée pour ajouter le nouveau don au lieu de [votre code de programmation préféré].

À l’intérieur d’un fichier UDF, vous ne pouvez pas modifier la base de données. Par conséquent, cette procédure stockée ne peut pas être utilisée comme une fonction définie par l’utilisateur de la base de données. Apparemment, il existe des hacks pour le faire de toute façon, mais comme les hacks vont, c’est probablement une mauvaise idée pour de nombreuses raisons de vaincre cette règle dans SQL Server. Merci à drbarriesmith d’avoir posé une bonne question.

Ils avaient une procédure stockée appelée "get_next_number" et une table qui maintenait la séquence d’ID pour divers autres éléments. Le problème, c’est que les clients qui n’allaient pas utiliser la nouvelle pièce d’identité tout de suite appelaient souvent le proc stocké et parfois ne l’utilisaient pas du tout. C’était une source de lacunes dans la numérotation. L’autre source de problèmes était une condition de concurrence simple – deux ou plusieurs processus essayaient d’obtenir le prochain id, et ce qui se produirait, ni l’un ni l’autre ne pourrait "confirmer" le nouvel ID car il a été fait en deux étapes – les deux processus tireraient le même nouvel ID exactement au même moment, puis quand l’ID serait vérifié, il serait trouvé en cours d’utilisation, donc le proc essayerait le suivant – et ainsi l’autre processus. Finalement, il est arrivé au point où nous allions déborder de la colonne entière.

Puisque vous utilisez un verrouillage correct dans ce scénario, cela sera probablement correct, mais c’est une bonne idée de charger le test. Comme vous pouvez le voir, le système décrit ci-dessus conviendra à un petit nombre d’utilisateurs, mais lorsque cette condition de concurrence se produit, c’est vraiment mauvais.

Si l’objectif est d’utiliser des identifiants de la forme mmmm-nnnnn, et s’il n’y aura pas d’insertions, qu’en est-il de l’utilisation d’une colonne d’identité avec une colonne indiquant le mois (ou autre) et une colonne indiquant la colonne d’identité avait pour le dernier article du mois précédent? Cela devrait permettre de gérer les mises à jour simultanées sans difficulté, sauf dans quelques scénarios où la difficulté devrait être légère:

-1- Si quelqu’un essaie d’ajouter un enregistrement pour l ‘«ancien mois» juste après que quelqu’un d’autre verrouille la valeur d’identité actuelle dans «dernier élément du mois précédent», la valeur d’index du mois suivant peut commencer plus haut que d’habitude. Cette situation ne devrait pas causer de difficulté particulière (je ne pense pas que les valeurs des colonnes d’identité soient garanties exemptes de lacunes dans tout système où les transactions avortées peuvent être annulées et annulées); il pourrait être facilement détecté, cependant, dans le cas où une manipulation spéciale était nécessaire.

-2- Si près de deux mois se sont écoulés depuis que le dernier enregistrement a été écrit, l’une des tentatives pour mettre à jour la valeur du «dernier élément du mois précédent» échouerait probablement. La routine pour mettre à jour la valeur n’aurait aucune note qu’elle n’a pas réussi et réessayer si approprié.