>Hi All,
>
>I need to create an auto-incrementing number for a series of records in a child table. It's not a PK just a unique identifier for a record.
>
>I have a parent table called 'Project' which can have 0 or many child records in a table called 'Job'. Each child job record (for a project) would start with 1 and increment up as each record is added. The number is only unique in the domain of a single project. Looks something like this.
>
>

Project 1
>   Job 1
>   Job 2
>   Job 3
>Project 2
>   Job 1
>   Job 2
>   Job 3

>
>I was considering using a table and storing the last used number (similiar to the new_id function) but I would have to keep a record for each Project.
>
>I'm just wondering if there is a better way to do this.
>
>TIA

If it won't cause problems to pessimistically lock all jobs an entire procect, (briefly) then you can easily determine the the last job # within the project and increment by 1.

Howver doing somethng like a modified newid function where a new record is added with each project (as you have considered) would probably give you better performance. It would also allow optimistic record locking.

Possible Alternative:

Is it absolutely vital that the job # repeat? That is, do your business needs allow the following:

Project 1
   Job 1
   Job 2
   Job 3
Project 2
   Job 4
   Job 5
   Job 6

If you could do this then the Newid function would unmodifiably work.
Your job numbers in addition to being unique within a project would "happen" to be unique witin the jobs table. If this does not meet your users needs then you will need to use one of the two alternative s I mentioned above:

1) either do pessimitic record locking of an entire project when adding a job, 2) or use a modifed newid function against a table that holds one record for each project. This alternative, which you have already considered, may actually be the best way if your business needs are exactly as described. No pessimistic record locking is required, and performance is actually better.