sql unique auto generation id

Hi - I created a login/registration for my website and I had one problem.
I want to provide unique customer number for every registered customer, how can I achieve that using sql while creating table itself.

Details:
My table is something is like below

CREATE TABLE  tbl_users (
userID int IDENTITY(1,1),
userName varchar(100) NOT NULL,
userEmail varchar(100) NOT NULL UNIQUE,
userPass varchar(100) NOT NULL,
userStatus varchar(1) NOT NULL CHECK (userStatus IN('Y', 'N')) DEFAULT 'N',
tokenCode varchar(100) NOT NULL,
PRIMARY KEY (userID)
)

Open in new window


so now if some one registers with my website I insert
username, email, pass, status, tokencode (from my php script)
but the only unique identifier is userID which is nothing but a simple number here
1 or 2 or 3 ....

I can't provide 1 as customer ID for my customer, so I am thinking of updating this line
userID int IDENTITY(1,1),

Open in new window


in create table such that it generates some type of unique id
9 digit number like 897634874
or combination of numbers and characters like C987D23R8
or some sort of unique ID using the registered user lastname like LNAME08924, USR032475


How can I do that ?

in simple I want my userID in create table to be a complex 9 digit ID than simple number which increments 1 every time.

Thanks
shragiAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vikas GargAssociate Principal EngineerCommented:
Hello,

when you create table you might be using like this

CREATE TABLE [dbo].YourTableName(
[UID] [int] IDENTITY(1,1) NOT NULL,
       ...
 )

In place of that what you can do is

CREATE TABLE [dbo].YourTableName(
[UID] [int] IDENTITY(789456140,1) NOT NULL,
       ...
 )

and the identity value will start from 789456140 and it will be incremented by 1 for each new record.

If you want to change it by adding other than 1 you can replace the 1 in the identity syntax with the required value

like below

CREATE TABLE [dbo].YourTableName(
[UID] [int] IDENTITY(789456140,3) NOT NULL,
       ...
 )
shragiAuthor Commented:
Hi Vikas - thats a clever way but what if I want to create a alphanumeric number like 78D67R879
Vikas GargAssociate Principal EngineerCommented:
Hi,

In that case you can have another column apart from the identity column.

that column will be computed column

ALTER TABLE Tablename
ADD CUID as ('HELLO' + CONVERT(VARCHAR(10),identitycolumn))

What this will do that when you insert a new record identity will be generated in identity column and in this column that number will be added along with pre defied pattern.

Hope this will help you to achieve your requirement .
SolarWinds® Network Configuration Manager (NCM)

SolarWinds® Network Configuration Manager brings structure and peace of mind to configuration management. Bulk config deployment, automatic backups, change detection, vulnerability assessments, and config change templates reduce the time needed for repetitive tasks.

Vitor MontalvãoMSSQL Senior EngineerCommented:
Can't understand why you want an UserID if you already have an Unique field:
userEmail varchar(100) NOT NULL UNIQUE
Scott PletcherSenior DBACommented:
@Vitor:

Email addresses can change, sometimes often.  For internal systems, you want an id that never changes.
Scott PletcherSenior DBACommented:
I'd create a function and call that to populate the table key.  That makes it easy to change the key style/pattern without affecting any other code.  For best performance, you'd then want to use an INSTEAD OF INSERT trigger on the table to do the actual INSERTs.

Btw, using a "random" key such as this in not necessarily wrong, but it will result in a more fragmented index, so you'd need to reorg/rebuild that index more often.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.