Composite field in SQL Server, does it exist?

Question

I'm designing a table to be used by a Web App, and my framework requires that the primary key be a surrogate key. But I also need an extra field which will be a composite of 2 other fields. In other words:

Id -> Primary key
OrderNumber
OrderDate
UniqueOrderNumber -> a composite of OrderNumber and OrderDate

UniqueOrderNumber should be a string generated by merging OrderNumber and OrderDate. This new field will then by used in certain queries that require a unique OrderNumber.

I wanted to avoid handling this logic in the application as I feel this should be a job for the databate. Is there a way to have the database generate this UniqueOrderNumber field automatically by concatenating the values of OrderNumber and OrderDate?

• md1337: I'm building a DB against a legacy system and OrderNumbers are NOT unique and they roll over, so I will make them unique in my DB using the dates. I need them unique because of some queries that require the OrderNumbers to be unique. And I also NEED a surrogate key because of the ORM framework I use (this is Id).
• JNK: Comments Removed -- Keep the comments civil, please. Md1337 you are here looking for help, please don't take criticism of your design personally.

Answer 1

You could leverage computed columns for this. So your create table would look like:

CREATE TABLE foo
(ID int not null,
 OrderNumber int not null,
 OrderDate datetime not null,
 UniqueOrderNumber AS 
 (CONVERT(varchar(20),OrderNumber) + ":" + CONVERT(varchar(20),OrderDate,112))
)

SQL Fiddle Example

• Jaxidian: This works well for us. Our requirements are very different than yours but we effectively do this to calculate a datetime column from a few other columns (month number, day number, year number, etc). In our case, the datetime column is only there to assist with queries but are ultimately unimportant.
• swasheck: @md1337 ... and if the data doesn't change (which i'd hope it wouldnt if it seems like you'd originally thought it would be a good PK), you can persist the computed column
• StixO: Just wondering if UniqueOrderNumber is really unique can we enforce a unique constraint on it?