Table with unique Random Number column
-
I'm looking for an easy way to have a unique random number column on a table - to be generated when inserting multiple records, rather than by single inserts - e.g.
USE NORTHWIND
CREATE TABLE #TestRnd (
pKey INT IDENTITY(1,1),
MyData Varchar(50),
Random1 FLOAT default RAND(),
Ramdom2 UniqueIdentifier default NEWID() )
INSERT INTO #TestRnd
(MyData)
SELECT ContactName FROM Customers
I get the same random number for every row in column Random1. Random2 is unique - and looks random enough, but I want a numeric field, and pKey may be unique and numeric but its not random!
-
My first and only question is why do you want to do that for?
-
If the need is for uniqueness and not keys you may want to consider timestamp datatype
-
-
Hi,
are you using the Ramdom2 column for replication purposes, or do you just want a column with a random number in it.
You can convert NEWID() to a positive integer using the cumbersome conversion
ABS(CAST(CAST(NEWID() AS BINARY(16)) AS BIGINT))
But you will not be able to store an integer value in a UniqueIdentifier column, you would have to drop the UniqueIdentifier status or create a view based on your table which displayed the NEWID() value as a number.
David
If it ain't broke, don't fix it...
-
First Ninja's point: I'm porting a "shadow IT" application written in Access and VB6 - Its a production line sequencing program to keep a balanced workload on the assembly track. Quite often several orders get the same score (i.e take the same amout of work to build), and the random number is to distinguish between them.
Jason: currently the random number is used as a multiplier, so has to be numeric. I could change the logic, but the existing application gives the right results, so I'm reluctant.
And Adrian's point: The pkey is no good as items are added in batches meaning you might get all 'red' orders then all 'blue' orders if pkey is used. This is bad as if the 'red' components are running out, you want time to notice and resupply, so the mix has to be as random as possible. If 'red' runs out completely and a 'red' order appears, the assembly line stops causing major grief to all.
Thanks to David for the suggestion to use CAST.
Tom
-
"Jason: currently the random number is used as a multiplier, so has to be numeric. I could change the logic, but the existing application gives the right results, so I'm reluctant."
The timestamp datatype can be converted to numeric (i.e: Select cast([Timestamp column] as INT) from [Your Table])
However, David's suggestion applies to your situation much more effectively because the timestamp column value will change with each insert, update, while the newid value will remain constant once inserted.
When you use the UniqueIdentifier, to avoid needing to remove the UI status or create other views you can set the default value of your unique column (defined as BIGINT) to the cast provided by David. Here is a quick example.
CREATE Table tblTest (iId INT IDENTITY(1,1) PRIMARY KEY,vcValue VARCHAR(255),biUniuque BIGINT DEFAULT ABS(CAST(CAST(NEWID() AS BINARY(16)) AS BIGINT)))
-
-
If you want an integer, you can use this as the default for your column:
abs(convert(int,convert(varbinary(16),newid())))
-
Perhaps I'm doing something wrong, Joe... the T-SQL example you posted (repeated below), generates the sequence of numbers that follows that and repeats... not random at all...
CREATE TABLE Generator31 (keyval INTEGER NOT NULL);
INSERT INTO Generator31 VALUES (1); -- any start value
UPDATE Generator31
SET keyval =
keyval/2 + ((keyval % 2) + (keyval/8 % 2) % 2)* 8
SELECT * FROM Generator31;
8
12
14
15
23
19
17
16
8
12
14
15
23
19
17
16...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply