Custom Auto-Generated Sequences with SQL Server

Let's start with the simple "C0000" - "C9999" example. First, let's create our Customers table like this:
create table Customers
(
dbID int identity not null primary key,
CustomerName varchar(100)
)
Note that the dbID column is standard, database-generated identity which will be our
physical primary key of the table. However, we will add a CustomerNumber column
which will be what we expose to the outside world in the "C0000" format, as described.
Let's create a function accepts an integer, and uses that integer to return our CustomerNumber:
create function CustomerNumber (@id int)
returns char(5)
as
begin
return 'C' + right('0000' + convert(varchar(10), @id), 4)
end
Using that function, we can simply add a computed column to our table like this:
alter table Customers add CustomerNumber as dbo.CustomerNumber(dbID)
Or, we could also create a column in our table to store the Customer Number,
and use a trigger to populate it:
alter Customers add CustomerNumber varchar(10)
create trigger Customers_insert on Customers
after insert as
update
Customers
set
Customers.customerNumber = dbo.CustomerNumber(Customers.dbID)
from
Customers
inner join
inserted on Customers.dbID= inserted.dbID
Using either method, once they are in place, we can simply
insert into our table, and for each Row added a unique "Customer Number" is assigned:
insert into Customers (CustomerName) values ('jeff')
select * from Customers
returns:
(1 row(s) affected)
dbID CustomerName CustomerNumber
----------- ------------ --------------
1 jeff C0001
(1 row(s) affected)
Follow this link:
http://www.sqlteam.com/article/custom-auto-generated-sequences-with-sql-server