4

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?

1 Answer 1

7

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:

0

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.