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

Question

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

Answer 1

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

Answer 2

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