Hi, Why does Sql server doesn't allow more than one IDENTITY column in a table?? Any specific reasons.
+1
A:
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
2009-05-20 19:44:22
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
2009-05-20 20:23:00
+7
A:
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
2009-05-20 19:45:40
+1
A:
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.
fbinder
2009-05-20 19:46:16
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
2009-05-20 20:23:43
+2
A:
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
2009-05-20 19:46:24
+2
A:
An Identity column is a column ( also known as a field ) in a database table that :-
- Uniquely identifies every row in the table
- 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
2009-05-20 19:52:55
"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
2009-05-20 20:24:43
...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
2009-05-20 20:25:26
A:
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
2009-05-20 20:32:11
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
2009-05-20 21:55:35
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
2009-05-20 22:11:58
A:
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
2009-05-21 07:25:38