What am I doing wrong when using RAND () in MS SQL Server 2005?

Question

I am trying to select a random 10% sample from a small table. I thought that I just use the RAND () function and select those lines where the random number is less than 0.10:

SELECT * FROM SomeTable
WHERE SomeColumn='SomeCondition' AND
      RAND() < 0.10

But I soon discovered that RAND () always returns the same number! Reminds me of this xkcd cartoon .

OK, no problem, the RAND function takes on its initial value. I will run this query periodically, and I want it to give different results if I run it on another day, so I sow it with a combination of date and unique string identifier:

SELECT * FROM SomeTable
WHERE SomeColumn='SomeCondition' AND
      RAND(CAST(GETDATE) AS INTEGER) + RowID) < 0.10

! , RAND, , . , RAND . , !

, :

SQL Server
SQL?

. TABLESAMPLE , , , , WHERE. TOP NEWID , , .

- ?

: AlexCuse , . , , RAND ?

Answer 1

( ΤΖΩΤΖΙΟΥ) 10% . , Rand() <.10, .

-

select top 10 percent * from MyTable order by NEWID()

.

edit: RAND . , ( kludge - , Rand() UDF)

CREATE VIEW RandView AS 

SELECT RAND() AS Val

GO

CREATE FUNCTION RandomFloat()
RETURNS FLOAT
AS
BEGIN

RETURN (SELECT Val FROM RandView)

END

select blah, dbo.RandomFloat() from table .

Answer 2

(, rowid), , , SQL-, :

SELECT * FROM SomeTable WHERE SomeColumn='SomeCondition' AND 0*rowid+RAND() < 0.10

RAND() , .

. , , , .

Answer 3

:

select * from SomeTable
where rand(0*SomeTableID + cast(cast(newid() as binary(4)) as int)) <= 0.10

Answer 4

?

SQL Server 2005?

UDF, Rand(), .

Answer 5

SELECT TOP 10 PERCENT * FROM schema.MyTable ORDER BY NEWID()