CODEWITHSUNDEEP
sqlsql-serverc#-4.0randomnumbers
DomBat
DomBat

Reputation: 2113

Unique 6 digit number but not sequential for Customer ID (SQL)

Our Customer table has an int Identity column for ID. This was going to be given out to customers, so when they phone they could just give their ID.

It is now obvious that our competitors would easily be able to register twice on our site, say a month apart and find out exactly how many people have registered.

Therefore, is there a nice simple way to create a "Customer ID" (in SQL or c#) which we could give to customers that is: (a) 6 digits long (b) is unique (c) is not sequential(

Thanks in advance

Upvotes: 6

Views: 6083

Answers (5)

Tom Chantler
Tom Chantler

Reputation: 14941

Maybe this is insane, but here is my way of generating the Customer Numbers up front.

This will generate however many UNIQUE keys you want very quickly.

You could obviously save these into a real table.

Here is a SQLFiddle of the below: http://www.sqlfiddle.com/#!3/d41d8/3884

DECLARE @tbl TABLE
(
    ID INT IDENTITY(1,1),
    CustNo INT UNIQUE
)

DECLARE @Upper INT
DECLARE @Lower INT
DECLARE @NumberRequired INT

SET @Lower = 100000 ---- The lowest random number allowed
SET @Upper = 999999 ---- The highest random number allowed
SET @NumberRequired = 1000 -- How many IDs do we want?

WHILE (SELECT COUNT(*) FROM @tbl) < @NumberRequired
BEGIN
    BEGIN TRY
        INSERT INTO @tbl SELECT (ROUND(((@Upper - @Lower -1) * RAND() + @Lower), 0))
    END TRY
    BEGIN CATCH
        -- If it goes wrong go round the loop again
    END CATCH
END

SELECT  *
FROM    @tbl

EDIT: Actually this is probably faster. It generates all 900000 possible keys in around 30 seconds on my dev machine, which is okay for a one-off job.

DECLARE @tbl TABLE
(
    ID INT
)

DECLARE @Upper INT
DECLARE @Lower INT
DECLARE @i INT;

SET @Lower = 100000 ---- The lowest random number allowed
SET @Upper = 999999 ---- The highest random number allowed

SET @i = @Lower

WHILE @i <= @Upper
BEGIN
    INSERT INTO @tbl SELECT @i
    SET @i = @i + 1
END

SELECT  ID
FROM    @tbl ORDER BY NEWID()

Upvotes: 3

podiluska
podiluska

Reputation: 51504

If you choose any increment that is not a factor of 1000000, then you could take the last 6 digits of that number to provide the ID; ie (IDENTITY (1,7)) % 1000000.

But your competitors could still find the increment by a few sequential registrations, so this would not completely solve the issue.

So it would seem you want a number that is completely random - so for that, you'll have to check whether it already exists when you generate it, or pre-generate a list of numbers, sort them randomly, and pick the next when creating a new customer.

Another option to consider is some form of encryption, if you can find or create an appropriate algorithm that creates a short enough output.

If you take the large non factor increment route, you could then subsequently re-arrange the order of the digits to create a more random number - eg;

declare @inc int , @loop int
declare @t table (i int, cn int, code varchar(4))

select @inc = 5173, @loop = 1
while @loop<=10000
begin
    insert @t (i, cn)
    select @loop, (@inc*@loop)%10000
    select @loop = @loop + 1
end

update @t 
set code = substring(convert(varchar(4),cn),2,1)
    + substring(convert(varchar(4),cn),4,1)
    + substring(convert(varchar(4),cn),3,1)
    + substring(convert(varchar(4),cn),1,1)

select code, count(*) from @t group by code having count(*)>1
select top 20 * from @t order by i

Depending on the number you choose, some sequential items will have the same difference between them, but this number will vary. So it's not cryptographically secure, but probably enough to thwart all but the most determined of competitors.

You could convert the above to a function to run off a standard IDENTITY(1,1) id field

Upvotes: 4

Pieter B
Pieter B

Reputation: 1967

You could make a table with 2 columns, one with the values 100.000 to 999.999 and one with a marker whether the number has been given out. When making a new client assign an unassigned number from this table at random and mark it assigned.

Upvotes: 0

valex
valex

Reputation: 24144

What if juts use user registration timestamp. It doesn't contain user's count information and unique (If you don't register users each second for example). For instance if you use 10000 in this query you can register users each minute and get unique 9 symbol digit:

select cast(cast(current_timestamp as float)*10000 as int)

Upvotes: 0

mehdi lotfi
mehdi lotfi

Reputation: 11581

You can have calculated column that generated from Identity column and create unique value that Expect.

for example calculated column like below :

100000 + Identity_Column * 7 + 3

Upvotes: 0

Related Questions