TSQL RAND random values

Question

Hi, I need populate with dummy values a table.

I need create a random generated value "RANDOM_VALUE" for every single row created.

Random value must be a string and its range could be only 'A' or 'B'.

The script should be able to list a series of row with value A or B randomly generated

Here an example of how should work:

ROW   RANDOM_VALUE
1     A
2     B
3     B
4     A
...   ...

Any ideas how to do it? Please help I am very new in SQL thanks!

Answer 1

Rand() is only evaluated once per column so will be the same for all rows to get around this you can use NewId() as below.

SELECT CHAR(65+ABS(CHECKSUM(NEWID()))%2), RestOfCols
FROM YourTable

If you need the output exactly as per your question just join onto a big enough table. e.g.

WITH cte AS
(
SELECT 
      ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS ROW,
      CHAR(65+ABS(CHECKSUM(NEWID()))%2) AS RANDOM_VALUE
FROM sys.objects
)
INSERT INTO DummyTable
SELECT ROW,RANDOM_VALUE 
FROM cte 
WHERE ROW<= 4

Answer 2

Create your own Random number function like this, which you can use in place of Rand().

CREATE VIEW dbo.RandomNumberView
AS
SELECT Rand() AS RandomNumber

GO

CREATE FUNCTION dbo.RandomNumber()
RETURNS float
AS
BEGIN
    RETURN (SELECT RandomNumber FROM dbo.RandomNumberView)
END
GO

Answer 3

for generating test data, you might find it valuable to have deterministic data. In other words, every time you generate data, it's the same. That way it's easier to reproduce bugs.

For this, you can use hashbytes() over a deterministic seed. In other words:

create function dbo.fn_RandishInt(@seed nvarchar(max), @min int, @max int)
returns int
as
begin
    declare @crc bigint
    declare @p float

    set @crc = cast(cast(hashbytes('md5', @seed) as int) as bigint) & 0xffffffff
    set @p = cast(@crc as float) / 4294967296

    return round(((@max - @min) * @p) + @min, 0)
end
go


SELECT
      ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS s,
      char(dbo.fn_RandishInt(ROW_NUMBER() OVER (ORDER BY (SELECT 0)), 65, 66)) AS t
FROM
    sys.objects

this will always yield the same, random-looking, results.