6

Why does Sql server doesn't allow more than one IDENTITY column in a table?? Any specific reasons.

Bhaskar
  • 4,189
  • 4
  • 26
  • 20
  • The accepted answer is wrong about the reason. Even if it allowed you to create two identity columns it would be pointless anyway though [as here](http://stackoverflow.com/a/3807400/73226) – Martin Smith Apr 04 '13 at 09:05

8 Answers8

7

Why would you need it? SQL Server keeps track of a single value (current identity value) for each table with IDENTITY column so it can have just one identity column per table.

Mehrdad Afshari
  • 414,610
  • 91
  • 852
  • 789
2

The SQL Server stores the identity in an internal table, using the id of the table as it's key. So it's impossible for the SQL Server to have more than one Identity column per table.

Paulo Santos
  • 11,285
  • 4
  • 39
  • 65
2

An Identity column is a column ( also known as a field ) in a database table that :-

  1. Uniquely identifies every row in the table
  2. Is made up of values generated by the database

This is much like an AutoNumber field in Microsoft Access or a sequence in Oracle.

An identity column differs from a primary key in that its values are managed by the server and ( except in rare cases ) can't be modified. In many cases an identity column is used as a primary key, however this is not always the case.

SQL server uses the identity column as the key value to refer to a particular row. So only a single identity column can be created. Also if no identity columns are explicitly stated, Sql server internally stores a separate column which contains key value for each row. As stated if you want more than one column to be having unique value, you can make use of UNIQUE keyword.

Stack Programmer
  • 3,386
  • 1
  • 20
  • 12
  • "An Identity column ... Uniquely identifies every row in the table" -- Really? Why then is this possible?: CREATE TABLE Test1 (col1 INTEGER IDENTITY) ; SET IDENTITY_INSERT Test1 ON ; INSERT INTO Test1 (col1) VALUES (1) ; INSERT INTO Test1 (col1) VALUES (1) ; SET IDENTITY_INSERT Test1 OFF ; SELECT col1 FROM Test1 – onedaywhen May 20 '09 at 20:24
  • 1
    ...so if SQL Server allows the IDENTITY column to contain duplicates, how then could it possibly be true that "SQL server uses the identity column as the key value to refer to a particular row"? – onedaywhen May 20 '09 at 20:25
  • Onedaywhen, your comments don't make sense. SQL Server doesn't allow an identity column to contain duplicates. – Lisa May 26 '11 at 02:15
  • 1
    @Lisa - Yes it does. This was demonstrated in the first comment. "if no identity columns are explicitly stated, Sql server internally stores a separate column which contains key value for each row". This is just plain wrong. I assume you are thinking of the uniqueifier added to non unique clustered indexes. A question was asked about this answer [here](http://dba.stackexchange.com/a/39216/3690) – Martin Smith Apr 04 '13 at 09:06
2

Because MS realized that better than 80% of users would only want one auto-increment column per table and the work-around to have a second (or more) is simple enough i.e. create an IDENTITY with seed = 1, increment = 1 then a calculated column multiplying the auto-generated value by a factor to change the increment and adding an offset to change the seed.

onedaywhen
  • 55,269
  • 12
  • 100
  • 138
1

Yes , Sequences allow more than one identity like columns in atable , but there are some issues here . In a typical development scenario i have seen developers manually inserting valid values in a column (which is suppose to be inserted through sequence) . Later on when a sequence try inserting value in to the table , it may fail due to unique key violation.

Also , in a multi developer / multi vendor scenario, developers might use the same sequence for more than one table (as sequences are not linked to tables) . This might lead to missing values in one of the table . ie tableA might get the value 1 while tableB might use value 2 and tableA will get 3. This means that tableA will have 1 and 3 (missing 2).

Apart from this , there is another scenario where you have a table which is truncated every day . Since Sequences are not having any link with table , the truncated table will continue to use the Seq.NextVal again (unless you manually reset the sequence) leading to missing values or even more dangerous arthmetic overflow error after sometime.

Owing to above reason , i feel that both Oracle sequences and SQL server identity column are good for their purposes. I would prefer oracle implementing the concept of Identity column and SQL Server implementing the sequence concept so that developers can implement either of the two as per their requirement.

vinesh
  • 11
  • 1
  • This question asked about identity columns, not sequence objects (which are now available starting in SQL Server 2012). – Jon Seigel Apr 04 '13 at 13:39
0

The whole purpose of an identity column is that it will contain a unique value for each row in the table. So why would you need more than one of them in any given table?

Perhaps you need to clarify your question, if you have a real need for more than one.

Justin Ethier
  • 131,333
  • 52
  • 229
  • 284
  • Really? Why then is this possible?: CREATE TABLE Test1 (col1 INTEGER IDENTITY) ; SET IDENTITY_INSERT Test1 ON ; INSERT INTO Test1 (col1) VALUES (1) ; INSERT INTO Test1 (col1) VALUES (1) ; SET IDENTITY_INSERT Test1 OFF ; SELECT col1 FROM Test1 ; – onedaywhen May 20 '09 at 20:23
0

An identity column is used to uniquely identify a single row of a table. If you want other columns to be unique, you can create a UNIQUE index for each "identity" column that you may need.

Fabio Vinicius Binder
  • 13,024
  • 4
  • 34
  • 33
  • Really? Why then is this possible?: CREATE TABLE Test1 (col1 INTEGER IDENTITY) ; SET IDENTITY_INSERT Test1 ON ; INSERT INTO Test1 (col1) VALUES (1) ; INSERT INTO Test1 (col1) VALUES (1) ; SET IDENTITY_INSERT Test1 OFF ; SELECT col1 FROM Test1 – onedaywhen May 20 '09 at 20:23
-1

I've always seen this as an arbitrary and bad limitation for SQL Server. Yes, you only want one identity column to actually identify a row, but there are valid reasons why you would want the database to auto-generate a number for more than one field in the database.

That's the nice thing about sequences in Oracle. They're not tied to a table. You can use several different sequences to populate as many fields as you like in the same table. You could also have more than one table share the same sequence, although that's probably a really bad decision. But the point is you could. It's more granular and gives you more flexibility.

The bad thing about sequences is that you have to write code to actually increment them, whether it's in your insert statement or in an on-insert trigger on the table. The nice thing about SQL Server identity is that all you have to do is change a property or add a keyword to your table creation and you're done.

Aaron Daniels
  • 9,563
  • 6
  • 45
  • 58
  • Uhh, this is two totally different things. On is the concept of identity, which MAY be managed by a generated sequence. And the other is a generated sequence. Complaining about one does not merit bad feelings toward the other. – Mark Canlas May 20 '09 at 21:55
  • 99.9% of the time, a SQL Server IDENTITY column and an Oracle sequence are used for the same purpose: as a database generated key to provide uniqueness to a row. However, you can use an Oracle sequence in other ways that you cannot use an IDENTITY column. I was pointing out that other database vendors recognize the need to have the ability to auto-generate values for more than one field in a table, and SQL Server does not. The author of the question seemed perplexed and perturbed at this, and I was agreeing with that sentiment. – Aaron Daniels May 20 '09 at 22:11