Can we have more than one identity columns in a table

Question

Can we have more than one identity columns in a table

diva.mayas

Mr or Mrs. 500

Points: 569
More actions
June 29, 2010 at 11:57 pm

#223349

Hi
Can we have more than one identity columns in a table.
if yes then why, if no then why
i am interested to know the reasons behind it. any techie can share their stuffs. pls it would highly appreciable ..thanks

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

From Books Online:

When you use the IDENTITY property to define an identifier column, consider the following:
* A table can have only one column defined with the IDENTITY property, and that column must be defined by using a decimal, int, numeric, smallint, bigint, or tinyint data type.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

parthenitin Ten Centuries Points: 1190 More actions · Answer 2

Identity is the column having property to uniquely identify the row.

If you are going to make two identity columns then what sense it makes.

Nothing.

even we cant declare the two columns as identity in one table

it unnecessarialy increases space to store that perticular extra column.

So its just rowNo we can say & it must be only one in a table.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

diva.mayas (6/29/2010)
Hi
Can we have more than one identity columns in a table.
if yes then why, if no then why
i am interested to know the reasons behind it. any techie can share their stuffs. pls it would highly appreciable ..thanks

What is an identity column? What does it identify?

A car can have more than one identity value, the chassis number and the registration number. The chassis number distinguishes the car from others made at the factory (close enough, it'll do). The registration number distinguishes the car from others on the road.

How many attributes of identity does a row in a table need in order to distinguish it from another row in the same table?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

diva.mayas Mr or Mrs. 500 Points: 569 More actions · Answer 4

Thank you, its clear now why a table of a column can contain only one IDENTITY property.

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 5

diva.mayas (6/30/2010)
Thank you, its clear now why a table of a column can contain only one IDENTITY property.

Actually, it is down to MS SQLServer implementation of AUTO INCREMENTING.

You can find that in Oracle there is no limitation on how many columns can be populated using different SEQUENCES. It would be cool to have Oracle like SEQUENCE functionality in SQL (together with all functions currently available for IDENTITY).

For now, in SQL Server, if, for any reason, your need to have multiple incremental columns in the same table, you will need to have custom implementation of incrementation :w00t:, they can not be AUTO INCREMENTAL.:hehe:

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 6

As a "workaround". You can always use row_number() in your queries to get unique values, especially usefull if you need to have the identity reseed for groups