CODEWITHSUNDEEP
sqlsql-serveridentity
Daniel Cruz
Daniel Cruz

Reputation: 2319

Create an identity column that depends on another column

Let's say that I have a table with the following columns

dbo.Orders (
    customerNumber INT,
    orderNumber INT,
    cost FLOAT, -- its just an example, there are other columns
    ...
)

I want the order number to autoincrement but only for the given customer. So for example, if I was to execute the following

INSERT INTO
    dbo.Orders (customerNumber, cost)
VALUES
    (1, 2500), -- would take orderNumber: 1 
    (1, 3500), -- would take orderNumber: 2
    (1, 1500), -- would take orderNumber: 3
    (2, 650), -- would take orderNumber: 1, because the customer is 2
    (2, 50), -- would take orderNumber: 2
    (1, 100), -- would take orderNumber: 4, because customers 1 last order was 3

And so on, such that there will be always an unique combination of customerNumber and orderNumber

I know I can enforce the uniqueness of the pair of values using an UNIQUE KEY with both columns, but I could not find anything about an IDENTITY using two columns. I found you can get something similar to an IDENTITY creating a sequence, but I could not find something that would allow me to have many sequences one per customer.

The only thing I can think off is to read the current value, then add one and then insert it, like so

SELECT @lastOrderNumber = orderNumber 
FROM dbo.Orders
WHERE customerNumber = @Customer

SET @newOrderNumber = ISNULL(@lastOrderNumber,0) + 1;

INSERT INTO dbo.Orders (customerNumber, cost, orderNumber)
VALUES (@Customer, 2500, @newOrderNumber)

But I think it becomes kind of cumbersome, its more difficult to do multiple inserts at once, and also becomes succeptible to racing conditions such as two insertions on the same customer nearly at the same time, something identities are very good at handling. I don't know if there is a better way

Thank you so much in advance

Upvotes: 2

Views: 84

Answers (2)

Dave.Gugg
Dave.Gugg

Reputation: 6781

I think this way of creating the schema is going to bite you in the long run. You're essentially forcing a PK to be two columns wide, so any FKs to that table are going to need to be two columns, which is wider than necessary. This looks like a fact table as opposed to dimension, so it may not be that bad, but it still seems unnecessary.

All that said, in addition to the solutions above, you could create a helper table to hold the next Order ID per customer. This would require some finesse because the INSERTS need to read and update the next number in a single transaction to prevent duplicates. This could lead to concurrency/blocking issues, but as long as the INSERT is not long/complex, it would take a mighty busy system to bog down.

CREATE TABLE #Orders
(
    customerNumber INT
  , orderNumber INT
  , cost DECIMAL(38, 2)
  , CONSTRAINT PK_Orders PRIMARY KEY ( customerNumber, orderNumber )
);

CREATE TABLE #CustomerOrderNumberCurrentValue
(
    customerNumber INT
  , LastUsedOrderNumber INT NOT NULL
  , CONSTRAINT PK_CustomerOrderNumberCurrentValue PRIMARY KEY (customerNumber)
);

--New customers would need to be added to this table
INSERT INTO #CustomerOrderNumberCurrentValue
(
    customerNumber
  , LastUsedOrderNumber
)
VALUES
(   1 , 0   ), ( 2, 0)

BEGIN TRAN

DECLARE @NextOrderNumber INT
SELECT @NextOrderNumber = LastUsedOrderNumber + 1 FROM #CustomerOrderNumberCurrentValue WHERE customerNumber = 1;

INSERT INTO #Orders
(
    customerNumber
  , orderNumber
  , cost
)
VALUES
(   1 , @NextOrderNumber , 2500   );

UPDATE #CustomerOrderNumberCurrentValue
SET LastUsedOrderNumber = @NextOrderNumber
WHERE customerNumber = 1;

COMMIT

BEGIN TRAN

SELECT @NextOrderNumber = LastUsedOrderNumber + 1 FROM #CustomerOrderNumberCurrentValue WHERE customerNumber = 1;

INSERT INTO #Orders
(
    customerNumber
  , orderNumber
  , cost
)
VALUES
(   1 , @NextOrderNumber , 3500  );

UPDATE #CustomerOrderNumberCurrentValue
SET LastUsedOrderNumber = @NextOrderNumber
WHERE customerNumber = 1;

COMMIT

BEGIN TRAN

SELECT @NextOrderNumber = LastUsedOrderNumber + 1 FROM #CustomerOrderNumberCurrentValue WHERE customerNumber = 1;

INSERT INTO #Orders
(
    customerNumber
  , orderNumber
  , cost
)
VALUES
(   1 , @NextOrderNumber , 1500   );

UPDATE #CustomerOrderNumberCurrentValue
SET LastUsedOrderNumber = @NextOrderNumber
WHERE customerNumber = 1;

