Insert random numbers into a column

Question

Insert random numbers into a column

henryvuong1000

SSC Eights!

Points: 821
More actions
March 20, 2011 at 1:20 am

#147612

I have a table, one of the columns is empty. I want to insert a random number into each row of the empty column, under the condition that no two rows have the same number. Can someone show me how? Thanks.

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

Ron McCullough SSC Guru Points: 63877 More actions · Answer 1

In order for someone to assist you will you please supply the table definition and indexes and a short sample of the data contained in the other columns. To learn how to provide this information quickly and easily please click on the first link in my signature block.

For the column which is to contain the random values be sure to specify the data type, i.e., INT, BIGINT, DECIMAL, FLOAT .. etc.

Other than that have you examined the RAND function available in SQL ?

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 2

This seems exactly like why Identity() was built. It's random in that it has nothing to do with the rest of the data, and is unique per table.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 3

henryvuong1000 (3/20/2011)
I have a table, one of the columns is empty. I want to insert a random number into each row of the empty column, under the condition that no two rows have the same number. Can someone show me how? Thanks.

First, since you're mostly a newbie on this forum, please study and practice the first link in my signature below to get coded answers much more quickly.

Shifting gears, since you didn't provide any data, column names, nor even a table name, I made my own to suit the purposes of this demo. 🙂 Please read the comments so you know what is going on here. 😉

/****************************************************************************************

Purpose:

Create a voluminous test table with various types of highly randomized data.

--Jeff Moden

****************************************************************************************/

--===== Conditionally drop the test table to make reruns easier

IF OBJECT_ID('dbo.JBMTest','U') IS NOT NULL

DROP TABLE dbo.JBMTest

;

--===== Create and populate a 1,000,000 row test table.

-- "SomeInt" has a range of 1 to 50,000 non-unique numbers

-- "SomeLetters2" has a range of "AA" to "ZZ" non-unique 2 character strings

-- "SomeMoney has a range of 0.0000 to 99.9999 non-unique numbers

-- "SomeDate" has a range of >=01/01/2000 and <01/01/2010 non-unique date/times

-- "SomeHex12" contains 12 random hex characters (ie, 0-9,A-F)

-- "SomeNRRand" will become a Non-Repeating Random number

-- This will take something less than 11 seconds

SELECT TOP 1000000

SomeInt = ABS(CHECKSUM(NEWID()))%50000+1,

SomeLetters2 = CHAR(ABS(CHECKSUM(NEWID()))%26+65)

+ CHAR(ABS(CHECKSUM(NEWID()))%26+65),

SomeMoney = CAST(ABS(CHECKSUM(NEWID()))%10000 /100.0 AS MONEY),

SomeDate = CAST(RAND(CHECKSUM(NEWID()))*3653.0+36524.0 AS DATETIME),

SomeHex12 = RIGHT(NEWID(),12),

SomeNRRand = CAST(NULL AS INT)

INTO dbo.JBMTest

FROM sys.all_columns ac1

CROSS JOIN sys.all_columns ac2

;

As you can see, the SomeNRRand column has nothing but NULLs in it.

Enter the nearly magical power of CTE's; This UPDATE uses a CTE to create a randomly ordered Row Number and associate it with each row in the table without any kind of join. Like I said, it's magic. The UPDATE statement simply applies the calculated column called MyRand and puts it into SomeNRRand in the table itself. The reference to NEWID() is what makes it "random". It's the only multi-row random number in all of SQL Server.

WITH -- This will take something less than 22 seconds because it has to sort

cteRand AS

(

SELECT MyRand = ROW_NUMBER() OVER (ORDER BY NEWID()),

SomeNRRand

FROM dbo.JBMTest

)

UPDATE cteRand

SET SomeNrRand = MyRand

;

Of course, we should verify that it actually worked...

--===== This just double checks to make sure that no two

-- random numbers are alike.

SELECT SomeNRRand, COUNT(*)

FROM dbo.JBMTEST

GROUP BY SomeNRRand

HAVING COUNT(*) > 1

;

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

Ron McCullough SSC Guru Points: 63877 More actions · Answer 4

Craig Farrell (3/20/2011)
This seems exactly like why Identity() was built. It's random in that it has nothing to do with the rest of the data, and is unique per table.

But usually consecutive in sequence (that is when rows have not been deleted from the table) and hence I would not consider that as truly random .. but then again who knows what the OP really wants/needs unless he/she is more specific.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 5

bitbucket-25253 (3/20/2011)
Craig Farrell (3/20/2011)
This seems exactly like why Identity() was built. It's random in that it has nothing to do with the rest of the data, and is unique per table.
But usually consecutive in sequence (that is when rows have not been deleted from the table) and hence I would not consider that as truly random .. but then again who knows what the OP really wants/needs unless he/she is more specific.

True. It was the 'unique' bit that caught my eye on that one. I suppose you could ABS() a NewID cast to bigint and run that multiple times for null entries against a unique index on the column, but that seemed kinda overkill to me. Jeff's solution above randomizes who gets the ID field, and is neat, but it's still just an identity() column in an odd order.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 6

bitbucket-25253 (3/20/2011)
Craig Farrell (3/20/2011)
This seems exactly like why Identity() was built. It's random in that it has nothing to do with the rest of the data, and is unique per table.
But usually consecutive in sequence (that is when rows have not been deleted from the table) and hence I would not consider that as truly random .. but then again who knows what the OP really wants/needs unless he/she is more specific.

Hi Ron,

If you look at what I've done, it's no different than what Craig suggests. The numeric assignment made be an IDENTITY in sequential order isn't really any different that the assignment of numbers in a random order and sequences are the easiest way to guarantee no duplication of the "random" number.

The proof of the pudding would be to sort a table with an IDENTITY column by any other column and the IDENTITY will, indeed, appear to be random unless the sort is done on a date column which indicates the order of entry.

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

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 7

Craig Farrell (3/20/2011)
but it's still just an identity() column in an odd order.

I absolutely agree except for one thing... my solution isn't self-maintaining. An IDENTITY column is and, therefor, is also the better solution in this case.

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

Rao.V Hall of Fame Points: 3452 More actions · Answer 8

you also can use option called "SEQUENCE" for that column so that it generates number automatically when u insert a new row

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

Rao.V (3/21/2011)
you also can use option called "SEQUENCE" for that column so that it generates number automatically when u insert a new row

Do you mean Identity (which has already been suggested) or Sequence which is a new feature in Denali (the next version of SQL Server)?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Rao.V Hall of Fame Points: 3452 More actions · Answer 10

Rao.V

Hall of Fame

Points: 3452

March 21, 2011 at 7:27 am

#1301294

yes i mean identity

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 11

Rao.V (3/21/2011)
you also can use option called "SEQUENCE" for that column so that it generates number automatically when u insert a new row

Rao.V (3/21/2011)
yes i mean identity

???:blink: 😛

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 12

Jeff Moden (3/21/2011)
Rao.V (3/21/2011)
you also can use option called "SEQUENCE" for that column so that it generates number automatically when u insert a new row
Rao.V (3/21/2011)
yes i mean identity
???:blink: 😛

A rose by any other name.....

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 13

Heh... but Tiger Lilly just isn't a Rose. 😛

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