如何设置 SQL Server 来生成分组索引？

Question

假设我在数据库中有一个 Projects 表来跟踪活动项目，以及一个 Tasks 表来跟踪每个 的 Tasks项目。每个表都有一个正常的自动生成的主键，但是我也希望每个任务都有一个相对于 Project 的 TaskNumber 。因此，当将任务添加到项目时，它们会被分配从001、002、003等递增的编号，每个项目从001开始。

首先，我认为我可以为每个 Project 存储一个 NextTaskNumber 字段。但是，在任何人有机会添加新任务并增加 NextTaskNumber 字段之前，多个人可能会从数据库请求相同的编号。我不想在 Project 中出现重复的任务编号。

---

更新

感谢您迄今为止的回答，但我忘记提及一个关键点。我实际上是通过 NHibernate 访问数据库，而不是直接通过 SQL 命令。那么有没有办法通过N Hibernate映射来做到这一点呢？或者，我可以在数据库上设置一个触发器，以便在创建新任务时填充任务编号字段吗？

Answer 1

您可以使用您的 NextTaskNumber 想法如果您对代码有足够的控制权，以便您可以强制执行任务表插入操作的部分使用包含读取 NextTaskNumber 的事务、创建任务行，然后更新 NextTaskNumber。事务应该锁定数据，这样就不会出现重复的 TaskNumber 条目。

Answer 2

以下代码是我通常的“我刚刚醒来”的责任：

单次插入

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

多次插入

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

创建视图

使用以下查询创建视图：

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

SQL Server Denali

SQL Server Denali 允许您创建序列。每个项目可以有多个序列，尽管这并不是真正使用它们的正确方法。