Forum |  HardWare.fr | News | Articles | PC | S'identifier | S'inscrire | Shop Recherche
1266 connectés 

  FORUM HardWare.fr
  Programmation
  SQL/NoSQL

  [INFO] Limitations de la fonction RAND() de SQL Server

 
 


 Mot :   Pseudo :  
 
Bas de page
Auteur Sujet :

[INFO] Limitations de la fonction RAND() de SQL Server

n°1116319
Arjuna
Aircraft Ident.: F-MBSD
Posté le 10-06-2005 à 22:58:14  profilanswer
 

Salut,
Comme beaucoup de personnes qui ont bossé avec SQL Server, j'ai été confronté au problème de la génération d'un nombre aléatoire.
 
La fonction RAND() de T-SQL fait tout sauf de l'aléatoire, et c'est facilement vérifiable, y compris avec l'exemple de la documentation.
 
Déjà, au sein d'une même requête, RAND() produit un nombre unique pour toutes les lignes retournées, ce qui est loin d'être utile.
Ensuite, entre deux appels, quelque soit le temps qui se déroule entre eux, si on utilise le même SEED, on se retrouve avec une valeur quelque peut... identique. Le changement est perceptible (quand il l'est) à partir de la 4° ou 5° décimale... C'est peu.
 
Bref, cette fonction n'est vraiment pas utilisable.
 
Pourtant, SQL Server dispose d'un autre générateur de nombre aléatoires, mais il n'est pas vraiment utilisable... Parcequ'il ne retourne pas de nombre compris entre 0 et 1, mais un GUID (Identificateur global unique), qui est un nombre de 16... bytes, pas bits ! (128 bits).
Déjà, ça fait un peux gros, et en plus, c'est au format peut utilisable pour faire des calculs (que le processeur ne saurait de toute façon pas faire) : "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx".
 
Ceci dit, une solution simple pour générer un mot de passe aléatoire peut être de faire :
Right(newid(), 12) => 12 caractères hexa-décimaux
Left(newid(), 8) => 8 caractères hexa-decimaux
Ou si vous être parano, vous pouvez toujour faire :
Replace(newid(), '-', '') (32 caractères hexa)
 
Pour tromper l'ennemi, on peut s'amuser à les regrouper ces caractères 2 par deux, et générer le caractère ASCII associé (en vérifiant que le caractère est bien saisissable au clavier ! ;))
 
