TSQL RAND random values

Question

I need to populate with dummy table values.

I need to create an arbitrary generated value "RANDOM_VALUE" for each individual row.

The random value must be a string, and its range can only be "A" or "B".

the script should be able to list a series of lines with a value of A or B, created randomly

Here is an example of how to work:

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

Any ideas how to do this? Thank!

Answer 1

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

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

, . .

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

, , 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

to generate test data, you might think that it matters for deterministic data. In other words, every time you generate data, it is one and the same. Thus, it is easier to reproduce errors.

To do this, you can use hashbytes()over a determinate 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

it will always give the same, random results.