0

I need to create a table like the following one:

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

Where ID is an Identity(1,1) and I need the Version to be auto-populated depending on being inserted Product.

A similar effect could be achieved upon SELECT by using ROW_NUMBER() with PARTITION like this:

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

What are my options?

2
  • Why does this sequence need to be stored? Do you have gobs of extra memory to donate to this? May 27, 2015 at 19:27
  • @AaronBertrand Well, I need to know in what order these rows were inserted... I believe I can add a column with default set to GETDATE()
    – Jyrkka
    May 27, 2015 at 19:31

1 Answer 1

5

If you can trust the IDENTITY column to be ever-increasing, I see no reason to store this information. It becomes redundant because it's information you can obtain from data already stored in the table (as you have already shown). Storing it is wasteful in terms of:

  • additional disk space
  • additional memory, since the data will hopefully be in memory most of the time
  • calculating the version # on every insert
  • re-calculating the version # for all related rows whenever any row is deleted

Now, the trade-off is measurable, since it is possible that the data is updated very infrequently, yet queried millions of times between updates. In that case, it may make sense to pre-calculate the data and store it. But don't prematurely optimize for that; make that decision when you can demonstrate that calculating it at runtime is adding measurable impact to your workload.

If you can't trust the IDENTITY column to be ever-increasing (after all, someone could go back and fill gaps left by deletes using IDENTITY_INSERT), then you could add a SMALLDATETIME column as NOT NULL with a default of CURRENT_TIMESTAMP, at a cost of 4 bytes per row (or DATETIME2 at varying cost depending on precision, if you need more granularity than minute). Then you should still calculate the version # at query runtime, IMHO, using a similar query to the one you proposed:

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

* Please always use the schema prefix

If you really, really, really, really, really think this is a good idea, then you can achieve this with an after trigger. Note that even with a very granular datetime with a default, you can't rely on just that for order-of-insert semantics (think multi-row inserts like below). Again, assuming you can rely on the IDENTITY column to be ever-increasing and never back-filled, you can use that column to break any ties.

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

Now we can test it out:

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

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

Results:

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

Now let's delete a couple of rows:

DELETE dbo.Products WHERE Id = 2;

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

Results:

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

Now we'll insert a few new rows:

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

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

Results:

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

Clean up:

DROP TABLE dbo.Products;

Of course this can be easier if you control DML to the table through a stored procedure; you could calculate the new version(s) without the need for a trigger. However, I feel this is a waste of effort to even show you; I wouldn't recommend this approach at all because you can't ever really ensure all data manipulation will go through stored procedures, which means your data could easily get out of sync anyway (whereas the rank calculated at query runtime is always guaranteed to be current). Given the same table above:

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

The following batch will yield the same results at each step as above:

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;

Don't forget to clean up:

DROP TABLE dbo.Products;
DROP PROCEDURE dbo.InsertProductRow, dbo.DeleteProductRow;
1
  • Thank you very much Aaron, I really, really, really appreciate your efforts put in so detailed answers, I think that I learned a lot from them. I beg your pardon for spending so much of your time.
    – Jyrkka
    May 28, 2015 at 8:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.