COMMIT

BEGIN TRAN

SELECT @NextOrderNumber = LastUsedOrderNumber + 1 FROM #CustomerOrderNumberCurrentValue WHERE customerNumber = 2;

INSERT INTO #Orders
(
    customerNumber
  , orderNumber
  , cost
)
VALUES
(   2 , @NextOrderNumber , 650   );

UPDATE #CustomerOrderNumberCurrentValue
SET LastUsedOrderNumber = @NextOrderNumber
WHERE customerNumber = 2;

COMMIT

BEGIN TRAN

SELECT @NextOrderNumber = LastUsedOrderNumber + 1 FROM #CustomerOrderNumberCurrentValue WHERE customerNumber = 2;

INSERT INTO #Orders
(
    customerNumber
  , orderNumber
  , cost
)
VALUES
(   2 , @NextOrderNumber , 50   );

UPDATE #CustomerOrderNumberCurrentValue
SET LastUsedOrderNumber = @NextOrderNumber
WHERE customerNumber = 2;

COMMIT

BEGIN TRAN

SELECT @NextOrderNumber = LastUsedOrderNumber + 1 FROM #CustomerOrderNumberCurrentValue WHERE customerNumber = 1;

INSERT INTO #Orders
(
    customerNumber
  , orderNumber
  , cost
)
VALUES
(   1 , @NextOrderNumber , 100   );

UPDATE #CustomerOrderNumberCurrentValue
SET LastUsedOrderNumber = @NextOrderNumber
WHERE customerNumber = 1;

COMMIT

SELECT *
FROM #Orders;

DROP TABLE #Orders;
DROP TABLE #CustomerOrderNumberCurrentValue;

Note in the code above that each INSERT is a separate transaction that includes grabbing the next order ID and updating the table that holds it. Very important!

Upvotes: 1

Charlieface
Charlieface

Reputation: 72194

If you want to keep this simple and just use basic locking, it's not that hard. You don't need intermediate variables, but you do need correct locking hints. The locking hints used below, along with the index, will be all you need to ensure correctness.

INSERT INTO
    dbo.Orders (customerNumber, cost, orderNumber)
SELECT v.*,
  ISNULL(o.orderNumber, 0) + ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM (VALUES
    (1, 2500),
    (1, 3500),
    (1, 1500),
    (2, 650),
    (2, 50),
    (1, 100)
) AS v(customerNumber, cost)
OUTER APPLY (
    SELECT TOP (1)
        o.orderNumber
    FROM dbo.Orders o WITH (SERIALIZABLE, UPDLOCK)  -- the UPDLOCK is absolutely essential!!
    WHERE o.customerNumber = v.customerNumber
    ORDER BY
        o.orderNumber DESC
) AS o;

You must put an index on the table (customerNumber ASC, orderNumber DESC) otherwise you'll get huge locking problems and slow query plans.

If you are set on using sequences, then given the relatively low numbers of possible customerNumbers, you could create a sequence per value. You can automate this using triggers.

I'd advise shoving them into another schema for easy management sake.

CREATE OR ALTER TRIGGER tr_CreateCustomerSequence ON dbo.Customer
AFTER INSERT, DELETE
AS

SET NOCOUNT ON;

DECLARE @sql nvarchar(max);

SELECT @sql = STRING_AGG(CONCAT(N'
CREATE SEQUENCE Sequences.Customer_', i.customerNumber, ' AS int MINVALUE 1 NOCACHE'), '')
FROM inserted i
WHERE NOT EXISTS (SELECT 1
    FROM sys.sequences seq
    WHERE seq.name = CONCAT(N'Customer_', i.customerNumber)
);

EXEC @sql;


SELECT @sql = STRING_AGG(CONCAT(N'
DROP SEQUENCE Sequences.Customer_', i.customerNumber), '')
FROM deleted;

EXEC @sql;

Then you can use them pretty simply, using sp_sequence_get_range to get the values dynamically. The only complication is that you need to split the insert batch per customer, not once for all customer orders.

DECLARE @customerNumber int = 1;   -- or a parameter
DECLARE @numberOfInsertRows bigint = 3;

DECLARE @sequence_name nvarchar(776) = CONCAT(N'Sequences.Customer_', @customerNumber);
DECLARE @range_first_value int;

EXEC sp_sequence_get_range
  @sequence_name = @sequence_name,
  @range_size = @range_size,
  @range_first_value = @range_first_value OUT;


INSERT INTO
    dbo.Orders (customerNumber, cost, orderNumber)
SELECT v.*,
  @range_first_value + ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1
FROM (VALUES
    (1, 2500),
    (1, 3500),
    (1, 1500)
) AS v(customerNumber, cost);

You can alternatively call sp_sequence_get_range from client-side app code, and then pass the correct values in.

Upvotes: 3

Related Questions