Unique index on computed column when contributing columns are already unique

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 i

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?

StackExchange Database Administrators Q#335663, answer score: 7