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