Can a sql server table have two identity columns?

sql sql-server identity-column
William Hurst picture William Hurst · Dec 8, 2008 · Viewed 57.4k times · Source

I need to have one column as the primary key and another to auto increment an order number field. Is this possible?

EDIT: I think I'll just use a composite number as the order number. Thanks anyways.

Answer

Eugene Yokota picture Eugene Yokota · Dec 8, 2008 
CREATE TABLE [dbo].[Foo](
    [FooId] [int] IDENTITY(1,1) NOT NULL,
    [BarId] [int] IDENTITY(1,1) NOT NULL
)

returns

Msg 2744, Level 16, State 2, Line 1
Multiple identity columns specified for table 'Foo'. Only one identity column per table is allowed.

So, no, you can't have two identity columns. You can of course make the primary key not auto increment (identity).

Edit: msdn:CREATE TABLE (Transact-SQL) and CREATE TABLE (SQL Server 2000):

Only one identity column can be created per table.

Related questions

Adding an identity to an existing column

I need to change the primary key of a table to an identity column, and there's already a number of rows in table. I've got a script to clean up the IDs to ensure they're sequential starting at 1, works fine …

Read More
Identity increment is jumping in SQL Server database

In one of my tables Fee in column "ReceiptNo" in SQL Server 2012 database identity increment suddenly started jumping to 100s instead of 1 depending on the following two things. if it is 1205446 it is jumps to 1206306, if it is 1206321, it jumps …

Read More
How to get list of all tables that has identity columns

I would like to learn how to fetch list of all tables that has identity columns from a MS SQL database.

Read More