CODEWITHSUNDEEP
sqlsql-serversql-server-2008-r2
E mad
E mad

Reputation: 313

How to generate random boolean value in sql server 2008?

Im writing a database for some kind of university and there is a table named

Contact_Assign Its parameters are:

Is_Instructor       UD_BOOLEAN NOT NULL,
Is_TeacherAssistant UD_BOOLEAN NOT NULL,
Is_Student          UD_BOOLEAN NOT NULL,
Registration_ID     UD_ID      NOT NULL,
Contact_ID          UD_ID      NOT NULL,

now I want to insert dummy data in this table but I have no idea how can I do this for the boolean parameters.

PS. UD_BOOLEAN is 

CREATE TYPE UD_BOOLEAN FROM BIT

any idea how?

Upvotes: 18

Views: 11789

Answers (4)

Ryan
Ryan

Reputation: 3215

Another solution that makes use of NEWID():

select ABS(CHECKSUM(NEWID())) % 2

Upvotes: 3

Jamiec
Jamiec

Reputation: 136114

If you are only generating one row, you could use something as simple as:

SELECT CAST(ROUND(RAND(),0) AS BIT)

However, if you are generating more than one row, RAND() will evaluate to the same value for every row, so please see Martin Smith's answer.

Upvotes: 11

Gordon Linoff
Gordon Linoff

Reputation: 1269953

If you want a different value for the second column, you can use newid(). Here is an example:

select cast((case when left(newid(), 1) between '0' and '7' then 1 else 0 end) as bit)

You would only need newid() if you were inserting more than one row in a single statement.

Upvotes: 5

Martin Smith
Martin Smith

Reputation: 453327

You can use

CRYPT_GEN_RANDOM(1) % 2

The advantages over RAND are that it is stronger cryptographically (you may not care) and that if inserting multiple rows it is re-evaluated for each row.

DECLARE @T TABLE(
  B1 BIT,
  B2 BIT);

INSERT INTO @T
SELECT TOP 10 CRYPT_GEN_RANDOM(1)%2,
              CAST(ROUND(RAND(), 0) AS BIT)
FROM   master..spt_values

SELECT *
FROM   @T

would give the same value in all rows for the second column

Upvotes: 32

Related Questions