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

Question

"There can only be one IDENTITY column per table"

Why is it so? Take a scenario of a vehicle, there exists a chasis number which is unique as well as the registration number which turns out to be unique. To depict this scenario in sql server we need a custom implementation for on of the columns. Conversely, in Oracle you can have as many sequences as you want on a table. Why is there a restriction on the IDENTITY Column, any specific reasons?

The scenario of having a vehicle schema is something imaginary am questioning myself as to why there's a restriction on the identity column.

Answer 1

Data is stored in the database, keyed by the IDENTITY column. A single such column allows for a filesystem-like storage. Having multiple IDENTITY columns would confuse the issue.

My recommendation is to choose one of your columns to be the IDENTITY or PRIMARY KEY, and for the other to be a UNIQUE KEY. As a user there will be no difference, but it will make the DBMS happy.

Answer 2

An Identity Column in SQL Server has a seed and an auto increment. We could always calculate what the 2nd hypothetical id value should be if we knew the value of the first id column anyway.

e.g. If this was legal syntax

create table #foo
(
bar int identity(1,10),
baz int identity(1000,1)
)

We wouldn't need to store baz as it could be calculated from bar as follows.

baz = 1000 + (bar-1)/10

Answer 3

Because it's would be the same value. if you had identity(1,1) as the primary, and identity(100,2), you would get these results:

1     100
2     102
3     104
4     106
5     108
6     110
7     112
8     114
9     116
10    118

you could get the second column by doing this:

((ID-1)*2)+100

it's a linear equation, so what would be the point other than for show?

Answer 4

• Oracle sequences are not SQL Server IDENTITY columns: you write some code for them. They don't work out of the box based on the CREATE TABLE DDL
• Any subsequent IDENTITY columns can be worked out from the first one (edit: as other folk mentioned)

Answer 5

It's a silly limitation I agree. It's not too difficult to work around it though. Just create a separate table with an IDENTITY column to use purely as a sequence generator. Insert into the sequence table, retrieve the value using SCOPE_IDENTITY() and then insert the value wherever you like. You can then support as many sequences as you need.