Générer des codes d'activation avec SSIS ou SQL SERVER 2005

Version imprimable

Bonjour !

Est -il possible de générer un certain nb d'enregistrements (par ex 10 000) dans une table Matable (nid,sserialnumber,ncode) avec des valeurs aléatoires non redondantes de 10 chiffres pour le serialnumber et pour le code
Code:

1 2 3 4 5 6 7 8 9 10 Ex: nID sserialnumber ncode 1 8745125487 658789512 2 1235478954 145874596 3 9996655212 666548745 . . . . . . . . .
mais sans utiliser une interface web juste avec SQL SERVER ou SSIS ???

d'avance merci

Bonjour,

Je vous propose le très tordu code suivant :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
WITH 
	CTE_RANDOM (Random, Indice) AS
	(
			SELECT REVERSE(REPLACE(SUBSTRING(CAST(ABS(CAST(CAST(NEWID() AS VARBINARY) AS BIGINT)) AS VARCHAR), CAST(RAND() * 10 AS INT), 10), '0', CAST(CAST(RAND() * 10 AS TINYINT) AS CHAR(1)))), 1
		UNION ALL
			SELECT REVERSE(REPLACE(SUBSTRING(CAST(ABS(CAST(CAST(NEWID() AS VARBINARY) AS BIGINT)) AS VARCHAR), CAST(RAND() * 10 AS INT), 10), '0', CAST(CAST(RAND() * 10 AS TINYINT) AS CHAR(1)))), Indice + 1
			FROM CTE_RANDOM
			WHERE Indice < 10000
	)
SELECT Random
FROM CTE_RANDOM
WHERE Random NOT LIKE '0%'
OPTION (MAXRECURSION 0)

Il est un peu abscons mais il a le mérite de produire des nombres assez aléatoires.

@++ ;)

28/11/2008, 11h31
magicsam

Merci elsuket !!

comment faire pour insérer les nombres directement dans une table 'Matable' ??

d'avance merci

Bonjour,

Essayez ceci :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
WITH 
	CTE_RANDOM (Random, Indice) AS
	(
			SELECT REVERSE(REPLACE(SUBSTRING(CAST(ABS(CAST(CAST(NEWID() AS VARBINARY) AS BIGINT)) AS VARCHAR), CAST(RAND() * 10 AS INT), 10), '0', CAST(CAST(RAND() * 10 AS TINYINT) AS CHAR(1)))), 1
		UNION ALL
			SELECT REVERSE(REPLACE(SUBSTRING(CAST(ABS(CAST(CAST(NEWID() AS VARBINARY) AS BIGINT)) AS VARCHAR), CAST(RAND() * 10 AS INT), 10), '0', CAST(CAST(RAND() * 10 AS TINYINT) AS CHAR(1)))), Indice + 1
			FROM CTE_RANDOM
			WHERE Indice < 10000
	)
INSERT INTO maTable (maColonne)
SELECT Random
FROM CTE_RANDOM
WHERE Random NOT LIKE '0%'
OPTION (MAXRECURSION 0)

@++ ;)

02/12/2008, 13h21
cavo789

Pas mal ! :king:

Bonjour, je remonte ce vieux sujet, car il m'a bien aidé, mais j'ai un soucis de performance avec une contrainte supplémentaire : l'unicité.

Pour le moment, j'ai créé une procédure qui me retourne un code unique :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 CREATE PROC GETUNIQUENEWCODE @test varchar(12) OUTPUT as BEGIN declare @cpt int; select @cpt=1; WHILE(@cpt > 0) BEGIN select @test = SUBSTRING(CAST(ABS(CAST(CAST(NEWID() AS VARBINARY) AS BIGINT)) as varchar),0,12) select @cpt = count(*) from Code where COD_CODE_SECRET = @test END RETURN 0 END
Cependant cette procédure va perdre de plus en plus en performance au fur et à mesure de la quantité de code en base.
La volumétrie étant de deux millions de codes par ans environ...

Existe-t-il des mécanismes intégré à SQL server pour faire mieux ?

J'ai bien pensé à la contrainte d'unicité sur colonne, mais elle ne s'applique bien sur qu'après l'insertion, et la gestion du code erreur me parait hasardeuse...

