6

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

Chaitanya
  • 1,698
  • 5
  • 21
  • 41
  • I want to know why it is not possible. – Chaitanya Sep 27 '10 at 20:01
  • 2
    Because the engineers on the SQL Server team either couldn't do it, or didn't think it was a good idea to do it. – Randy Minder Sep 27 '10 at 20:02
  • 1
    PostgreSQL also [thankfully] has sequences, and I agree they are a better approach than IDENTITY (and MySQL's auto_increment for that matter). Why doesn't SQL Server support this? I'd figure it's a throwback to the Sybase code SQL Server was based on, but that whomever built that code never encountered the need. I admit, the need is very corner case. – OMG Ponies Sep 27 '10 at 20:06
  • Sounds like somebody is abdicating the implementation of requirements to SqlServer. –  Sep 27 '10 at 20:12
  • For your vehicle example, bear in mind that the **chassis number** will be an attribute of the vehicle, but the **registration** will be an attribute of the owner, which is an attribute of the vehicle. Normalization works! – JNK Sep 27 '10 at 20:14
  • @JNK: Yes normalization works, Suppose there's a User schema (with registration as the PK) and another details schema with Chasis number as the PK and the registration number as the FK. In a city lets suppose there are 100K vehicles and I need to store the information of all the vehicles, so i have a user schema and details schema each with 100K rows. then whats the cost of joins that will be incurred when normalization principle is followed? Will that not be heavy? – Chaitanya Sep 27 '10 at 20:22
  • 2
    @Chaitanya - It won't be terrible if your indexes are properly applied. Also bear in mind that you are precluding anyone from owning more than one vehicle in your design. Ideally the vehicle will have an ID, the owner will have an ID, and you will have more flexibility to model real-world situations. I guess this will depend on whether you want your data to fit the real world, or the real world to mold to your data model... – JNK Sep 27 '10 at 20:26
  • @JNK: Am a beginner in designing tables and with a lot of stuff around on designing schema's, am confused on how to look at the things. This caused me to ask the question, the table i'm designing falls very much into this scenario. I apologize if i have acted ignorantly & incoherently. The whole intention is to know the correct method[process] to go about designing schema's. – Chaitanya Sep 27 '10 at 20:30
  • @Chaitanya - no need to apologize, this is how people learn things. I am far from an expert but wanted to point out to you what I thought was an oversight in your analogy. – JNK Sep 27 '10 at 20:32

7 Answers7

9

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
Martin Smith
  • 438,706
  • 87
  • 741
  • 845
  • 1
    darn you for articulating what i was trying to say! – DForck42 Sep 27 '10 at 20:08
  • very late but, what if `bar` is guid and `baz` is int? – ibubi Nov 19 '18 at 07:59
  • @ibubi [`IDENTITY`](https://learn.microsoft.com/en-us/sql/t-sql/statements/create-table-transact-sql-identity-property?view=sql-server-2017) in SQL Server is always some sort of integer. You can't declare a guid column with `IDENTITY` property. – Martin Smith Nov 19 '18 at 11:36
6

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?

DForck42
  • 19,789
  • 13
  • 59
  • 84
  • IDENTITY values are not always assigned in sequence however. If you have two simultaneous inserts of multiple rows then the values can get interleaved between the two sets of rows. Also, specific values could be inserted so that the two columns aren't dependent on each other. Just a couple of reasons why you might want two IDENTITY columns. – nvogel Sep 29 '10 at 22:13
  • 1
    @dportas, sure, but in theory, if you had 2 identities, they would both be assigned at the same time, still giving you the same effect. – DForck42 Sep 30 '10 at 18:08
  • Excellent answer! – Kumar Vaibhav Apr 05 '18 at 01:58
2

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.

Kevin Lacquement
  • 5,057
  • 3
  • 25
  • 30
1

The reason isn't explained in MSDN that I could find. However, I suspect that it has to do with the way sql server implements the identity column. Conceptually, I think it's more a setting or value at the table level than at the column level and the column is just a way to access the value. I think this because all the methods for interacting with identity values have the table as a parameter and not the column eg. IDENT_SEED, IDENT_CURRENT, and IDENT_INCR. If it was a column level setting, the parameters would include a column name. If you had more than one identity column on the table, those functions wouldn't work.

I'm just speculating though.

ubergeek
  • 51
  • 2
1
  • 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)
gbn
  • 422,506
  • 82
  • 585
  • 676
0

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.

nvogel
  • 24,981
  • 1
  • 44
  • 82
0

You could use a computed column instead, starting at some value plus the id value:

CREATE TABLE dbo.TwoIdentity (id INT IDENTITY (1,1) NOT NULL, SecondID AS 100+id, TEXT VARCHAR(50) NULL)

enter image description here

J.C. Gras
  • 4,934
  • 1
  • 37
  • 44