"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.
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