Sql-server – AutoIncrement non-primary key or Make unique composite key

composite-typescomputed-columnsql serversql server 2014

I have a db with a table that has a rollover value such as OrderNumber and another column is year.

This is the primary key – a composite key – but there are other tables with which I need to use a foreign key relationship – I really would prefer to have an auto increment surrogate key for this table, the trick though is that OrderNumber and Year must be unique. IS there a good way to handle this?

I was reading here for some inspirations. Composite field in SQL Server, does it exist?

Best Answer

As Vérace suggested, you can do both: create a surrogate (auto-increment) key to simplify joins and related table structures, and add a unique constraint on the two current PK columns to continue to enforce the proper data integrity.

In SQL Server, multiple columns can participate in a unique index / constraint:

ALTER TABLE dbo.Orders
ADD CONSTRAINT UQ_Year_OrderNumber
UNIQUE ([Year], OrderNumber);

Thus there is no need to create a computed column to combine the two columns.

Related Solutions

Sql-server – Replacing composite key with surrogate

Reading up on Kimball dimensional modeling, use of a surrogate key, in particular an IDENTITY, will help reduce fragmentation caused by page splits as you'll be appending rows at the end of leaf pages vs. attempting to insert them in the middle if the keys are not in the order of the index (ascending or descending depending on how the index was defined).

But, if the surrogate key will not be used in joins with other tables or in WHERE criteria, use of the surrogate key as a clustered index might not provide any additional benefit after the initial data import. If you keep the composite clustered key, sorting the source data in the order of the six-column composite key prior to import should avoid the page split fragmentation you're witnessing.

As for a choice of clustered index, the best candidate may be the column(s) that would be used in your most common/critical queries for WHERE criteria or joins. Use of a NCI will require a bookmark lookup to obtain the remaining values in the table if needed in the resultset.

Sql-server – Composite field in SQL Server, does it exist

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

Best Answer

Related Solutions

Sql-server – Replacing composite key with surrogate

Sql-server – Composite field in SQL Server, does it exist

Related Question