How can i generate a personal unique number for user?

Question

Hello. i am creating a website for a courier in my local area and i want to be able to implement a feature.

Once the users' registers, they will be given a personal unique id which can be used for one user only to keep track of their data from the admin.

Now the unique id can only be generated for each users and it cannot be used for more than one user, in short. Every user will get a unique id and will not be able to get the same unique id

Accepted Answer

A very common way to do this is to have an auto-generated column in your table:

CREATE SEQUENCE UserID_Sequence AS int START WITH 1
go
CREATE TABLE Users (UserID int NOT NULL 
                       CONSTRAINT default_User_UserID DEFAULT NEXT VALUE FOR UserID_Sequence,
                    Username nvarchar(50) NOT NULL,
                    CONSTRAINT pk_Users PRIMARY KEY (UserID)
)
go
INSERT Users (Username)
   OUTPUT inserted.UserID, inserted.UserName
   VALUES('Nisse Nilsson'), ('Jacques LeMacque'), ('Flavio Tavio')
go
-- Cleanup
DROP TABLE Users
DROP SEQUENCE UserID_Sequence

Beware that the id may not always be contiguous, but there can be gaps. That should not be an issue in most context.

An similar technique is to use IDENTITY, which is very popular. However, IDENTITY gives you problems if you would need to insert rows with explicit IDs. With sequences you don't have that problem.

Answer

Hi @A.haque

You may consider using NEWID() to generate user id. It’s an RFC4122-compliant function that creates a unique value of type uniqueidentifier.

Best regards,

Cosmog

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Share via

How can i generate a personal unique number for user?

1 additional answer

Your answer