AutoNumber with specified format

Hi all,
In SQLServer 2005, are there any method to create the autonumber with
specific format?
for example,
I would like to have a key which the pattern is ABC00001 and the next
autonumber is ABC00002 and the next autonumber is ABC00003...
However, I won't like to handle it in the front-end application. Any idea?
Regards
alexGood wrote:
> Hi all,
> In SQLServer 2005, are there any method to create the autonumber with
> specific format?
> for example,
> I would like to have a key which the pattern is ABC00001 and the next
> autonumber is ABC00002 and the next autonumber is ABC00003...
> However, I won't like to handle it in the front-end application. Any
> idea?
> Regards
> alex
You'll have to roll that solution yourself. Your proposed key will
consume 8 bytes and give you only 100,000 values - unless you change the
prefix. In any case, I would store the next value as a numeric in a
"next key table" and use an instead of insert trigger on the table to
automatically create the key values. For example:
Set nocount on
go
create table dbo.NextKey (TableName nvarchar(255) not null primary key,
NextKeyVal int not null default 1)
insert into dbo.NextKey (TableName) Values ('TestTable')
Select * from dbo.NextKey
go
Create Table dbo.TestTable (SomeID char(8) not null primary key, SomeCol
int not null)
go
Drop Trigger TestTableIOI
go
Create Trigger TestTableIOI on dbo.TestTable Instead Of Insert
as
Begin
Declare @.NextKeyVal int
If (Select count(*) from inserted) != 1
RAISERROR ('Only one row inserted at a time please', 15, 1)
Else Begin
Update dbo.NextKey Set @.NextKeyVal = NextKeyVal = NextKeyVal + 1
Where TableName = N'TestTable'
If @.NextKeyVal Is Null
RAISERROR ('Could not determine next key value for table: %s', 15,
1, N'TestTable')
Else Begin
Insert dbo.TestTable (SomeID, SomeCol)
Select 'ABC' + right('00000' + cast(@.NextKeyVal as varchar(5)),
5), SomeCol From inserted
End
End
End
Go
Insert into dbo.TestTable (SomeID, SomeCol) Values ('', 5) -- Note: Must
supply a dummy value for PK
Insert into dbo.TestTable (SomeID, SomeCol) Values ('', 5) -- Note: Must
supply a dummy value for PK
Insert into dbo.TestTable (SomeID, SomeCol) Values ('', 5) -- Note: Must
supply a dummy value for PK
Insert into dbo.TestTable (SomeID, SomeCol) Values ('', 5) -- Note: Must
supply a dummy value for PK
Insert into dbo.TestTable (SomeID, SomeCol) Values ('', 5) -- Note: Must
supply a dummy value for PK
Select * from dbo.TestTable
Go
Drop Table dbo.TestTable
Drop Table dbo.NextKey
David Gugick - SQL Server MVP
Quest Software|||Alternatively, go with the identity column and use a computed column to gene
rate
the value derived from the identity column. In your example it looks like
the identity you need is just a concatenation of the identity value with
a fixed prefix. For example,
create function dbo.IdAsMyId (@.id int)
returns char(8)
as begin
declare @.prefix char(3);
set @.prefix = 'ABC';
declare @.leadingZeros int;
set @.leadingZeros = 8 - len(@.id) - len(@.prefix);
declare @.myId char(8)
set @.myId = 'ABC' + replicate('0', @.leadingZeros) + cast(@.id as varchar(5));
return @.myId;
end
go
create table CustomIdentity (
id int identity(1,1),
myId as dbo.IdAsMyId(id),
data varchar(255))
go
insert CustomIdentity (data) values ('One')
insert CustomIdentity (data) values ('Two')
insert CustomIdentity (data) values ('Three')
go
select *
from CustomIdentity
go
You probaby want to index the computed column if you're going to search on
it.
Cheers,
Stefan
http://www.fotia.co.uk

> Good wrote:
>
> You'll have to roll that solution yourself. Your proposed key will
> consume 8 bytes and give you only 100,000 values - unless you change
> the prefix. In any case, I would store the next value as a numeric in
> a "next key table" and use an instead of insert trigger on the table
> to automatically create the key values. For example:
> Set nocount on
> go
> create table dbo.NextKey (TableName nvarchar(255) not null primary
> key,
> NextKeyVal int not null default 1)
> insert into dbo.NextKey (TableName) Values ('TestTable')
> Select * from dbo.NextKey
> go
> Create Table dbo.TestTable (SomeID char(8) not null primary key,
> SomeCol
> int not null)
> go
> Drop Trigger TestTableIOI
> go
> Create Trigger TestTableIOI on dbo.TestTable Instead Of Insert
> as
> Begin
> Declare @.NextKeyVal int
> If (Select count(*) from inserted) != 1
> RAISERROR ('Only one row inserted at a time please', 15, 1)
> Else Begin
> Update dbo.NextKey Set @.NextKeyVal = NextKeyVal = NextKeyVal + 1
> Where TableName = N'TestTable'
> If @.NextKeyVal Is Null
> RAISERROR ('Could not determine next key value for table: %s',
> 15,
> 1, N'TestTable')
> Else Begin
> Insert dbo.TestTable (SomeID, SomeCol)
> Select 'ABC' + right('00000' + cast(@.NextKeyVal as varchar(5)),
> 5), SomeCol From inserted
> End
> End
> End
> Go
> Insert into dbo.TestTable (SomeID, SomeCol) Values ('', 5) -- Note:
> Must
> supply a dummy value for PK
> Insert into dbo.TestTable (SomeID, SomeCol) Values ('', 5) -- Note:
> Must
> supply a dummy value for PK
> Insert into dbo.TestTable (SomeID, SomeCol) Values ('', 5) -- Note:
> Must
> supply a dummy value for PK
> Insert into dbo.TestTable (SomeID, SomeCol) Values ('', 5) -- Note:
> Must
> supply a dummy value for PK
> Insert into dbo.TestTable (SomeID, SomeCol) Values ('', 5) -- Note:
> Must
> supply a dummy value for PK
> Select * from dbo.TestTable
> Go
> Drop Table dbo.TestTable
> Drop Table dbo.NextKey|||Stefan Delmarco wrote:
> Alternatively, go with the identity column and use a computed column
> to generate the value derived from the identity column. In your
> example it looks like the identity you need is just a concatenation of
> the identity value
> with a fixed prefix. For example,
>
I think if you go that route you are better off using a computed column
without the scalar function.
create table TestTable (id int identity not null primary key, ak as
'ABC' + right('00000' + cast(id as varchar(5)), 5), col1 int not null)
create unique index TestTable_ak on TestTable (ak)
go
insert into TestTable (col1) values (1)
insert into TestTable (col1) values (2)
insert into TestTable (col1) values (3)
Select * from TestTable
go
drop table TestTable
David Gugick - SQL Server MVP
Quest Software