How to make SQL Server 2008 table primary key auto increment with some prefix -

the given query in mysql format. want same query in sql server 2008.

create table table1_seq (   id int not null auto_increment primary key );  create table table1 (   id varchar(7) not null primary key default '0', name varchar(30) ); 

now trigger

delimiter $$ create trigger tg_table1_insert before insert on table1 each row begin   insert table1_seq values (null);   set new.id = concat('lhpl', lpad(last_insert_id(), 3, '0')); end$$ delimiter ; 

then insert rows table1

insert table1 (name)  values ('jhon'), ('mark'); 

and you'll have

|      id | name | ------------------ | lhpl001 | jhon | | lhpl002 | mark | 

this may answer question

-- create table 'abcd' prefix, combining identity column id create table dbo.persons  (    id int identity (1,1) not null   ,personid ('abcd' + convert(varchar(20), id)) persisted not null primary key    ,name varchar(100) ); go  -- not specify calculated column when insert values table insert dbo.persons (name) values ('person1'), ('person2'), ('person3'); go  -- display records table select personid, name dbo.persons; go 

this approach not require create separate table , trigger, therefore efficient.

hope helps.