Re: field which generates a random number?

Post removed (X-No-Archive: yes)

Re: field which generates a random number?

> If that is the default value, it will grab a random number within that
> range and assign a different number to each row, correct? Thanks.

Yes (1), note though that the RAND function will resolve to the same value for all rows returned by
one query:

USE tempdb
CREATE TABLE t(c1 int, c2 int DEFAULT (RAND()*900) + 100)
GO
INSERT INTO t (c1) VALUES(1)
INSERT INTO t (c1) VALUES(1)
SELECT * FROM t
--Different values
GO
TRUNCATE TABLE t
INSERT INTO t (c1)
SELECT TOP 10 1 AS c1 FROM sysobjects
SELECT * FROM t
--Same values for all rows

(1)
As noted by Greg, the values are "pseudo-random". To be honest, I don't know mathematics well enough
to distinguish pseudo-random from truly random. I believe I read somewhere that a computer cannot
generate true random values with less that some hardware to detect decay of some radioactive
isotope, or something to that effect. But I'll let the mathematicians contemplate over that.

One thing you can do to determine if it is "random enough" is to do something like:

SET NOCOUNT ON
USE tempdb
IF OBJECT_ID('t') IS NOT NULL DROP TABLE t
CREATE TABLE t(c1 int identity, c2 int DEFAULT (RAND()*900) + 100)
GO
INSERT INTO t DEFAULT VALUES
GO 100000

SELECT COUNT(*) AS #occurences, c2
FROM t
GROUP BY c2
ORDER BY #occurences


--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi


"THO" wrote in message news:tho-72D848.11050703092007@news.giganews.com...
> In article <#3CNDfj7HHA.5012@TK2MSFTNGP02.phx.gbl>,
> "Tibor Karaszi"
> wrote:
>
>> You can have the following expression as a default value for the column:
>>
>> (RAND()*900) + 100
>
> If that is the default value, it will grab a random number within that
> range and assign a different number to each row, correct? Thanks.
>
>
>
>> Note, however, that if you have an INSERT with a subquery, the value will be
>> the same for all those
>> rows.