1

I've found a lot of answers where the call to RAND is constantly being reseeded, but this means that while different values are created for each row, the operation cannot be repeated with the same result. Is there any way to ensure that each row gets a new value but where it is still seeded by the initial call?

The below attempts don't work:

UPDATE #TestTable
SET
    Number = CONVERT(INT, FLOOR(RAND(CHECKSUM(NEWID())) * 1000))

Every row has a different value, but every time I run it the values for a given row are changed.

DECLARE @RandomSeeder FLOAT;

--Seeds the value, probably a better way to do this.
SELECT @RandomSeeder = RAND(5336); 

UPDATE #TestTable
SET
    Number = CONVERT(INT, FLOOR(RAND() * 1000));

It appears like RAND is called once and then that value is used in every row.

Lawtonfogle
  • 974
  • 4
  • 17
  • 32
  • 2
    Why do you need to repeat the operation with the same results? Kinda is an oxymoron. I would like the the same values and order from a random function... – xQbert Jul 21 '15 at 19:21
  • 2
    See [This answer](http://stackoverflow.com/a/30173756/119477) – Conrad Frix Jul 21 '15 at 19:23
  • @xQbert I want random data, but I want the ability to start over from the same place while operating on it so that if I find a bug I can repeatedly test it. Imagine trying to debug code that relies on calls to a random function. Without the ability to seed the random call debugging the function is far harder. – Lawtonfogle Jul 21 '15 at 19:52
  • @ConradFrix As far as I can tell that works. Is there any explanation as to why/how it works and why it needs a view and a function to work? – Lawtonfogle Jul 21 '15 at 20:15
  • So the function is there to make it a RBAR process. The view is there because functions like RAND() aren't allowed to directly be in a scalar function because they're supposed to be deterministic. – Conrad Frix Jul 21 '15 at 20:44
  • @ConradFrix Further testing shows it is working. Do you mind making this an answer? – Lawtonfogle Jul 22 '15 at 13:30
  • I would consider creating a persisted table of random numbers. Make it big enough for your purposes. Populate it once and then use it when needed. – Vladimir Baranov Jul 24 '15 at 00:36

2 Answers2

0

To put a guid as a column value you should declare it as for example:

[Number] UNIQUEIDENTIFIER CONSTRAINT [DF_Email_EmailID] DEFAULT (newsequentialid()) NOT NULL

you could then use that value to generate a random number.

montelof
  • 491
  • 1
  • 6
  • 13
  • There is no way of reseeding this to repeat results. Nor is it random. Being sequential. – Martin Smith Jul 21 '15 at 19:49
  • he said he needs random data, with the ability to redo the process. instead of a temp table, he can store in a persistent table this values, use the value to call a function based on a random number generated by this GUID it could be a hash, or checksum, based on the GUID, then if he wants to redo the process he could know the value by applying the same formula again vs this GUID row. – montelof Jul 21 '15 at 20:45
0

In short, RAND() doesn't step by default. Per Conrad Frix in the comments, it is designed to be deterministic, so a view and a function to call it must be created to pass through the RAND() calls.

The setup for creating the view and function are as follows:

IF OBJECT_ID('RANDView') IS NOT NULL
BEGIN DROP VIEW RANDView END
GO

CREATE VIEW RANDView
AS SELECT RAND() AS RANDNumber
GO

IF OBJECT_ID('SeededRAND') IS NOT NULL
BEGIN DROP FUNCTION SeededRAND END
GO

CREATE FUNCTION SeededRAND() RETURNS FLOAT
AS BEGIN
    DECLARE @Return FLOAT
    SELECT @Return = RANDNumber FROM RANDView
    RETURN @Return
END
GO

A demo that uses this view and function can be written as follows:

CREATE TABLE #TestTable (
TestNum INT);

INSERT INTO #TestTable
VALUES (1),(2),(3),(4);

SELECT * FROM #TestTable;

SELECT RAND(5336); -- Seed the RAND() function

UPDATE #TestTable
SET TestNum = CONVERT(INT, FLOOR(dbo.SeededRAND() * 1000));

SELECT * FROM #TestTable;

You can play with the demo in SQL Fiddle here.

SQL Tactics
  • 296
  • 4
  • 15