How to generate a local Id based on a specific column?-CodePudding

How can insert a row in SQL and add a value that would represent an internal counter grouped by a certain column value.

For example

CREATE TABLE Product (
    Id int IDENTITY(1,1) PRIMARY KEY NOT NULL, 
    StoreId int, 
    StoreProductId int, 
    ProductName varchar(255)
)

when insert a row such as this

INSERT INTO(storeID, productName)
select 1, 'MyProduct'

I want to have values (1, 1, 1, 'MyProduct')

If I add a new product for that same store I want to have values (2, 1, 2, 'MyProduct2')

For a different store I want to have values (3, 2, 1, 'MyProduct3')

How do I do it safely ie. not having duplicate StoreProductId? I tried this using computed column column, but I was unable to use count, also I tried to use trigger on insert, but not sure if that is the right way to avoid duplicates.

CodePudding user response：

You can use function with computed column:

CREATE  FUNCTION dbo.fnGetStoreProductId(@id INT)
RETURNS int
AS
BEGIN
    DECLARE @StoreProductId INT
    ;WITH cte AS 
    (
        SELECT Id, ROW_NUMBER() OVER (PARTITION BY StoreId ORDER BY Id) rn
        FROM dbo.Product
    )
    SELECT @StoreProductId = rn
    FROM cte
    WHERE cte.Id = @id

    RETURN @StoreProductId
END
GO

ALTER TABLE dbo.Product Add  StoreProductId AS dbo.fnGetStoreProductId(Id)

CodePudding user response：

This is not something you should be storing at all.

There are numerous problems with such a design, such as:

Impossible to ensure integrity, without resorting to triggers.
You cannot guarantee sequential data if updates and/or deletes are allowed.
Insert performance is massively impacted.

Instead just calculate it when you need at the time of querying, using ROW_NUMBER

SELECT
  p.Id,
  p.StoreId,
  StoreProductId = ROW_NUMBER() OVER (PARTITION BY p.StoreId ORDER BY p.Id)
  p.ProductName
FROM dbo.Product p;