T-SQL Random Output of Two Values

November 7, 2022

I have a table of users that are owned by various Owners. One person left and the list of users needs to be split up between two owners. How would you use SQL to randomly assign the users between two ownerIds . For example, a function that theoretically behaves like below

UPDATE Users
set OwnerId = RAND(64,72)
Where OwnerId = 37

We are utilizing SQL Server 2012. Tried looking at a random function between two users but is unable to find anything specific. Thank you,

>Solution :

You can use CHECKSUM(NEWID()) which returns an integer that is either odd or even, so then just check mod 2 (% 2).

UPDATE dbo.Users
   SET OwnerId = CASE 
   WHEN CHECKSUM(NEWID()) % 2 = 0 THEN 64 ELSE 72 END
WHERE OwnerId = 37;

Working example in this fiddle.

If you want to be cryptic/clever or play code golf, then something like this can work in this very specific example:

UPDATE dbo.Users 
  SET OwnerId = 64 + (CHECKSUM(NEWID()) % 2 * 8)
WHERE OwnerId = 37;

The reason this works better than RAND() is because RAND() is only evaluated once, whereas NEWID() is evaluated for every row.

Following Larnu’s recommendation, though, you could also do this:

WITH cte AS
(
  SELECT ID, OwnerId, rn = ROW_NUMBER() OVER (ORDER BY ID)
  FROM dbo.Users
  WHERE OwnerId = 37
)
UPDATE cte SET OwnerId = 64 + (rn % 2 * 8);