Discussion :

[Coroebus]

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.

[taibag]

Bonjour,

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

[Coroebus]

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

[Coroebus]

Résolu !

voilà comment j'ai fait :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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

[aieeeuuuuu]

Bonjour,

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 ?

[SQLpro]

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 +

[Coroebus]

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.

[SQLpro]

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

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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 +