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.

Answer 2

Simplest Solution will be as below :

    /* Adding Leading 0 to [App_ID] */
[SELECT RIGHT(CONCAT('0000', (([App_ID] - (([App_ID] - 1) % 3)) / 3) + 1), 4) AS [App_ID]

I did the similar thing in my recent code, please find the below image.

Answer 3

Hope the below example will help you.

Explanation part - In the below Code, used the MAX(Primary_Key Identity column) and handled first entry case with the help of ISNULL(NULL,1). In All other cases, it will add up 1 and gives unique value. Based on requirements and needs, we can made changes and use the below example code. WHILE Loop is just added to show demo(Not needed actually).

IF OBJECT_ID('dbo.Sample','U') IS NOT NULL 
  DROP TABLE dbo.Sample 

CREATE TABLE [dbo].[Sample](
    [Sample_key] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,    
    [Student_Key] [int] UNIQUE NOT NULL,
    [Notes] [varchar](100) NULL,
    [Inserted_dte] [datetime] NOT NULL
)

DECLARE @A INT,@N INT
SET @A=1
SET @N=10
WHILE(@A<=@N)
BEGIN
    INSERT INTO [dbo].[Sample]([Student_Key],[Notes],[Inserted_dte])
    SELECT ISNULL((MAX([Student_Key])+1),1),'NOTES',GETDATE() FROM  [dbo].[Sample]
    SET @A+=1
END

SELECT * FROM [dbo].[Sample]