有沒有一種簡單的方法可以讓一個基於另一列的非 IDENTITY 自動遞增列？

+----+-------------+-----------------+-------------+
| ID | ProductName | ProductCategory | ProductCode |
+----+-------------+-----------------+-------------+
|  1 | Item 1      |               1 |           1 |
|  2 | Item 2      |               1 |           2 |
|  3 | Item 3      |               2 |           1 |
|  4 | Item 4      |               3 |           1 |
|  5 | Item 5      |               1 |           3 |
|  6 | Item 6      |               2 |           2 |
+----+-------------+-----------------+-------------+

基本上我想要做的是有一個列自動增量，但基於同一 ProductCategory 中的最後一行。

目前我有一個執行以下操作的儲存過程

SELECT TOP 1 @NextProductCode = ProductCode + 1 
 FROM Products 
WHERE ProductCategory = @ProductCategory

這可行，但儘管這是一個流量非常低的數據庫，但這裡存在明顯的並發問題。我猜我可以在儲存過程中拋出一個TABLOCKX，但我很好奇是否有更簡單的方法來完成這個。

我也在嘗試避免使用動態 SQL，否則我可以為每個新的 ProductCategory 創建一個序列。

如果我對您的理解正確，您正在尋找已經在數據庫中的一組產品，並為每個產品類別分配一個新的產品 ID，如果這是正確的，那麼您可以將其添加到您的選擇語句，

ROW_NUMBER() OVER (PARTITION BY ProductCategory ORDER BY ID) AS ProductCode

它應該做你正在尋找的東西。

我建議不要在 Products 表中搜尋最後一個 product_id，而是在表中添加一個計數器Category。

現在，您可以使用SELECT ... WITH (UPDLOCK)

• 查看Paul White的這個答案或Microsoft Docs關於表 HINTS 的答案。

DECLARE @ProductCode int = 0;

BEGIN TRANSACTION;

SELECT
   @ProductCode = ProductCode + 1
FROM
   Category WITH (UPDLOCK)
WHERE
   ID = 1;

UPDATE
   Category
SET
   ProductCode = @ProductCode
WHERE
   ID = 1;

-- Insert new products using @ProductCode

COMMIT TRANSACTION;

或者您可以利用OUTPUT 子句

DECLARE @ProductCode int = 0;
DECLARE @t TABLE (PCode int);

BEGIN TRANSACTION

UPDATE
   Category
SET
   ProductCode = ProductCode + 1
OUTPUT 
   inserted.ProductCode INTO @t
WHERE
   ID = 1;

SELECT 
   @ProductCode = PCode 
FROM 
   @t;

-- Insert new products using @ProductCode

COMMIT TRANSACTION

*db<>fiddle [here](https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=3ec7582fd4a4ba05d0f9d8b791a83bf7)*

引用自：https://dba.stackexchange.com/questions/223852