Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I am following this SO post where the author (stack programmer) states

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.

He also mentioned that

Also if no identity columns are explicitly stated, Sql server internally stores a separate column which contains key value for each row.

Could anyone explain the two points(specially the second one) in more details?


share|improve this question

migrated from Apr 4 '13 at 8:52

This question came from our site for professional and enthusiast programmers.

up vote 6 down vote accepted

Could anyone explain the two points(specially the second one) in more details?

I hope not, since they are both wrong. In SQL Server rows are identified by keys (in B-trees) or by row ids (in heaps). Non-unique clustered indexes have an internal uniqueifier column. The fact that often times an IDENTITY type column is also used as a primary key is orthogonal to the question.

Do not base your knowledge on stuff from posts on forums, including StackOverflow, and including this very post. A good source is the official documentation, eg. Tables and Index Data Structures Architecture. Read a good book, like the SQL Server Internals series.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.