Say I have a Projects table in a database to track active projects, as well as a Tasks table that tracks the Tasks for each Project. Each table has a normal auto generated primary key, however I also want to each task to have a TaskNumber relative to the Project. Thus as Tasks are added to a Project they are assigned incrementing numbers from 001, 002, 003 etc, starting at 001 for each project.

First I thought I could store a NextTaskNumber field with each Project. However, it would be possible for multiple people to request the same number from the database before any have a chance to add their new task and increment the NextTaskNumber field. I don't want to end up with duplicate Task numbers with in a Project.

---

Update

Thanks for the answers so far but I forgot to mention one critical point. I'm actually accessing the database through NHibernate not direct开发者_高级运维ly through SQL commands. So is there a way to do this through N Hibernate mapping? Or alternatively could I setup a trigger on the database to populate the task number field when a new task is created?

---

You can use your NextTaskNumber idea if you have control enough over the code so that you can force the piece that does the inserts to the Task table to use a transaction that includes the reading of NextTaskNumber, the creation of the Task row, and then the updating of NextTaskNumber. The transaction should lock the data so that you won't have duplicate TaskNumber entries.

---

The following code is subject to my usual "I just woke up" liabilities:

Single Insert

INSERT INTO Tasks
            (ProjectID,Name,GroupedIndexField)
SELECT @ProjectID
       , 'Do Some Work'
       , ( SELECT ISNULL(MAX(GroupedIndexField), 0)
             FROM Tasks
            WHERE ProjectID = @ProjectID ) + 1

Multiple Insert

INSERT INTO Tasks
            (ProjectID,Name,GroupedIndexField)
SELECT tblTasksToInsert.ProjectID
       , tblTasksToInsert.Name
       , ( SELECT ISNULL(MAX(GroupedIndexField), 0)
             FROM Tasks
            WHERE ProjectID = tblTasksToInsert.ProjectID ) + ROW_NUMBER() OVER (PARTITION BY tblTasksToInsert.ProjectID ORDER BY InsertionOrder)
  FROM tblTasksToInsert

Create a View

Create a view with with following query:

SELECT TaskID
       , ProjectID
       , ROW_NUMBER() OVER (PARTITION BY ProjectID ORDER BY TaskID ) AS GroupedIndexValue
  FROM Tasks

SQL Server Denali

SQL Server Denali allows you to create sequences. You could have multiple sequences per project, although this isn't really the right way to use them.