An Identity column is a column ( also known as a field ) in a database table that (1) uniquely identifies every row in the table, and (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. Because the concept is so important in database science, all RDBMS systems implement some type of generated key, although each has its own terminology. Deconstructing a Roman pillar. ... In computer science, data that has several parts can be divided into fields. ... This article does not cite any references or sources. ... Look up table in Wiktionary, the free dictionary. ... This article does not cite any references or sources. ... The term Oracle database may refer either to the database management system (DBMS) software released by Oracle Corporation as Oracle RDBMS, or to any of the individual databases managed by such software. ... This article does not cite any references or sources. ... Part of a scientific laboratory at the University of Cologne. ... A relational database management system (RDBMS) is a database management system (DBMS) that is based on the relational model as introduced by Edgar F. Codd. ...

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. In database design, a primary key is a value that can be used to identify a unique row in a table. ...

Two types of identity columns are available in SQL Server: incremental ( where the user can set a seed and an increment ) and random ( where the server chooses a random numeric value and ensures that it hasn't already been used ). Mostly it refers to the Microsoft SQL Server, which was actually derived from Sybase SQL Server ...

Code Samples

Create Table Contacts (

  FirstName varChar(30), LastName varChar(30), Phone varChar(16), ContactID identity(1, 1)  

)

or

Create Table Contacts (

  FirstName varChar(30), LastName varChar(30), Phone varChar(16)  

) GO Alter Table Contacts Add ContactID identity(1, 1)

Related Functions

It is often useful or necessary to know what identity value was generated by an INSERT command. SQL Server provides several functions to do this: @@Identity provides the last value generated on the current connection, while Scope_Identity(tablename) provides the last value generated, regardless of the connection it was created on. For the insert keyword in SQL language, see insert (SQL) For the Vancouver, Canada band, see Insert (Band) In film, an insert is a shot of part of a scene as filmed from a different angle and/or focal length from the master shot. ... Mostly it refers to the Microsoft SQL Server, which was actually derived from Sybase SQL Server ...

Example:

  Insert Into Contacts ( FirstName, LastName ) Values ( 'Test', 'User' ) -- Select @@Identity -- OR -- Declare @ID int Select @ID = @@Identity Update Contacts Set Phone = 'XXX-YYY-ZZZZ' Where ContactID = @ID  

External Links

• MSDN Article "Managing Identity"