Help with Rand() Function (1 Viewer)

[WayneRyan]

I need help with the Rand() function in TSQL.

I needed to return a random 20% of the rows from a certain table.
On the surface it seemed that I could use the identity column for the seed
and check for Rand() between 0.1 and 0.2.

However, Rand(id) returns a very SEQUENTIALLY NUMBERED set of rows. This
results in selecting data from a few horizontal "bands" in the table.
The data was anything but random.

In the Help files, they show using GetDate() as a seed, but admit that
in a query all of the Rand() return values are the same!

I ended up using > --> SubString(Cast(Rand(id) As Varchar(20)), 7, 1) In ('1', '2')
The 7th character seemed to be "somewhat" random.

This did end up generating a very nice looking sample. Not perfect in the
ideal sense, but adaquate for this task.

This is very easily handled in Access, using a Public Function call in
a query that does the Randomize, etc.

Is there a "real" solution in SQL Server?

Thanks,
Wayne

 

[WayneRyan]

The solution is to use the Newid() function:

ABS(CAST(CAST(newid() AS VARBINARY) AS INT))

It generates random #s based on the MAC address of the network card and the
system clock.

Wayne