5

Right now I have one table of members with table primary key 'id' as auto incremented.

I want that instead of auto increment, it will generate a random integer of 5-6 digits and put that as 'id'. As every 'id' is primary key so generated int will be unique. Any ideas to implement this on sql server will help.

sk786
  • 394
  • 1
  • 4
  • 21
  • 5
    Why would you want that? Indexing an identity column is much better then indexing a random ordered int column. you should consider just adding a new column to your table that will hold the random int number with a unique constraint instead. – Zohar Peled Jun 17 '15 at 08:58
  • @ZoharPeled Thanks for your answer. Actually I want to allot a member unique random id so that he cant guess any other member's id. I thought of adding a new column but in my software this id column is linked to many other tables, so I thought if any solution that might help on this. – sk786 Jun 17 '15 at 09:01
  • If you've only got 5-6 digits, then it wouldn't be too hard to brute force another valid id. If you really want uniqueness that no-one's going to guess in a hurry then you should consider GUIDs. There will be a performance penalty though if you generate truly random ones. – DeanOC Jun 17 '15 at 09:11
  • 2
    You should build your system in such a way that knowing the member's Id will not effect security in any way. even if your member id is a random number of 6 digits, what's stopping your user to try another random 6 digit number? – Zohar Peled Jun 17 '15 at 09:12
  • 4
    @ZoharPeled Actually its not about security issue, its about guessing the total number of members in system. I dont want to expose member strength to members. I am thinking of manually inserting the random id via code after search for uniqueness. That is only option I can see now. – sk786 Jun 17 '15 at 09:16

4 Answers4

1

You can use CHECKSUM with NEWID() and use it as a DEFAULT like this.

SELECT ABS(CHECKSUM(NEWID())) % 100000

% 100000 is optional if you want to restrict the number to be max 5 digit.

Note: Having random generated clustered key will cause page splits during inserts.

ughai
  • 9,830
  • 3
  • 29
  • 47
1

To avoid guessing the total number of users, just add a random value as initial value when creating the database.

CREATE TABLE users
(
  ID int identity (7854, 7),
)

When also specifying an increment value > 1, you loose values of course. Check the value range with the expected number of records.

Another (much better) option would be to hide the primary keys from your users, and if they need to see some identification, they should see a value that is separate from the primary key. Add another value to the table, called "visible ID" or something.

Stefan Steinegger
  • 63,782
  • 15
  • 129
  • 193
0

Try using RAND function as below to generate 6 digit random number

select ceiling(RAND()*1000000)
Azar
  • 1,852
  • 15
  • 17
  • I am looking for auto generating of random ID as default, as right now I have auto increment indexing, instead of auto increment I want random integer. – sk786 Jun 17 '15 at 09:04
0
CREATE TABLE NEWID_TEST
(
ID UNIQUEIDENTIFIER DEFAULT NEWID() PRIMARY KEY,
TESTCOLUMN CHAR(2000) DEFAULT REPLICATE('X',2000)
)
GO

-- or use 

CREATE TABLE NEWSEQUENTIALID_TEST
(
ID UNIQUEIDENTIFIER DEFAULT NEWSEQUENTIALID() PRIMARY KEY,
TESTCOLUMN CHAR(2000) DEFAULT REPLICATE('X',2000)
)
GO
Manish Jesani
  • 1,339
  • 5
  • 20
  • 36
bggff
  • 1