如何儲存每個產品的版本序列

我需要創建一個如下表：

+----+---------+---------+
| Id | Product | Version |
+----+---------+---------+
|  1 |       1 |       1 |
|  2 |       1 |       2 |
|  3 |       2 |       1 |
|  4 |       1 |       3 |
|  5 |       2 |       2 |
+----+---------+---------+

IDan在哪裡Identity(1,1)，我需要Version根據插入來自動填充Product。

SELECT使用ROW_NUMBER()with可以達到類似的效果PARTITION：

SELECT ID, Product,
ROW_NUMBER() OVER(PARTITION BY Product ORDER BY ID) AS Version
FROM Products

我有哪些選擇？

如果您可以相信該IDENTITY列會不斷增加，我認為沒有理由儲存此資訊。它變得多餘，因為它是您可以從已經儲存在表中的數據中獲取的資訊（如您已經顯示的那樣）。儲存它在以下方面是浪費的：

• 額外的磁碟空間
• 額外的記憶體，因為數據希望大部分時間都在記憶體中
• 計算每個插入的版本號
• 每當刪除任何行時，重新計算所有相關行的版本號

現在，權衡是可衡量的，因為數據可能很少更新，但在更新之間卻被查詢了數百萬次。在這種情況下，預先計算數據並儲存它可能是有意義的。但是不要過早地為此進行優化；當您可以證明在執行時計算它會為您的工作負載增加可衡量的影響時，請做出該決定。

如果您不能相信該IDENTITY列會不斷增加（畢竟，有人可以返回並使用 填補刪除留下的空白IDENTITY_INSERT），那麼您可以添加一個預設為的SMALLDATETIME列，成本為每行 4 個字節（或者根據精度以不同的成本，如果您需要比分鐘更多的粒度）。然後，您仍然應該在查詢執行時計算版本號，恕我直言，使用與您建議的查詢類似的查詢：NOT NULL``CURRENT_TIMESTAMP``DATETIME2

SELECT Id, Product,
ROW_NUMBER() OVER (PARTITION BY Product ORDER BY inserted_date)
FROM dbo.Products; --*

*請始終使用架構前綴

如果你真的、真的、真的、真的、真的認為這是個好主意，那麼你可以通過後觸發來實現。請注意，即使使用具有預設值的非常精細的日期時間，您也不能僅依賴於插入順序語義（考慮如下所示的多行插入）。同樣，假設您可以依靠該IDENTITY列不斷增加且永不回填，您可以使用該列來打破任何關係。

USE tempdb;
GO

CREATE TABLE dbo.Products
(
 Id INT IDENTITY(1,1) PRIMARY KEY,
 Product INT,
 inserted_date DATETIME2(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
 [Version] INT
);
GO

CREATE TRIGGER dbo.AddRedundantInfoToProducts
ON dbo.Products
AFTER INSERT, DELETE
AS
BEGIN
 SET NOCOUNT ON;

 ;WITH src AS 
 (
   SELECT Id, [Version], rn = ROW_NUMBER() OVER 
     (PARTITION BY Product ORDER BY inserted_date, Id)
   FROM dbo.Products
   WHERE Product IN
   (
     SELECT Product FROM inserted
     UNION ALL
     SELECT Product FROM deleted
   )
 )
 UPDATE src SET [Version] = rn;
END
GO

現在我們可以測試一下：

INSERT dbo.Products(Product) VALUES(1),(1),(2),(1),(2);

SELECT Id, Product, inserted_date, Version FROM dbo.Products;

結果：

1   1   2015-05-27 16:18:19.723 1
2   1   2015-05-27 16:18:19.723 2
3   2   2015-05-27 16:18:19.723 1
4   1   2015-05-27 16:18:19.723 3
5   2   2015-05-27 16:18:19.723 2

現在讓我們刪除幾行：

DELETE dbo.Products WHERE Id = 2;

SELECT Id, Product, inserted_date, Version FROM dbo.Products;

結果：

1   1   2015-05-27 16:18:19.723 1 
3   2   2015-05-27 16:18:19.723 1
4   1   2015-05-27 16:18:19.723 2
5   2   2015-05-27 16:18:19.723 2

現在我們將插入一些新行：

INSERT dbo.Products(Product) VALUES(2),(2),(3);

SELECT Id, Product, inserted_date, Version FROM dbo.Products;

結果：

1   1   2015-05-27 16:18:19.723 1
3   2   2015-05-27 16:18:19.723 1
4   1   2015-05-27 16:18:19.723 2
5   2   2015-05-27 16:18:19.723 2
6   2   2015-05-27 16:19:00.773 3
7   3   2015-05-27 16:19:00.773 1
8   2   2015-05-27 16:19:00.773 4

清理：

DROP TABLE dbo.Products;

當然，如果您通過儲存過程控制 DML 到表，這會更容易；您可以在不需要觸發器的情況下計算新版本。但是，我覺得這甚至向您展示都是浪費精力；我根本不會推薦這種方法，因為您無法真正確保所有數據操作都將通過儲存過程，這意味著您的數據無論如何都可能很容易不同步（而在查詢執行時計算的排名始終保證是目前的）。給定上面的同一張表：

CREATE PROCEDURE dbo.InsertProductRow
 @Product INT
AS
BEGIN
 SET NOCOUNT ON;
 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
 BEGIN TRANSACTION;
 DECLARE @rn INT;
 SELECT @rn = COALESCE(MAX([Version]),0) + 1
   FROM dbo.Products
   WHERE Product = @Product;
 INSERT dbo.Products(Product, [Version]) VALUES(@Product,@rn);
 COMMIT TRANSACTION;
END
GO

CREATE PROCEDURE dbo.DeleteProductRow
 @Id INT
AS
BEGIN
 SET NOCOUNT ON;
 DECLARE @Product INT;
 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
 BEGIN TRANSACTION;
 SELECT @Product = Product FROM dbo.Products WHERE Id = @Id;
 DELETE dbo.Products WHERE Id = @Id;
 ;WITH src AS 
 (
   SELECT Id, [Version], rn = ROW_NUMBER() OVER 
     (ORDER BY inserted_date, Id)
   FROM dbo.Products
   WHERE Product = @Product
 )
 UPDATE src SET [Version] = rn;
 COMMIT TRANSACTION;
END
GO

以下批次將在每個步驟中產生與上述相同的結果：

EXEC dbo.InsertProductRow @Product = 1;
EXEC dbo.InsertProductRow @Product = 1;
EXEC dbo.InsertProductRow @Product = 2;
EXEC dbo.InsertProductRow @Product = 1;
EXEC dbo.InsertProductRow @Product = 2;

SELECT Id, Product, inserted_date, [Version] FROM dbo.Products;

EXEC dbo.DeleteProductRow @Id = 2;

SELECT Id, Product, inserted_date, [Version] FROM dbo.Products;

EXEC dbo.InsertProductRow @Product = 2;
EXEC dbo.InsertProductRow @Product = 3;
EXEC dbo.InsertProductRow @Product = 2;

SELECT Id, Product, inserted_date, [Version] FROM dbo.Products;

不要忘記清理：

DROP TABLE dbo.Products;
DROP PROCEDURE dbo.InsertProductRow, dbo.DeleteProductRow;

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