auto generate ID'S in sqlserver

[ganesh22]

Hi,
My requirement is i want to auto generate ID'S in sqlserver that means
in database one column is there recordid for that coloumn i want to add numbers with no duplication like JDS 10001,JDS 10002,JDS 10003............like that if any record added in database means automatically add that type of id's
if suddenly we deleted that record means that id will not add again how to do dat??

[Delerna]

1) Does "JDS " have to be part of the number or can it be totally numeric?
2) If "JDS" is required is there always a single space between it and the number part?
3) can the most recent ID be reused if it gets deleted?

suppose the last ID in the sequence is JDS 1167
If JDS 1167 gets deleted then it will be reused
if JDS 1001 or any other ID less than JDS 1167 gets deleted it wont be reused

Is that acceptable?

[deepuv04]

Hi,
My requirement is i want to auto generate ID'S in sqlserver that means
in database one column is there recordid for that coloumn i want to add numbers with no duplication like JDS 10001,JDS 10002,JDS 10003............like that if any record added in database means automatically add that type of id's
if suddenly we deleted that record means that id will not add again how to do dat??

Try the following code, hope will give you some idea...

[code =sql]
create table Sample_Table(pk int identity(1001,1) primary key, CustomKey as 'JDS '+ cast(pk as varchar));

insert Sample_Table default values;

insert Sample_Table default values;

insert Sample_Table default values;


select * from Sample_Table;

[/code]