sql - Identity-like column but based on Group By criteria

Question

In my SQL Server 2012 database, I'm creating a "Tasks" table that will have a compound primary key composed of three fields:

Issue_ID [int] NOT NULL,
Issue_Sub_ID [int] NOT NULL,
Task_ID [int] NOT NULL

Issue_ID and Issue_Sub_ID are foreign keys to other tables. In the table I'm creating, there can be many tasks associated with each Issue_ID / Issue_Sub_ID combination.

What I would like to do is establish a default value for the Task_ID column, similar to if I used IDENTITY(1,1), but that will auto-increment based on the Issue_ID / Issue_Sub_ID group. For example, the Task_ID results would look as follows, given the provided Issue_ID / Issue_Sub_ID values:

Issue_ID     Issue_Sub_ID     Task_ID
========     ============     =======
12345             1              1
12345             1              2
12345             1              3
12345             2              1
12345             2              2
67890             2              1
67890             2              2
67890             2              3

I'm familiar with the ROW_NUMBER() OVER(PARTITION BY Issue_ID, Issue_Sub_ID ORDER BY Issue_ID, Issue_Sub_ID) possible solution but, as I'd like this column to be a part of the compound primary key of the table, I don't think that will work.

Thanks all in advance.

Answer 1

I agree with Sean - add an identity column, and then just use a computed column for the task id. Even though I've answered a question very much like this one here, I'm not sure about marking this one as a duplicate. The reason for this is that you want to use the task_id as a part of the primary key.
However, I'm not sure that's possible, since in order to include a computed column in the primary key it must be persisted, and for some reason (I think it's because of the use of a UDF) SQL Server will not allow me to mark it as persisted.
Anyway, here is my proposed solution for this:

First, create a function that will calculate the task id:

CREATE FUNCTION dbo.GenerateTaskId
(
    @Row_Id int,
    @Issue_Id int,
    @Issue_Sub_Id int
)
RETURNS Int
AS
BEGIN

    RETURN 
    (
        SELECT COUNT(*)
        FROM dbo.Tasks
        WHERE Issue_Id = @Issue_Id
        AND Issue_Sub_ID = @Issue_Sub_ID
        AND Row_Id <= @Row_Id
    )
END
GO 

Then, create the table with the task id as a computed column:

CREATE TABLE dbo.Tasks
(
    Row_Id [int] IDENTITY(1,1),
    Issue_ID [int] NOT NULL,
    Issue_Sub_ID [int] NOT NULL,
    Task_Id AS dbo.GenerateTaskId(Row_Id, Issue_Id, Issue_Sub_Id), 
    CONSTRAINT PK_Tasks PRIMARY KEY (Row_Id)
)
GO

Now, test it:

INSERT INTO Tasks VALUES
(12345, 1),
(12345, 1),
(12345, 1),
(12345, 2),
(12345, 2),
(67890, 2),
(67890, 2),
(67890, 2)

SELECT *
FROM Tasks

Results:

Row_Id  Issue_ID    Issue_Sub_ID    Task_Id
1       12345       1               1
2       12345       1               2
3       12345       1               3
4       12345       2               1
5       12345       2               2
6       67890       2               1
7       67890       2               2
8       67890       2               3

You can see a live demo on rextester.