Getting Random Number for each row

Question

I have a table with some names in a row. For each row I want to generate a random name. I wrote the following query to:

BEGIN transaction t1

Create table TestingName
(NameID int,
 FirstName varchar(100),
 LastName varchar(100)
)

INSERT INTO TestingName
SELECT 0,'SpongeBob','SquarePants' 
UNION 
SELECT 1, 'Bugs', 'Bunny' 
UNION 
SELECT 2, 'Homer', 'Simpson' 
UNION 
SELECT 3, 'Mickey', 'Mouse' 
UNION 
SELECT 4, 'Fred', 'Flintstone'

SELECT FirstName from TestingName
WHERE NameID = ABS(CHECKSUM(NEWID())) % 5

ROLLBACK Transaction t1

The problem is the "ABS(CHECKSUM(NEWID())) % 5" portion of this query sometime returns more than 1 row and sometimes returns 0 rows. I must be missing something but I can't see it.

If I change the query to

DECLARE @n int
set @n= ABS(CHECKSUM(NEWID())) % 5

SELECT FirstName from TestingName
WHERE NameID = @n

Then everything works and I get a random number per row.

If you take the query above and paste it into SQL management studio and run the first query a bunch of times you will see what I am attempting to describe.

The final update query will look like

Update TableWithABunchOfNames
set [FName] = (SELECT FirstName from TestingName
WHERE NameID = ABS(CHECKSUM(NEWID())) % 5) 

This does not work because sometimes I get more than 1 row and sometimes I get no rows.

What am I missing?

Answer 1

The problem is that you are getting a different random value for each row. That is the problem. This query is probably doing a full table scan. The where clause is executed for each row -- and a different random number is generated.

So, you might get a sequence of random numbers where none of the ids match. Or a sequence where more than one matches. On average, you'll have one match, but you don't want "on average", you want a guarantee.

This is when you want rand(), which produces only one random number per query:

SELECT FirstName
from TestingName
WHERE NameID = floor(rand() * 5);

This should get you one value.