Pour ce faire, on pourra créer ces deux fonctions (je n'ai pas trouvé de moyen de convertir une chaîne hexa en decimal) :

Code :
  1. create function hexToDec
  2. (
  3. @val char(1)
  4. )
  5. returns tinyint
  6. as
  7. begin
  8. declare @tmp tinyint
  9. select @tmp = case
  10.        when @val between '0' and '9' then cast(@val as tinyint)
  11.        when @val = 'A' then 10
  12.        when @val = 'B' then 11
  13.        when @val = 'C' then 12
  14.        when @val = 'D' then 13
  15.        when @val = 'E' then 14
  16.        when @val = 'F' then 15
  17.        end
  18. return(@tmp)
  19. end
  20. go
  22. create procedure CreatePassword
  23. (
  24. @nbChar tinyint,
  25. @password varchar(255) output
  26. )
  27. as
  28. begin
  29. declare @cptGuidChar tinyint
  30. declare @guid as char(32)
  31. declare @ascii as tinyint
  33. set @password = ''
  34. set @cptGuidChar = 0
  35. while len(@password) < @nbChar
  36. begin
  37.  if @cptGuidChar = 0
  38.  begin
  39.   select @guid = Replace(Cast(newid() as char(36)), '-', '')
  40.   set @cptGuidChar = 32
  41.  end
  42.  set @ascii = dbo.hexToDec(substring(@guid, @cptGuidChar, 1)) * 16 + dbo.hexToDec(substring(@guid, @cptGuidChar - 1, 1))
  43.  set @cptGuidChar = @cptGuidChar - 2
  45.  set @password = rtrim(@password + (case
  46.  -- ! " # $ % & ' ( ) * + ' - . /
  47.  when @ascii between 33 and 47 then char(@ascii)
  48.  -- 0 1 2 3 4 5 6 7 8 9
  49.  when @ascii between 48 and 57 then char(@ascii)
  50.  -- : ; < = > ? @
  51.  when @ascii between 58 and 64 then char(@ascii)
  52.  -- A B C D E F G H I J K L M N O P Q R S T U V W X Y Z
  53.  when @ascii between 65 and 90 then char(@ascii)
  54.  -- [ \ ] ^ _ `
  55.  when @ascii between 91 and 96 then char(@ascii)
  56.  -- a b c d e f g h i j k l m n o p q r s t u v w x y z
  57.  when @ascii between 97 and 122 then char(@ascii)
  58.  -- { | } ~
  59.  when @ascii between 123 and 126 then char(@ascii)
  60.  else ' '
  61.  end))
  62. end
  63. end
  64. go


 
Note: On est obligé d'utiliser une procédure et non pas une fonction pour la génération du mot de passe car l'utilisation de newid() au sein d'une fonction est interdit (fonction non déterministique).
 
On peut ensuite utiliser la procédure comme suit :

Code :
  1. declare @pass varchar(255)
  2. exec CreatePassword 10, @pass output
  3. select @pass
  4. exec CreatePassword 10, @pass output
  5. select @pass
  6. exec CreatePassword 40, @pass output
  7. select @pass
  8. exec CreatePassword 40, @pass output
  9. select @pass


 
Résultat :

Code :
  1. vBvmO!dszt
  2. 3!:*TB(H$4
  3. " )+D~O>3T&ch9ldi7nX;m|xO_dKf0y:]v0BX`DWn
  4. @;^4E_@Yv'KVn#Z}1_VI]gf+DTM'W]rht8$rjZOF


 
Ca, c'est du mot de passe incrackable où je ne m'y connais pas ! :D
Vous noterez que la liste des caractères retenus est en commentaire dans la procédure, vous pouvez changer les intervals afin de trouver un bon compris entre du tout alphanumérique et du tout pas bô comme ça ;)
 
Bon, voilà, on a un générateur de mots de passe vraiment aléatoire (la demi-vie de la génération d'un GUID est d'environ... quelques centaines de milliers d'années, à compter qu'un PC de type Pentium 4 ne fait que ça pendant tout ce temps), donc ce serait bien domage d'obtenir des doublons ou des séquences qui se répètent !
 
Ceci dit, ça ne résoud pas le problème du genre "bon, j'ai mon select qui me retourne 25000 lignes, et je veux en prendre un au pif".
C'est là qu'intervient cet article trouvé chez Microsoft (et qui a motivé ce topic) :
http://msdn.microsoft.com/library/ [...] sp04c1.asp
 
Si vous y comprenez quelque chose, tant mieu pour vous, moi je panne rien :D
Donc, de mon côté, je reprends ma bonne technique utilisé par mon générateur de mot de passe :

Code :
  1. create procedure doRandomNumber
  2. (
  3. @random float output
  4. )
  5. as
  6. declare @tmp numeric(38)
  7. begin
  8. declare @guid char(32)
  9. set @guid = Replace(newid(), '-', '')
  10. set @tmp = cast(dbo.hexToDec(substring(@guid, 1, 1)) as numeric) +
  11.       cast(dbo.hexToDec(substring(@guid, 2, 1)) as numeric) * cast(16 as numeric) +
  12.       cast(dbo.hexToDec(substring(@guid, 3, 1)) as numeric) * cast(16 as numeric) * cast(16 as numeric) +
  13.       cast(dbo.hexToDec(substring(@guid, 4, 1)) as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) +
  14.       cast(dbo.hexToDec(substring(@guid, 5, 1)) as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) +
  15.       cast(dbo.hexToDec(substring(@guid, 6, 1)) as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) +
  16.       cast(dbo.hexToDec(substring(@guid, 7, 1)) as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) +
  17.       cast(dbo.hexToDec(substring(@guid, 8, 1)) as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) +
  18.       cast(dbo.hexToDec(substring(@guid, 9, 1)) as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) +
  19.       cast(dbo.hexToDec(substring(@guid, 10, 1)) as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) +
  20.       cast(dbo.hexToDec(substring(@guid, 11, 1)) as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) +
  21.       cast(dbo.hexToDec(substring(@guid, 12, 1)) as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) +
  22.       cast(dbo.hexToDec(substring(@guid, 13, 1)) as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) +
  23.       cast(dbo.hexToDec(substring(@guid, 14, 1)) as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) +
  24.       cast(dbo.hexToDec(substring(@guid, 15, 1)) as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) +
  25.       cast(dbo.hexToDec(substring(@guid, 16, 1)) as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) +
  26.       cast(dbo.hexToDec(substring(@guid, 17, 1)) as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) +
  27.       cast(dbo.hexToDec(substring(@guid, 18, 1)) as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) +
  28.       cast(dbo.hexToDec(substring(@guid, 19, 1)) as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) +
  29.       cast(dbo.hexToDec(substring(@guid, 20, 1)) as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) +
  30.       cast(dbo.hexToDec(substring(@guid, 21, 1)) as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) +
  31.       cast(dbo.hexToDec(substring(@guid, 22, 1)) as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) +
  32.       cast(dbo.hexToDec(substring(@guid, 23, 1)) as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) +
  33.       cast(dbo.hexToDec(substring(@guid, 24, 1)) as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) +
  34.       cast(dbo.hexToDec(substring(@guid, 25, 1)) as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) +
  35.       cast(dbo.hexToDec(substring(@guid, 26, 1)) as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) +
  36.       cast(dbo.hexToDec(substring(@guid, 27, 1)) as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) +
  37.       cast(dbo.hexToDec(substring(@guid, 28, 1)) as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) +
  38.       cast(dbo.hexToDec(substring(@guid, 29, 1)) as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) +
  39.       cast(dbo.hexToDec(substring(@guid, 30, 1)) as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) +
  40.       cast(dbo.hexToDec(substring(@guid, 31, 1)) as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) +
  41.       cast(dbo.hexToDec(substring(@guid, 32, 1)) as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(16 as numeric) * cast(4 as numeric)
  42. set @random = cast(@tmp as float) / 99999999999999999999999999999999999999
  43. end
  44. go


Ouais, je sais, j'aurais pu faire les calculs au lieu de mettre des * 16 partout...
Et... Ne me demandez pas pourquoi le "99999999999999999999999999999999999999", je ne sais pas pourquoi :p
J'ai essayé "(16^31 * 5) - 1" comme la fonction est censée faire, mais ça ne marche pas, j'ai des valeurs > 1 (problèmes d'arrondis certainement, le FLOAT perdant énormément de précision)
 
 
Execution :

Code :
  1. declare @val float
  2. exec doRandomNumber @val output
  3. select @val
  4. exec doRandomNumber @val output
  5. select @val
  6. exec doRandomNumber @val output
  7. select @val
  8. exec doRandomNumber @val output
  9. select @val
  10. exec doRandomNumber @val output
  11. select @val


 
Résultats :

Code :
  1. 0.79073888734954445
  2. 0.39270339086289996
  3. 0.63849267521354258
  4. 0.50870770027071233
  5. 0.33877549375235916


 
Voilà, j'espère que ça vous sera utile :)
Et tout cas, moi j'en avait besoin, donc j'ai pas perdu mon temps en passant 3 heures à écrire cet article, que j'espère vous le trouverez intéressant ^^
 
Edit:
La procédure stockée que j'ai indiqué, parceque c'est une procédure stockée et non une fonction ne resoud toujours pas la question "bon, j'ai mon select qui me retourne 25000 lignes, et je veux en prendre un au pif"... puisqu'on ne peut pas la faire intervenir dans la requête !
 
On pourra toujours écrire un curseur qui parcoure le résultat de la requête, compte le nombre de lignes, puis prend la (count * random)ième ligne. Vu le bordel que ça a été pour récupérer le nombre aléatoire, on n'est plus à ça prêt :whistle:


Message édité par Arjuna le 10-06-2005 à 23:21:09
mood
Publicité
Posté le 10-06-2005 à 22:58:14  profilanswer
 

n°1192201
Bubs
Posté le 05-09-2005 à 13:00:39  profilanswer
 

merci !!! c super


---------------
Shakira (Youpi) - l'autre forum ou je participe
n°1192209
Bubs
Posté le 05-09-2005 à 13:14:46  profilanswer
 

ah oui, en fait tu t'es pas mal pris la tete,
 
tu peux aussi faire ça :
 
select rand(checksum(NEWID()))


---------------
Shakira (Youpi) - l'autre forum ou je participe
n°1193318
Arjuna
Aircraft Ident.: F-MBSD
Posté le 06-09-2005 à 16:25:07  profilanswer
 

en fait, le souci, c'est que tout comme le truc que j'avais fait, on n'a pas le droit de l'utiliser dans une fonction.
 
sinon, un bête newid() suffit même ;)

n°1193319
Arjuna
Aircraft Ident.: F-MBSD
Posté le 06-09-2005 à 16:25:30  profilanswer
 

mais ceci dit, je suis arrivé au même résultat, donc c'est pas mieu :D


Aller à :
Ajouter une réponse
  FORUM HardWare.fr
  Programmation
  SQL/NoSQL

  [INFO] Limitations de la fonction RAND() de SQL Server

 

Sujets relatifs
Fonction Validation Email qui ne fonctionne pas alors qu'elle devraity'a t il une fonction C qui remplace la fonction c++ string.replace?
Probleme avec fonction include[SQL Server] Utilisation de xp_sendmail
ACCESS / SQLexport sql server 7
appeler une fonction javascript dans du phpaller chercher une fonction dans une autre feuille excel
execution de code par la fonction EVAL 
Plus de sujets relatifs à : [INFO] Limitations de la fonction RAND() de SQL Server

Forum MesDiscussions.Net, Version 2010.2
(c) 2000-2011 Doctissimo
Page générée en 0.116 secondes

Copyright © 1997-2022 Hardware.fr SARL (Signaler un contenu illicite / Données personnelles) / Groupe LDLC / Shop HFR