Discussion :

[magicsam]

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 :

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

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

[elsuket]

Bonjour,

Je vous propose le très tordu code suivant :

Code :

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

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.

@++

[magicsam]

Merci elsuket !!

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

d'avance merci

[elsuket]

Bonjour,

Essayez ceci :

Code :

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

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)

@++

[cavo789]

Pas mal !

[echtelion]

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 :

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

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...

[agemis31]

Bonsoir,

Ca me semble pas mal.

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

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
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

@+

[elsuket]

Bonjour,

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.

@++

[agemis31]

Une petite précision,

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

@+

[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.

[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.