Code generation

Question

Post reply

Code generation

scziege

SSC Veteran

Points: 247
More actions
May 11, 2010 at 12:58 am

#222368

Hi to all,
I have the following request for a customer.
We need a code generation tsql script which is used to generate a random code.
The code should contain numeric and alphanumeric codes with a lenght of 8.
The tsql script should generate 1.6 Million random codes with no duplicates and
and with a very different appearance so that you should not guess the code
very easy.
Does someone have a sample script for that problem.
Thanks in advance
Thorsten

Viewing 15 posts - 1 through 15 (of 36 total)

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

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 1

No random generator can guarantee truly unique , you will have to check for this you self.

Try this link ...

http://sqlblogcasts.com/blogs/sqlandthelike/archive/2009/08/27/a-faster-tsql-random-length-random-string-generator.aspx

Clear Sky SQL
My Blog[/url]

Atif-ullah Sheikh SSChampion Points: 12495 More actions · Answer 2

Well I dont know if was a coincidence.

I generated 1000000 rows in a tally table;

Select Top 1000000 Identity(int,1,1) N

into tblTally

from sys.columns a, sys.columns b,sys.columns c

Then, I inserted 1000000 in new Table tblB with NewID

CREATE TABLE [dbo].[tblB](

[NID] [uniqueidentifier] NULL,

[N] [int] IDENTITY(1,1) NOT NULL

) ON [PRIMARY]

Insert into tblB (NID)

Select NEWID() as NID

from tblTally

Then I inserted another 1000000 rows in tblB

Insert into tblB (NID)

Select NEWID() as NID

from tblTally

Then I queried the following SQL;

Select Distinct Left(NID,8) from tblB

And it gave me 1999502 Distinct rows out of 2000000

Meaning, only 498 rows were duplicate for Left(NID,8)

:w00t:

----------------------------------------------------------------------------------------------------------------------------------------------------------------------

Sometimes, winning is not an issue but trying.
You can check my BLOG [font="Arial Black"]here[/font][/url][/right]

spaghettidba SSC Guru Points: 105732 More actions · Answer 3

This code gave out only 253 duplicates on 16 Mln records.

I don't know if this is exactly what you're after, but you could give it a go.

SELECT TOP 1600000 RIGHT(master.dbo.fn_varbintohexstr(hashbytes('MD5',CAST(NEWID() AS VARCHAR(36)))),8) AS code

FROM tally AS a

CROSS JOIN tally AS b

-- Gianluca Sartori

steveb. SSC-Forever Points: 46733 More actions · Answer 4

does it have to be in T-SQL ?

Random numbers are difficult for a computer the only way to get close to true random numbers is to mointor a random real-life event or to use a certified service such as

http://www.random.org"> http://www.random.org

it is fairly easy to write a client app to call this service and populate the database. You may have to pay for 1.6 million rows though, but if this project is linked to gambiling or competitions then it may be required by law to use a certified service.

Atif-ullah Sheikh SSChampion Points: 12495 More actions · Answer 5

This code gave out only 253 duplicates on 16 Mln records.
I don't know if this is exactly what you're after, but you could give it a go.
SELECT TOP 1600000 RIGHT(master.dbo.fn_varbintohexstr(hashbytes('MD5',CAST(NEWID() AS VARCHAR(36)))),8) AS code
FROM tally AS a
CROSS JOIN tally AS b

So, it means that using New_ID() will give a better solution.

----------------------------------------------------------------------------------------------------------------------------------------------------------------------

Sometimes, winning is not an issue but trying.
You can check my BLOG [font="Arial Black"]here[/font][/url][/right]

spaghettidba SSC Guru Points: 105732 More actions · Answer 6

I don't think I understand what you mean with "better solution".

Anyway, duplicates cannot be ignored, as Dave already pointed out.

I don't think that "random" and "unique" can live inside the same algorithm.

-- Gianluca Sartori

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

Atif Sheikh (5/11/2010)
This code gave out only 253 duplicates on 16 Mln records.
I don't know if this is exactly what you're after, but you could give it a go.
SELECT TOP 1600000 RIGHT(master.dbo.fn_varbintohexstr(hashbytes('MD5',CAST(NEWID() AS VARCHAR(36)))),8) AS code
FROM tally AS a
CROSS JOIN tally AS b
So, it means that using New_ID() will give a better solution.

Yes... but not by itself and not without a dupe check. The requirement is for 8 characters.

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

1.6 million random 8 character codes that no one can guess and they're guaranteed to be unique within the set... takes about 25 seconds on my 8 year old desktop...

WITH

cteFirstGen AS

( --=== Gen enough 8 character random codes to cover possible dupes for 1.6 million codes

SELECT TOP 2000000 LEFT(NEWID(),8) AS RandomCode

FROM Master.sys.All_Columns ac1

CROSS JOIN Master.sys.All_Columns ac2

)

,

cteNumberDupes AS

( --=== Number the codes so we can select unique random codes

SELECT ROW_NUMBER() OVER (PARTITION BY RandomCode ORDER BY (SELECT NULL)) AS Occurance,

RandomCode

FROM cteFirstGen

WHERE RandomCode > '10000000'

) --=== Select 1.6 million unique random codes only

SELECT TOP 1600000 RandomCode

INTO #MyHead

FROM cteNumberDupes

WHERE Occurance = 1

SELECT * FROM #MyHead

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

spaghettidba SSC Guru Points: 105732 More actions · Answer 9

spaghettidba

SSC Guru

Points: 105732

May 11, 2010 at 7:51 am

#1165546

Great code, Jeff!!

-- Gianluca Sartori

Lowell SSC Guru Points: 323518 More actions · Answer 10

Jeff Moden (5/11/2010)
1.6 million random 8 character codes that no one can guess and they're guaranteed to be unique within the set... takes about 25 seconds on my 8 year old desktop...

you must have a monster "old" desktop; I've got a decent 2.8Gig developer machine and it took 5 times that long; 01:29 to run your code,01:28 on second run.

25 seconds....your old developer machine plugged into a SAN? or did you use DBCC TIMEWARP with this?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Cory E. SSCertifiable Points: 7076 More actions · Answer 11

Lowell (5/11/2010)
you must have a monster "old" desktop; I've got a decent 2.8Gig developer machine and it took 5 times that long; 01:29 to run your code,01:28 on second run.
25 seconds....your old developer machine plugged into a SAN? or did you use DBCC TIMEWARP with this?

I was curious, so, I ran it as well, on my desktop it was 41 and 37 seconds. nothing fancy at all with the desktop or setup, and it is pretty old.

-- Cory

spaghettidba SSC Guru Points: 105732 More actions · Answer 12

I get:

19 seconds for the first run

16 seconds for the second run

Don't run the select * from #myhead statement, as it involves presenting results in SSMS.

-- Gianluca Sartori

Cory E. SSCertifiable Points: 7076 More actions · Answer 13

Gianluca Sartori (5/11/2010)
Don't run the select * from #myhead statement, as it involves presenting results in SSMS.

DOH! :w00t: Good call. 11 second run time now.

-- Cory

Lowell SSC Guru Points: 323518 More actions · Answer 14

doh! Gianluca hit it! 16 seconds if i don't present the results in SSMS. i feel better now.

Gianluca Sartori (5/11/2010)
I get:
19 seconds for the first run
16 seconds for the second run
Don't run the select * from #myhead statement, as it involves presenting results in SSMS.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!