CODEWITHSUNDEEP
sql-servert-sqlcalculated-columns
Matija Grcic
Matija Grcic

Reputation: 13371

Computed column expression

I have a specific need for a computed column called ProductCode

ProductId | SellerId | ProductCode
1           1           000001
2           1           000002
3           2           000001       
4           1           000003

ProductId is identity, increments by 1. SellerId is a foreign key.

So my computed column ProductCode must look how many products does Seller have and be in format 000000. The problem here is how to know which Sellers products to look for?

I've written have a TSQL which doesn't look how many products does a seller have

ALTER TABLE dbo.Product
ADD ProductCode AS  RIGHT('000000' + CAST(ProductId AS VARCHAR(6)) , 6) PERSISTED

Upvotes: 0

Views: 1173

Answers (2)

ErikE
ErikE

Reputation: 50201

You cannot have a computed column based on data outside of the current row that is being updated. The best you can do to make this automatic is to create an after-trigger that queries the entire table to find the next value for the product code. But in order to make this work you'd have to use an exclusive table lock, which will utterly destroy concurrency, so it's not a good idea.

I also don't recommend using a view because it would have to calculate the ProductCode every time you read the table. This would be a huge performance-killer as well. By not saving the value in the database never to be touched again, your product codes would be subject to spurious changes (as in the case of perhaps deleting an erroneously-entered and never-used product).

Here's what I recommend instead. Create a new table:

dbo.SellerProductCode

SellerID  LastProductCode
--------  ---------------
  1        3
  2        1

This table reliably records the last-used product code for each seller. On INSERT to your Product table, a trigger will update the LastProductCode in this table appropriately for all affected SellerIDs, and then update all the newly-inserted rows in the Product table with appropriate values. It might look something like the below.

See this trigger working in a Sql Fiddle

CREATE TRIGGER TR_Product_I ON dbo.Product FOR INSERT
AS
SET NOCOUNT ON;
SET XACT_ABORT ON;
DECLARE @LastProductCode TABLE (
   SellerID int NOT NULL PRIMARY KEY CLUSTERED,
   LastProductCode int NOT NULL
);

WITH ItemCounts AS (
   SELECT
      I.SellerID,
      ItemCount = Count(*)
   FROM
      Inserted I
   GROUP BY
      I.SellerID
)
MERGE dbo.SellerProductCode C
USING ItemCounts I
   ON C.SellerID = I.SellerID
WHEN NOT MATCHED BY TARGET THEN
   INSERT (SellerID, LastProductCode)
   VALUES (I.SellerID, I.ItemCount)
WHEN MATCHED THEN
   UPDATE SET C.LastProductCode = C.LastProductCode + I.ItemCount
OUTPUT
   Inserted.SellerID,
   Inserted.LastProductCode
INTO @LastProductCode;

WITH P AS (
   SELECT
      NewProductCode =
         L.LastProductCode + 1
         - Row_Number() OVER (PARTITION BY I.SellerID ORDER BY P.ProductID DESC),
      P.*
   FROM
      Inserted I
      INNER JOIN dbo.Product P
            ON I.ProductID = P.ProductID
      INNER JOIN @LastProductCode L
         ON P.SellerID = L.SellerID
)
UPDATE P
SET P.ProductCode = Right('00000' + Convert(varchar(6), P.NewProductCode), 6);

Note that this trigger works even if multiple rows are inserted. There is no need to preload the SellerProductCode table, either--new sellers will automatically be added. This will handle concurrency with few problems. If concurrency problems are encountered, proper locking hints can be added without deleterious effect as the table will remain very small and ROWLOCK can be used (except for the INSERT which will require a range lock).

Please do see the Sql Fiddle for working, tested code demonstrating the technique. Now you have real product codes that have no reason to ever change and will be reliable.

Upvotes: 2

Tim Lehner
Tim Lehner

Reputation: 15251

I would normally recommend using a view to do this type of calculation. The view could even be indexed if select performance is the most important factor (I see you're using persisted).

You cannot have a subquery in a computed column, which essentially means that you can only access the data in the current row. The only ways to get this count would be to use a user-defined function in your computed column, or triggers to update a non-computed column.

A view might look like the following:

create view ProductCodes as
select p.ProductId, p.SellerId,
    (
            select right('000000' + cast(count(*) as varchar(6)), 6)
            from Product
            where SellerID = p.SellerID
                and ProductID <= p.ProductID
    ) as ProductCode
from Product p

One big caveat to your product numbering scheme, and a downfall for both the view and UDF options, is that we're relying upon a count of rows with a lower ProductId. This means that if a Product is inserted in the middle of the sequence, it would actually change the ProductCodes of existing Products with a higher ProductId. At that point, you must either:

  • Guarantee the sequencing of ProductId (identity alone does not do this)
  • Rely upon a different column that has a guaranteed sequence (still dubious, but maybe CreateDate?)
  • Use a trigger to get a count at insert which is then never changed.

Upvotes: 0

Related Questions