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?

Answer 1

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:

• Will a nonclustered index with a unique column always address all queries filtering that column first?
• 8k row overflow error when updating row of size 5k
• Is there a performance penalty when there are two unique indexes on a table with all of the colums in one index are also in the other index?
• Should I mark a composite index as unique if it contains the primary key?