10 Digit Numeric Unique Random Number Generation

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

10 Digit Numeric Unique Random Number Generation

Author

Topic

vijayakumar_svk
Yak Posting Veteran

50 Posts

Posted - 2011-06-09 : 13:20:13

I have a table with 200k records and I have the identity column. However I need to generate a 10 digit numeric number for all the rows. I need to do this in a update statement to update all the records at a time.

So I don't want to have UDF to generate the numbers (which I have already). Is there any possible way to do this in t-sql itself.

Performance and uniquness across the table is the key for me. So the update statement must run very quickly.

Sample Rows:

ID Number EntryText Hash
1 12 jsdlksfkdsfld 4324834234
2 13 sdsfmdlkfldffk 4673124743
.....
.....
175893 34 oiejrfiodsjfd 8597345277

Hash is the column which will be null initially and needs to be updated with the generated number.

thanks in advance
Vijay

==============================
Work smarter not harder take control of your life be a super achiever

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-06-09 : 13:36:01

if you just want it to be just unique, you can always do something like:

update hash = cast(id as bigint) + 1000000000

If you also want it to be random, I don't know of a way to avoid a per-row function evaluation (I assume you meant per-row function evaluation rather than specifically UDF). One way to do it, which does not UDF, but does use the SQL built in function would be


update has = cast(CAST(NEWID() AS VARBINARY(5)) AS BIGINT)%1000000000+1000000000

vijayakumar_svk
Yak Posting Veteran

50 Posts

Posted - 2011-06-10 : 01:50:18

Thanks sunit for the solution..

I want the number to be random.. I thought the second solution could work. But I am getting 1-2% duplicates when I try to update 150k records which I should not.

I tried changing varbinary(5) to varbinary(15) but still getting the duplicates.

==============================
Work smarter not harder take control of your life be a super achiever

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-06-10 : 07:56:45

The newid returns a 16-byte guid, so you might try making it 16, but I suspect the problem has to do with the modulo operator. Even so, I would not have expected that high a percentage of dups.

I did the following experiment which sort of confirms that theory. Do you always have to have a ten-digit number?


-- Inserting GUIDs generated by newid() into a temp table.

if OBJECT_ID('tempdb..#tmp') is not null DROP TABLE #tmp;
CREATE TABLE #tmp (id uniqueidentifier);

WITH N(n) AS
(
	SELECT 1
	UNION ALL
	SELECT n+1 FROM N WHERE n <= 100000
)
INSERT INTO #tmp SELECT newid() FROM N  
OPTION (MAXRECURSION 0);


-- check if there are any dups in the GUIDs.  (no dups in my tests)
SELECT id,COUNT(*) FROM #tmp GROUP BY Id HAVING COUNT(*) > 1 -- returns no dups

-- are there dups when cast to 10 digit int (4-10 dups in my test)
select 
	cast(CAST(id AS VARBINARY(5)) AS BIGINT)%1000000000+1000000000, COUNT(*) 
FROM 
	#tmp
group by 
	cast(CAST(id AS VARBINARY(5)) AS BIGINT)%1000000000+1000000000
HAVING COUNT(*) > 1

-- are there dups when cast to bigint ? (none in my tests).
select 
	cast(CAST(id AS VARBINARY(16)) AS BIGINT), COUNT(*) 
FROM 
	#tmp
group by 
	cast(CAST(id AS VARBINARY(16)) AS BIGINT)
HAVING COUNT(*) > 1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources