Unique index on computed column when contributing columns are already unique

Question

I have a table like this:

CREATE TABLE
  dbo.DiscountBarcodeList
(
ID int NOT NULL IDENTITY
    CONSTRAINT PK_DiscountBarcodeList
    PRIMARY KEY CLUSTERED
, Discount int NOT NULL
    CONSTRAINT FK_DiscountBarcodeList_Discount
    FOREIGN KEY REFERENCES dbo.Discount (ID)
, CodeNumber int NOT NULL
, AssignedEntity int NULL
    CONSTRAINT FK_DiscountBarcodeList_AssignedEntity
    FOREIGN KEY REFERENCES dbo.Entity (ID)
    ON DELETE SET NULL
, BarcodeID AS
    CONVERT(
      char(10)
    , CAST(Discount AS binary(2)) + CAST(CodeNumber AS binary(3))
    , 2)
, CONSTRAINT UQ_DiscountBarcodeList_DiscountCodeNumber
    UNIQUE NONCLUSTERED 
    (
      Discount  ASC
    , CodeNumber ASC
    )
);

The table will hold CodeNumbers allocated for Discounts beforehand and assigned to Entitys on demand. Though not on the database level, the values of Discount and CodeNumber will be limited to 2 bytes and 3 bytes respectively. Those limitations, together with the unique constraint on (Discount, CodeNumber), will effectively make the generated BarcodeID values unique as well.

The way this table is supposed to be used is, the application will be passing a @BarcodeID to look up an @Entity to assign to it. If the lookup is successful, then either the row's Entity will be set to @Entity, or the application will be notified that the @BarcodeID is already taken, something along these lines:

BEGIN TRANSACTION;
UPDATE
SET
  @OldEntity = Entity
, Entity = @Entity
WHERE
  BarcodeID = @BarcodeID
;
IF @OldEntity IS NULL
BEGIN
  COMMIT TRANSACTION;
  ...  /* report success */
END
ELSE
BEGIN
  ROLLBACK TRANSACTION;
  ...  /* report failure */
END;

Now I would like to make BarcodeID sargable. Since I know that the column will only have unique values, I am considering to make the index unique as I think that can make my lookup more efficient. On the other hand, I am concerned that the generated values will have to be checked for uniqueness, which is redundant here since the uniqueness is already guaranteed.

Is it possible to somehow tell whether the benefits, if any, of a unique index on a computed column are going to outweigh the probable overhead of the (unnecessary in this case) uniqueness check? Or at least is it possible to determine that for a scenario like mine? Or am I just overthinking this?

Paul White · Accepted Answer · 2024-02-09T05:29:01.047

It's a trade-off only you can really decide on.

You'll already have a Split-Sort-Collapse operator combination added to any plans that update Discount or CodeNumber to avoid false transient unique key violations. Adding a unique index on BarcodeID will add a second such operator combination to update plans, and an Eager Spool to drive them. It is also possible for such updates to fail with an error if a worktable is required beyond the server's limits. Those are the main downsides.

On the upside, you get your seekable index and possibly better information for the optimizer to use in general. You also have an actual guarantee of uniqueness rather than a slightly woolly implied one.

Personally, I would probably add the unique index and test the workload to see if the performance impact is bearable or not.

Related Q & A:

Unique index on computed column when contributing columns are already unique

1 Answers1