Comment puis-je remplir une colonne de nombres aléatoires dans SQL? J'obtiens la même valeur dans chaque ligne

Si vous êtes sur un Serveur SQL server 2008, vous pouvez également utiliser

 CRYPT_GEN_RANDOM(2) % 10000

Qui semble un peu plus simple (il est également évaluée une fois par ligne comme newid est - illustré ci-dessous)

DECLARE @foo TABLE (col1 FLOAT)

INSERT INTO @foo SELECT 1 UNION SELECT 2

UPDATE @foo
SET col1 =  CRYPT_GEN_RANDOM(2) % 10000

SELECT *  FROM @foo

Retourne (2 aléatoire probablement différents numéros)

col1
----------------------
9693
8573

Ressasser la inexpliquée downvote la seule raison légitime, je pense que, parce que le nombre aléatoire généré est entre 0-65535 qui n'est pas divisible par 10 000 certains numéros seront légèrement sur-représentées. Un moyen de contourner ce serait l'envelopper dans un scalaire de l'UDF, qui jette un nombre de plus de 60 000 personnes et appelle récursivement pour obtenir un nouveau numéro.

CREATE FUNCTION dbo.RandomNumber()
RETURNS INT
AS
  BEGIN
      DECLARE @Result INT

      SET @Result = CRYPT_GEN_RANDOM(2)

      RETURN CASE
               WHEN @Result < 60000
                     OR @@NESTLEVEL = 32 THEN @Result % 10000
               ELSE dbo.RandomNumber()
             END
  END  

J'ai testé 2 définir en fonction des méthodes de randomisation contre RAND() par la génération de 100,000,000 lignes avec chaque. Au niveau de la sortie est un flottant entre 0 et 1 pour imiter RAND(). La plupart du code est l'infrastructure de test donc je résume les algorithmes ici:

-- Try #1 used
(CAST(CRYPT_GEN_RANDOM(8) AS BIGINT)%500000000000000000+500000000000000000.0)/1000000000000000000 AS Val
-- Try #2 used
RAND(Checksum(NewId()))
-- and to have a baseline to compare output with I used
RAND() -- this required executing 100000000 separate insert statements

À l'aide de CRYPT_GEN_RANDOM était clairement le plus aléatoire, car il n'existe qu'un .000000001% de chance de voir même 1 en double lors de la cueillette 10^8 numéros à PARTIR d'un ensemble de 10^18 numéros. OIE nous ne devrions pas avoir vu tous les doublons et ce n'en avaient pas! Ce jeu de 44 secondes pour générer sur mon ordinateur portable.

Cnt     Pct
-----   ----
 1      100.000000  --No duplicates

D'Exécution de SQL Server Fois:
Le temps CPU = 134795 ms, temps écoulé = 39274 ms.

IF OBJECT_ID('tempdb..#T0') IS NOT NULL DROP TABLE #T0;
GO
WITH L0   AS (SELECT c FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) AS D(c))  -- 2^4  
    ,L1   AS (SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B)    -- 2^8  
    ,L2   AS (SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B)    -- 2^16  
    ,L3   AS (SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B)    -- 2^32  
SELECT TOP 100000000 (CAST(CRYPT_GEN_RANDOM(8) AS BIGINT)%500000000000000000+500000000000000000.0)/1000000000000000000 AS Val
  INTO #T0
  FROM L3;

 WITH x AS (
     SELECT Val,COUNT(*) Cnt
      FROM #T0
     GROUP BY Val
)
SELECT x.Cnt,COUNT(*)/(SELECT COUNT(*)/100 FROM #T0) Pct
  FROM X
 GROUP BY x.Cnt;

À près de 15 ordres de grandeur moins de hasard, cette méthode n'était pas tout à fait deux fois plus rapide, en seulement 23 secondes pour générer 100 M des nombres.

Cnt  Pct
---- ----
1    95.450254    -- only 95% unique is absolutely horrible
2    02.222167    -- If this line were the only problem I'd say DON'T USE THIS!
3    00.034582
4    00.000409    -- 409 numbers appeared 4 times
5    00.000006    -- 6 numbers actually appeared 5 times 

D'Exécution de SQL Server Fois:
Le temps CPU = 77156 ms, temps écoulé = 24613 ms.

IF OBJECT_ID('tempdb..#T1') IS NOT NULL DROP TABLE #T1;
GO
WITH L0   AS (SELECT c FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) AS D(c))  -- 2^4  
    ,L1   AS (SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B)    -- 2^8  
    ,L2   AS (SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B)    -- 2^16  
    ,L3   AS (SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B)    -- 2^32  
SELECT TOP 100000000 RAND(Checksum(NewId())) AS Val
  INTO #T1
  FROM L3;

WITH x AS (
    SELECT Val,COUNT(*) Cnt
     FROM #T1
    GROUP BY Val
)
SELECT x.Cnt,COUNT(*)*1.0/(SELECT COUNT(*)/100 FROM #T1) Pct
  FROM X
 GROUP BY x.Cnt;

RAND() seul est inutile pour la génération de la création de la base de référence pour comparer l'aléatoire a pris plus de 6 heures et a dû être redémarré plusieurs fois pour enfin obtenir le bon nombre de lignes de sortie. Il semble également que l'aléatoire laisse beaucoup à désirer bien que c'est mieux que d'utiliser de la somme de contrôle(newid()) afin de réamorcer chaque ligne.

Cnt  Pct
---- ----
1    99.768020
2    00.115840
3    00.000100  -- at least there were comparitively few values returned 3 times

En raison de la redémarre, le temps d'exécution n'a pas pu être capturé.

IF OBJECT_ID('tempdb..#T2') IS NOT NULL DROP TABLE #T2;
GO
CREATE TABLE #T2 (Val FLOAT);
GO
SET NOCOUNT ON;
GO
INSERT INTO #T2(Val) VALUES(RAND());
GO 100000000

WITH x AS (
    SELECT Val,COUNT(*) Cnt
     FROM #T2
    GROUP BY Val
)
SELECT x.Cnt,COUNT(*)*1.0/(SELECT COUNT(*)/100 FROM #T2) Pct
  FROM X
 GROUP BY x.Cnt;