Auto Increment field in SQL server

Hi ,

I need to have a primary key field and a not null unique field  with auto increment by 1 .
with below format for field 1 . AB0000001 where AB will be prefix and is of 10 digit length.

and second filed CD00000001 with CD as prefix and 10 charater length. Both need to have auto increment by 1 every time user enters data.

What are the best solution for this ? I have SQL SERVER 2008 Express R2 installed
Vikash pAsked:
Who is Participating?
 
ggzfabCommented:
I concur with COACHMAN99 to use a SQL identity field wit auto increment as that's the "normalized" way.
The "CD0000" string could be created using a function (e.g. "fncCode") when you need to print/show the field. That's separating the unique ID with the presentation and using such a function allows the change of the string for the entire application in one location! (Just imagine the 0's aren't sufficient...)
0
 
COACHMAN99Commented:
auto-incrementing strings is problematic. I would use the SQL identity field and in SQL I  would format the concatenation of 2 fields.
e.g. outputfield = 'CD' + format(ID,'0000000')
0
 
lcohanDatabase AnalystCommented:
Use 2 columns to form the PK where one would be a string data type (static "AB" char(2) for instance) AND the second column the IDENTITY auto increment by 1...

OR...even if not ideal you could use the GUID SQL data type with NEWID() to generate the unique, next value to be inserted.

SELECT NEWID();
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.