Composite increment column

Question

I have a situation where I need to have a secondary column be incremented by 1, assuming the value of another is the same.

Table schema:

CREATE TABLE [APP].[World]
(
    [UID] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
    [App_ID] [bigint] NOT NULL,
    [id] [bigint] NOT NULL,
    [name] [varchar](255) NOT NULL,
    [descript] [varchar](max) NULL,
    [default_tile] [uniqueidentifier] NOT NULL,
    [active] [bit] NOT NULL,
    [inactive_date] [datetime] NULL
)

First off, I have UID which is wholly unique, no matter what App_ID is.

In my situation, I would like to have id be similar to Increment(1,1), only for the same App_ID.

Assumptions:

1. There are 3 App_Id: 1, 2, 3

Scenario:

• App_ID 1 has 3 worlds
• App_ID 2 has 5 worlds
• App_ID 3 has 1 world

Ideal outcome:

App_ID  id
1       1
2       1
3       1
1       2
2       2
1       3
2       3
2       4
2       5

Was thinking of placing the increment logic in the Insert stored procedure but wanted to see if there would be an easier or different way of producing the same result without a stored procedure.

Figure the available option(s) are triggers or stored procedure implementation but wanted to make sure there wasn't some edge-case pattern I am missing.

Update #1

Lets rethink this a little.

This is about there being a PK UID and ultimately a Partitioned Column id, over App_ID, that is incremented by 1 with each new entry for the associated App_id.

• This would be similar to how you would do Row_Number() but without all the overhead of recalculating the value each time a new entry is inserted.
• As well App_ID and id both have the space and potential for being BIGINT; therefore the combination number of possible combinations would be: BIGINT x BIGINT

Answer 1

This is not possible to implement the way you are asking for. As others have pointed out in comments to your original post, your database design would be a lot better of split up in multiple tables, which all have their own identities and utilizes foreign key constraint where necessary.

However, if you are dead set on proceeding with this approach, I would make app_id an identity column and then increment the id column by first querying it for

MAX(identity)

and then increment the response by 1. This kind of logic is suitable to implement in a stored procedure, which you should implement for inserts anyway to prevent from direct sql injections and such. The query part of such a procedure could look like this:

INSERT INTO
  [db].dbo.[yourtable]
SET
  (
    app_id
    , id
  ) 
VALUES
  (
   @app_id
   , (
        SELECT
          MAX(id)
        FROM
          [db].dbo.[table]
          WHERE
            App_id = @app_id
     )
   )

The performance impact for doing so however, is up to you to assess. Also, you need to consider how to properly handle when there is no previous rows for that app_id.