March 20, 2011 at 1:20 am
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.
March 20, 2011 at 9:53 am
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 ?
March 20, 2011 at 1:00 pm
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.
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
March 20, 2011 at 1:58 pm
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
Change is inevitable... Change for the better is not.
March 20, 2011 at 2:00 pm
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.
March 20, 2011 at 2:10 pm
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.
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
March 20, 2011 at 2:11 pm
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
Change is inevitable... Change for the better is not.
March 20, 2011 at 2:14 pm
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
Change is inevitable... Change for the better is not.
March 21, 2011 at 6:10 am
you also can use option called "SEQUENCE" for that column so that it generates number automatically when u insert a new row
March 21, 2011 at 6:20 am
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
March 21, 2011 at 7:27 am
yes i mean identity
March 21, 2011 at 11:45 am
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
Change is inevitable... Change for the better is not.
March 21, 2011 at 11:53 am
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 rowRao.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
March 21, 2011 at 4:22 pm
Heh... but Tiger Lilly just isn't a Rose. 😛
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply