Is it a good idea to use a computed column as part of a primary key ?
- by Brann
I've got a table defined as :
OrderID bigint NOT NULL,
IDA varchar(50) NULL,
IDB bigint NULL,
[ ... 50 other non relevant columns ...]
The natural primary key for this table would be (OrderID,IDA,IDB), but this it not possible because IDA and IDB can be null (they can both be null, but they are never both defined at the same time). Right now I've got a unique constraint on those 3 columns.
Now, the thing is I need a primary key to enable transactional replication, and I'm faced with two choices :
Create an identity column and use it as a primary key
Create a non-null computed column C containing either IDA or IDB or '' if both columns were null, and use (OrderID,C) as my primary key.
The second alternative seams cleaner as my PK would be meaningful, and is feasible (see msdn link), but since I've never seen this done anywhere, I was wondering if they were some cons to this approach.
