I agree, I posted a similar response to the original post B4 I read this
reponse.


"Erland Sommarskog" <esquel@sommarskog.se> wrote in message
news:Xns961260ED91B7BYazorman@127.0.0.1...
> Jared Evans (jnevans@gmail.com) writes:
> > This table will also have other rows such as
> >
> > INV90000001
> > INV90000002
> > INV90000003
> > PYMT4000001
> > PYMT4000002
> >
> > etc...
> >
> > For example, the next PYMT row would be
> > PYMT4000003
> >
> > so if I insert a row that is of type "Payment", it would autoincrement
> > to this next value in the sequence.
> >
> > Since I would much prefer to keep tracking this kind of sequence, I
> > would not be able to use the id column in this case.
>
> You could have two columns, one with the type and one with the id. Or
> maybe even three, as that digit after the code is something you have
> not explained yet. And then you could have an computed column that
composes
> the strings. If you will use the string for lookups, you will need to
> index the column. The one thing to keep in mind is to make sure that
> you will have a couple of settings on, as described in Books Online.
> Of these, the trickeist is ARITHABORT, since it is not ON by default.
>
> As for the id, you would have roll your own, but that's simple:
>
> BEGIN TRANSACTION
> SELECT @id = coalesce(MAX(id), 0) + 1 FROM tbl (UPDLOCK)
> WHERE type = @type
>
> INSERT tbl (id, type, ...
> VALUES (@id, @type, ...
>
> ...
> COMMIT TRANSACTION
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinf...2000/books.asp