Problem in creating two identity columns in a single table

I am having a problem in creating two identity columns in a single table. This is my part of job... They need two identity columns in a single table at any cost. Is there any way to do this.

Please provide any syntax for creating two identity columns in a single table at a time o开发者_JAVA技巧r later also.

Thanks in Advance, Shashra

In SQL Server, you can have a computed column which just holds the same value as an identity (or any other) column:

CREATE TABLE [dbo].[test](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [idCopy]  AS ([id]),
    [whatever] [nvarchar](max) NOT NULL
)

you can't have two identical columns in the same table. If you were to have two columns named id in table X, what would be the result of this query:

select id from X

obviously sql will have trouble with this. I think the best you can do is make another query with a similar name (like, Identity or ID2) and set every field of the new column to the vlue of the ID (original field) in that table

There can be only one identity column in a SQL Server table:

"There can only be one IDENTITY column per table" - Why?

If you gave us more information about what you are trying to achieve (and the expected behavior of the sequences) and why, it might be a little easier.

For instance, you could add a computed or persisted computed column which exposes the identity by another name.

You could make two columns which are foreign keys to two separate tables with individual identity columns and then also add a unique constraint on each of the foreign key columns.

Until we know more about what you are trying to achieve, it's hard to see what benefit you would get from having two identity columns.

People should calm down. He is probably wanting to increment with different seeds and different increment amounts different fields.

id1: 1
id2: 1

id1: 1
id2: 2

id1: 1
id2: 3

id1: 2
id2: 1

The answer is to use a composite key and set the value yourself somehow.

Maybe use an identity and a trigger based on your second id's incrementing requirement: http://msdn.microsoft.com/en-us/library/ms189799.aspx