Composite increment column

user_659246 0
Viewed 247

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
sql-server-2008-r2 sql-server-2012 database-schema tsql sql-server
3 Answers

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]
user_79054440
Related