Unique random integer as primary key ID instead of auto increment [duplicate]

Question

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.

Answer 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.