Unique 6-digit number, but not sequential for client identifier (SQL)

Question

In the table "Our client" there is a column "Identifier" for the identifier. This will be issued to customers, so when they call, they can simply give their identifier.

Now it’s obvious that our competitors can easily register on our site twice, say, divide a month and find out exactly how many people have registered.

Therefore, is there a simple easy way to create a “Customer Identifier” (in SQL or C #) that we could provide clients that: (a) 6 digits (b) is unique (c) is not sequential (

Thanks in advance

Answer 1

This may be crazy, but here is my way of generating customer numbers ahead.

This will result in many unique keys you want very quickly.

You may have saved them in a real table.

Here is the SQLFiddle 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, it's probably faster. It generates all 900,000 possible keys in about 30 seconds on my dev machine, which is suitable for a one-time 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() 

Answer 2

If you choose any increment that is not a factor of 1,000,000, you can take the last 6 digits of this number to provide an identifier; those. (IDENTITY (1,7)) % 1000000 .

But your competitors could still find growth by several consecutive registrations, so this would not completely solve the problem.

Thus, it seems to you that you want the number to be completely random - so for this you will need to check whether it already exists at its creation or pre-generate a list of numbers, sort them randomly and choose the next one when creating a new client.

Another consideration is some form of encryption, if you can find or create an appropriate algorithm that creates a fairly short output.

If you take a large zoom path without a factor, you can subsequently order the order of the digits to create a more random number - for example,

 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 select, some consecutive elements will have the same difference between them, but this number will be different. So this is not cryptographically secure, but probably enough to interfere with all but the most determined competitors.

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

Answer 3

You can calculate the column created from the Identity column and create the unique value that is expected.

for example, a calculated column as shown below:

100000 + Identity_Column * 7 + 3

Answer 4

What if juts uses user registration timestamp. It does not contain information about the number of users and is unique (if you do not register users every second, for example). For example, if you use 10000 in this query, you can register users every minute and get a unique number of 9 characters:

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

Answer 5

You can create a table with two columns, one with values ​​from 100,000 to 999.999 and one with a marker whether the number will be issued. When creating a new client, randomly assign an unassigned number from this table and mark it as assigned.