CODEWITHSUNDEEP
sqlsql-server
Bhaskar
Bhaskar

Reputation: 4259

identity column in Sql server

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

Upvotes: 6

Views: 5747

Answers (8)

vinesh
vinesh

Reputation: 11

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.

Upvotes: 1

onedaywhen
onedaywhen

Reputation: 57023

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.

Upvotes: 2

Aaron Daniels
Aaron Daniels

Reputation: 9664

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.

Upvotes: -1

Stack Programmer
Stack Programmer

Reputation: 3484

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.

Upvotes: 2

Fabio Vinicius Binder
Fabio Vinicius Binder

Reputation: 13214

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.

Upvotes: 0

Paulo Santos
Paulo Santos

Reputation: 11567

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.

Upvotes: 2

Mehrdad Afshari
Mehrdad Afshari

Reputation: 421988

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.

Upvotes: 7

Justin Ethier
Justin Ethier

Reputation: 134157

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.

Upvotes: 0

Related Questions