Table design increment a column for each unique column

Question

Say I have a Customer table

id  |  customerId
1       569
2       569
1       577 
1       555
2       555
3       555

On each insert I want id increment by one depend on the customerId column. I have prepared my insert sp as below, but I wonder if there is a native way to make my table behave like this (when creating table with any constraints).

ALTER dbo.ins_Customer
@CustomerId INT
AS 
BEGIN
   DECLARE @MaxSeqId INT
   SELECT @MaxSeqId = MAX(id) FROM dbo.Customer 
   WHERE customerId = @CustomerId 
   INSERT INTO dbo.Customer VALUES (@MaxSeqId+1, @CustomerId)
END

I want this simple insert statement produce the result above with any possible, native constraints.

ALTER dbo.ins_Customer
@CustomerId INT
AS 
BEGIN
   INSERT INTO dbo.Customer VALUES (@CustomerId)
END

I believe you want the [IDENTITY](https://stackoverflow.com/questions/6777734/how-do-i-add-a-auto-increment-primary-key-in-sql-server-database) column type. — Neville Kuyt, Aug 29 '17 at 14:29
Yes I want Identity but for each `CustomerId`. Id will increment for each **any** record , but I want for each customerId. I mean customerId and id will be unique together. — ibubi, Aug 29 '17 at 14:31
What version of sql? For 2012 onward you may want to look at [SEQUENCE](https://learn.microsoft.com/en-us/sql/t-sql/statements/create-sequence-transact-sql). For a good post on it, [see this answer](https://stackoverflow.com/a/29954705/6167855) — S3S, Aug 29 '17 at 14:38
This cannot be done as part of the table definition. You'd have to use a trigger to set the values using the ROW_NUMBER function. — Jason A. Long, Aug 29 '17 at 14:39
@scsimon As I need to create a sequence for each customerId, sequnce will not work here I guess. — ibubi, Aug 29 '17 at 14:59

S3S · Answer 1 · 2017-08-29T14:45:26.613

2

Sure, just use row_number() on any query... no need to make this a permanent column

select
   row_number() over (partition by customerId order by (select 1)) as ID
  ,customerId
from
   yourTable

Note, this is often done in a VIEW so you don't have to update / alter your table.

edited Aug 29 '17 at 14:45

answered Aug 29 '17 at 14:21

S3S

24,809
5
26
45

1

sorry I didn't understand the answer, I have updated the question, I want the simple insert statement to give the result that I show. – ibubi Aug 29 '17 at 14:29

score 1 · Accepted Answer · answered Aug 29 '17 at 14:44

1

I wonder if there is a native way to make my table behave like this (when creating table with any constraints).

No, there is not. You will have to write custom code to make your table behave like this.

answered Aug 29 '17 at 14:44

Tab Alleman

31,483
7
36
52

Table design increment a column for each unique column

2 Answers2

Linked