Les contraintes sont :
- je dois avoir un code secret unique et non prévisible pour chaque ligne insérée, de 12 caractères numériques

la génération d'un ensemble de code se fait par lot de 50,000 environ, raison de mon interrogation sur les performances...

Bonsoir,

Ca me semble pas mal.

Voici un petit test qui prépare 100000 de lignes à l'avance: 2 doublons chez moi.

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
29
30
31
32
33
34
35
36
37
--Creation d'une table de nombres.
CREATE TABLE tbNumbers 
(
Number INT NOT NULL,	
CONSTRAINT PK_Numbers PRIMARY KEY CLUSTERED (Number)		
WITH FILLFACTOR = 100
)
 
--Remplissage -> 65536.
INSERT INTO tbNumbers
SELECT	(a.Number * 256) + b.Number AS Number
FROM 
(SELECT number FROM master..spt_values	WHERE type = 'P' AND number <= 255) a (Number),
(SELECT number FROM master..spt_values	WHERE type = 'P' AND number <= 255) b (Number)
 
--Un table de codes d'activation, rajouter un flag bUsed, ...
CREATE TABLE tbCodeActivation(ID int IDENTITY PRIMARY KEY, Guid UNIQUEIDENTIFIER, Code BIGINT)
 
 
--Remplissage de cette table avec 1000000 de lignes.
;WITH CTE (Guid) AS 
(
SELECT NEWID() 
FROM 
(SELECT Number FROM dbo.tbNumbers WHERE Number <= 49999) a CROSS JOIN
(SELECT Number FROM dbo.tbNumbers WHERE Number <= 19) b
)
INSERT INTO tbCodeActivation (Guid, Code)
SELECT Guid, ABS(CAST(CAST(Guid AS VARBINARY) AS BIGINT))
FROM CTE
--25 secs sur mon portable
 
-- Y a t'il des doublons ?
SELECT SUBSTRING(CAST(ABS(Code) AS VARCHAR), 1, 12), Count(*) 
FROM tbCodeActivation
GROUP BY SUBSTRING(CAST(ABS(Code) AS VARCHAR), 1, 12)
HAVING COUNT(*) > 1

25/05/2009, 20h58
elsuket

Bonjour,

Citation:

Existe-t-il des mécanismes intégré à SQL server pour faire mieux ?

Non, du moins je ne pense pas.

J'avais fait quelques tests, les doublons représentent à peu près un pourcent du cardinal généré. Mais dans votre cas, cela n'est pas acceptable.

Par nature SQL n'est pas un langage itératif (mais ensembliste).
Les moteurs SQL se sortent assez bien des itérations sur de faibles volumes de données, mais ne sont clairement pas conçus pour cela.

Vous avez deux possibilités :

- utiliser la fonction NEWSEQUENTIALID, qui vous garantira l'unicité lors de la génération, mais pas avec les valeurs déjà contenues dans la table, que vous devrez comparer. De plus cela ne sera pas très performant.

- écrire une procédure stockée d'assembly en .NET, qui produira le résultat bien plus rapidement que SQL Server.
Vous pouvez requêter la table dans l'assembly, et vous garantir l'unicité dans le code de votre procédure stockée d'assembly.
A mon avis au niveau vitesse d'exécution, y'a pas photo.

@++ ;)
25/05/2009, 21h05
agemis31

Une petite précision,

Attention à NEWSEQUENTIALID(), ca va à l'encontre du but recherché car la séquence est déterministe.

@+
26/05/2009, 11h11
echtelion

Merci pour vos réponses.

Concernant NEWSEQUENTIALID(), en plus d'être potentiellement prédictive, ma contrainte de longueur implique que le traitement que je réalise pour raccourcir le Guid ne me garantit plus l'unicité.

Je vais faire des tests de charge et si ce n'est vraiment pas acceptable me tourner vers une éventuelle solution CLR, que je ne maitrise pas trop malheureusement.
29/05/2009, 18h39
echtelion

Bon, j'ai tout traité par programmation au final, avec table de hash des codes existant, et création de codes avec vérif dans ladite table.

beaucoup plus rapide...
2 secondes de générations pour 100k codes.
214 secondes ensuite pour l'insertion sql.