sql server - MAX/ORDER BY on char column -

in sql server 2005 database have column rma_number datatype char(10) in table rma.

the value increasing number format rma0002511. fastest way highest number increment on inserting?

my first approach was:

select     top (1) rma_number         rma     (rma_generated = 1) order creation_date desc 

but error-prone because somehow possible higher rma_number has earlier creation date. workaround, sorting primary key works:

select     top (1) rma_number         rma     (rma_generated = 1) order idrma desc 

but maybe possible source of error.

logically best way order rma_number desc.

but because not sure if gives correct result , thought sorting char column slow if number of records increase, chose order date column.

so,

1. is idea order char(10)-column (performance , accuracy)?
2. would better select max( rma_number ) rma highest number(perf. , accuracy)
3. should stick on using primary key order if first 2 points wrong or should use int column , format number in application?

edit:

i think must clarify haven't mentioned. rma_number not generated on every insert. maybe there many records without number. martin uses primary key build number. problem, because gaps big.

thank in advance.

the fastest , safest (for concurrency) way not store rma000... prefix @ all.

just create integer identity column , add prefix on via computed column.

create table #rma ( id int identity(2511,1) primary key, rma_number 'rma' + right('000000' + cast(id varchar(7)),7) )  insert #rma default values  select * #rma 

or following new info not records have rma_number use approach non blocking, efficient, , concurrency safe solution.

create table dbo.sequence(  val int identity (2511, 2) /*seed @ 1 + whatever current max value is*/  )  go  /*call procedure allocated next sequence number use*/      create proc dbo.getsequence @val int output begin tran     save tran s1     insert dbo.sequence default values     set @val=scope_identity()     rollback tran s1 /*rolls far save point prevent                         sequence table filling up. id allocated won't reused*/ commit tran