Sunday, October 27, 2013

Randomizing rows - MSSQL server



Today we will explore ,how we can get ramdomized the rowsfrom SQL server
 Common approaches are
         1)        Using Sql Server Query
         2)        Using Programming Language ( C#|VB.net) to randomize row after retrieval
        Either in Sql server or in programming language we can generate a new column which will hold a randomly generated number for each row then we can sort the record set based on this column.

We First Explore the Pure SQL Server Way:
Using Rand Function?
In MSSQL server there is a function called RAND()
SELECT RAND()
This will generate a random number between 0 & 1.Or
SELECT RAND(10)
Here Rand function takes 10 as seed and generate random number less than 10 ,Can It Be Useful to randomize rows.Try Running Command
select RAND(10) as MyRandNum,* from sys.sysobjects

when we execute this command we get one more column in recordset called MyRandNum yet all the values in it are same irrespective of multiple rows
Now lets try without passing seed to Rand function as
select RAND(),* from sys.sysobjects
Here too same behavior.
Using NewId() Function ?
There is new kid on the block called NEWID(),this function generates some sort 
of long random char string,Lets try to see output of below query  
select NEWID(),*from sys.sysobjects  
   Here we observe that the a random string is generated for each row means its value for each row is different.
 Now let us see how much this function can stand, lets add one more random column
select NEWID(),NEWID(),* from sys.sysobjects
here when we run it found out that value of two random columns in each row ,do have 
different values from one another .Hence this function suits our requirement. 
Now can we convert this random character string to a random number With little googling 
and testing provided stuff on my MSSQL I found a trick that 
convert this random char string to a random number,it cast the random string to binary & 
then to number
 select ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT)) AS [RandomNumber],* from sys.sysobjects
 Now we got what we need Here is final query 
select ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT)) AS [RandomNumber],* from sys.sysobjects
Order by   ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT)) 
 Or if we don’t inclined to use this random number further
 select * from sys.sysobjects Order by ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT))  
 Now we got what we need Here is final query 
 select ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT)) AS [RandomNumber],* from sys.sysobjects
 Order by    ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT)) 
 Output Of above query:   
Reference :
http://www.sql-server-helper.com/tips/generate-random-numbers.aspx
 
 


No comments:

Post a Comment