Gérer les duplicates d'une valeur par défaut

Salut,

j'ai une fonction qui génère un code alphanumérique aléatoire dont je me sert pour faire un clé primaire. C'est un peu la même chose que quand on met un (newid()) mais cela renvoi un format différent.
Mon problème est de savoir comment SQL Server peut gérer l'aléa qui verrait une clé déjà existante proposée par la fonction. J'ai vérifié avec un code à un chiffre, au bout de quelques essai, l'aléa se produit cela renvoie une violation de la contrainte PRIMARY KEY.

La question est comment programmer SQL Server pour qu'il redemande un nouveau code lorsqu'il a une violation de la contrainte PRIMARY KEY ?

Merci d'avance, Nico.

Bonjour,

Pourquoi utilisez-vous une fonction alors que SQL Server peut gérer cela : identité, séquence...etc.?

Citation:

---

Envoyé par taibag

Bonjour,

Pourquoi utilisez-vous une fonction alors que SQL Server peut gérer cela : identité, séquence...etc.?

---

Bonjour,

A ma connaissance, IDENTITY (https://msdn.microsoft.com/fr-fr/library/ms186775.aspx) génère des identifiants numériques et ne garanti pas l'unicité de la valeur.
Pour mon besoin je souhaite un code unique, alphanumérique composé de majuscules, minuscules et chiffres pour réduire la longueur de la chaine (un code à 3 chiffres donne 1000 combinaisons, là 238 328)

A priori, il me faut mettre en place un trigger INSTEAD OF INSERT qui vérifie que la valeur n'existe pas et la change le cas échéant. Mais pour l'instant je ne sais pas le faire.

Je continue à ramer :)

Résolu !

voilà comment j'ai fait :

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28

ALTER TRIGGER [dbo].[MyTrigger] ON [dbo].[Table_1] -- On intercepte avant l'insertion avec INSTEAD OF INSERT INSTEAD OF INSERT AS -- On declare une variable pour stocker le code à utiliser dans l'éventualité d'un doublon DECLARE @Code nvarchar(50) Set @code = dbo.Code(1)   -- On teste si il y a doublon if NOT exists ( select * from Table_1 t inner join inserted i on i.id=t.id) -- Il n'y a pas de doublon on insert la ligne dans la table Begin INSERT INTO Table_1 SELECT * FROM inserted end else -- Il y a doublon, il faut chercher un identifiant inutilisé begin -- On lance une boucle qui teste des identifiants WHILE exists( SELECT * FROM Table_1 WHERE (id = @code)) Begin Set @code = dbo.Code(1) End -- On insert la ligne avec le code que la boucle a trouvé INSERT INTO Table_1 SELECT @code,val FROM inserted end

---

Pour tester, j'ai utilisé un code à 1 caractère (dbo.code(1)) qui me permettait d'arriver à saturer rapidement mon système. Lorsque la table approche des 62 lignes, les performances se dégradent énormément car la probabilité de trouver un code inutilisé devient très mince. Dans mon appli je vais utiliser des identifiants à 12 caractères soit 3226266762397899821056 combinaisons, Je peux dormir tranquille :)

Bonjour,

Citation:

---

Envoyé par Coroebus

Dans mon appli je vais utiliser des identifiants à 12 caractères soit 3226266762397899821056 combinaisons, Je peux dormir tranquille

---

Pas sûr... vous allez surement être réveillé en pleine nuit quand les performances vont se dégrader subitement !

12 caractères en nvarchar, ça fait 26 octets, là où un INT aurait suffit, et n'occuperait que 4 octets.
Par ailleurs, vos clefs primaires ne sont de fait pas linéaires, votre index cluster va se fragmenter rapidement, et les requêtes dans votre trigger ne seront pas efficaces.

Bref, pourquoi ne pas utiliser un INT avec IDENTITY pour vos clef primaires ? Quel est le besoin qui justifie la génération de ce code "maison" qui plombera tôt ou tard vos performances ?

Citation:

---

Envoyé par Coroebus

Bonjour,

A ma connaissance, IDENTITY (https://msdn.microsoft.com/fr-fr/library/ms186775.aspx) génère des identifiants numériques et ne garanti pas l'unicité de la valeur.
Pour mon besoin je souhaite un code unique, alphanumérique composé de majuscules, minuscules et chiffres pour réduire la longueur de la chaine (un code à 3 chiffres donne 1000 combinaisons, là 238 328)

A priori, il me faut mettre en place un trigger INSTEAD OF INSERT qui vérifie que la valeur n'existe pas et la change le cas échéant. Mais pour l'instant je ne sais pas le faire.

Je continue à ramer :)

---

Aucun système d'auto incrément ne garantie l'unicité Ce n'est pas son rôle ! En effet, vous devez pouvoir forcer une valeur ou réinitialiser les valeurs. Mais si vous ne faite ni l'un ni l'autre les valeurs seront éternellement en séquence.
En sus un code alpha est beaucoup plus couteux en traitement qu'un nombre, du fait de la manière de stocker la chose et de la collation.
Enfin, en utilisant un système personnel il faut générer les valeurs dans une transaction, sinon vous aurez des doublons, du fait de la concurrence d'accès !

Bref, vous allez au devant de problématiques catastrophiques et de performances lamentables !

A +

Merci pour les remarques, c'est très intéressant.

A la réflexion, mon besoin n'est pas nécessairement celui d'une clé primaire. Cet identifiant doit servir de clé dans un URL pour un lien.
Je peux effectivement avoir un IDENTITY classique et un champs qui contient le code.

Pour une URL, le mieux serait d'utiliser un GUID avec l'attribut ROWGUIDCOL.

Code:

---

1 2	ALTER TABLE T ADD URL_CODE UNIQUEIDENTIFIER ROWGUIDCOL UNIQUE DEFAULT NEWID()

---

C'est spécialement conçu pour ce genre de cas.

A +

Bien noté ! Merci de ton aide :)