Avoid generating duplicating random number

Question

As per the module requirement file name length to be as 8 chars, for that to implement first 4 char DDMM and remaining 4 char trying to fetch the random numbers from the database by using function and view, the same what I am using in database I have pasted below:

Function:

CREATE FUNCTION [dbo].[GenerateRandomNumbersLetters]
(
    @NumberOfCharacters TINYINT
)
RETURNS VARCHAR(32)
AS
BEGIN
    RETURN 
    (
        SELECT LEFT(REPLACE([NewID], '-', ''), @NumberOfCharacters)
        FROM dbo.RetrieveNewID
    );
END

View:

CREATE VIEW [dbo].[RetrieveNewID]
AS
    SELECT [NewID] = NEWID();

My query:

select  
   SUBSTRING(replace(convert(varchar(10), getdate(), 3), '/', ''), 1, 4) +  
   dbo.GenerateRandomNumbersLetters(4) as FileNamerandomNUM

Ex: 0907CCE7

For every row it will provide a random number, but in one scenario recently the random generate duplicates, how can I avoid such scenarios also, kindly advice

I assume that there's no risk that you'll try to generate more that 65536 file names on a single day? — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Jul 12, 2012 at 8:02
yeah, but i am wondering for one particular file it duplicates last four digits random number, can it possible to cross check while generating? — user1494292
– user1494292, Commented Jul 12, 2012 at 8:04
Why the requirement to generate a random sequence number for the file name? Why not use a simple sequential number scheme? You cannot answer anything 'security' since 4 digits is ridiculously small space for security concerns. — Remus Rusanu
– Remus Rusanu, Commented Jul 12, 2012 at 8:22
ok, can you give some tips to generate a simple sequential number scheme about for 4 Digits please, i will try and perform it,thanks for prompt reply — user1494292
– user1494292, Commented Jul 12, 2012 at 8:26

Joe G Joseph · Accepted Answer · 2012-07-12 08:43:46Z

1

There is a risk of 'value repeating' for random numbers especially if you take only the first four digit of a random number.

Instead of that , generate sequence numbers. to implement this you can create a table with structure

file_date | seq_no

Ex: 0907 | 1000
    0907 | 1001

then each time you want to get a file name, query against this table for the next sequence number

select max(seq_no)+1 from <table>

answered Jul 12, 2012 at 8:43

Joe G Joseph

24.2k5 gold badges58 silver badges58 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Avoid generating duplicating random number

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related