Problem in creating two identity columns in a single table

Question

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 or later also.

Thanks in Advance, Shashra

You cannot have two IDENTITY columns in a SQL Server table, at any cost. — Randy Minder, Dec 20 '10 at 16:18
huh? can you explain this better? also, decide if you mean 'identity' or 'identical'. What would really be good to know is WHY they want these two columns — jon_darkstar, Dec 20 '10 at 16:18
Your question makes no sense, and why is it tagged mysql and sqlserver? — Jamiec, Dec 20 '10 at 16:19
@Shashra: what is the other guy's part of job? Is it possible to swap? — Quassnoi, Dec 20 '10 at 16:22
You'll need to share the requirement. Must the values be integer? Must the two "identities" be distinct from each other in the same row? In the same table? — D'Arcy Rittich, Dec 20 '10 at 16:23
You can fake it by creating one identity column and one calculated column pointing to that identity column. It would be utterly useless but it would satisfy any weird requirement from your stakeholders. — Lieven Keersmaekers, Dec 20 '10 at 16:25
Tell them it will cost a very, VERY large sum of money and take a very VERY long time. It took a large team of developers at Microsoft several years and several million dollars to create SQL server with _one_ Identity Column per table - It will probably take you longer and cost more to build another SQL Server that can do two identify columns per table. (You'll have a hard time finding and motivating competent developers to build a product that does this) ----- Or you could just tell them there _are_ two, but that they are so identical you can't tell the difference between them... — Charles Bretana, Dec 20 '10 at 16:26
@Lieven: Don't encourage him — you must fight crazy requirements, not satisfy them :) — D'Arcy Rittich, Dec 20 '10 at 16:27
@Charles: I'd estimate the cost as about `$15M` per identity column per table. — Quassnoi, Dec 20 '10 at 16:28
Two identity columns makes no sense - they'd be the same. There's requirement data missing from the question and we can't offer a rational response 'til we understand the requirement. — Murph, Dec 20 '10 at 16:30
@RedFilter: don't you *ever* think - `sure, whatever makes you happy` :) — Lieven Keersmaekers, Dec 20 '10 at 16:33
@Quassnoi: I'll do it for half that. I am working on a table-valued CLR function for this as we speak... — D'Arcy Rittich, Dec 20 '10 at 16:34
Two identity columns is like when your amplifier *goes to 11!* It's one better! — D'Arcy Rittich, Dec 20 '10 at 16:35
Job Description: *"Wanted: one Super DBA. Must be able to create two identity columns in a single table!"* — D'Arcy Rittich, Dec 20 '10 at 16:37
@RedFilter: now you see why you can't do it at a lower cost? — Quassnoi, Dec 20 '10 at 16:38

score 5 · Accepted Answer · answered Dec 20 '10 at 16:23

5

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
)

answered Dec 20 '10 at 16:23

Jamiec

133,658
13
134
193

1

But this is pointless...you could just select the same column again with an alias. And that way it would benefit from the index on the identity column. – D'Arcy Rittich Dec 20 '10 at 16:26
3

@RedFilter - I didnt make any comment as to its usefulness, I just answered the question. – Jamiec Dec 20 '10 at 16:28
@RedFilter, it may be pointless, but it does answer the OP's question. It would make sense to ask the OP WHY this needs to be accomplished. – Ramy Dec 20 '10 at 16:29
@Jamiec: When you provide an answer, I feel it is implied that it is a reasonable thing to do unless you explicitly state otherwise. – D'Arcy Rittich Dec 20 '10 at 16:31
@Ramy: it does not answer the question as that does not create two identities. The WHY has been asked already... – D'Arcy Rittich Dec 20 '10 at 16:32
THANKS a lot...really appreciate for everything. – Shahsra Dec 20 '10 at 16:59

score 1 · Answer 2 · answered Dec 20 '10 at 16:20

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

score 0 · Answer 3 · edited May 23 '17 at 12:11

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.

score 0 · Answer 4 · edited Nov 30 '12 at 12:37

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

Problem in creating two identity columns in a single table

4 Answers4

Linked