A column or pillar in architecture and structural engineering is a vertical structural element that transmits, through compression, the weight of the structure above to other structural elements below. For the purpose of wind or earthquake engineering, columns may be designed to resist lateral forces...
In computer science, data that has several parts can be divided into fields. Relational databases arrange data as sets of database records, also called rows. Each record consists of several fields; the fields of all records form the columns....
A database is an organized collection of data for one or more purposes, usually in digital form. The data are typically organized to model relevant aspects of reality , in a way that supports processes requiring this information...
In relational databases and flat file databases, a table is a set of data elements that is organized using a model of vertical columns and horizontal rows. A table has a specified number of columns, but can have any number of rows...
that is made up of values generated by the database. This is much like an AutoNumber
AutoNumber
AutoNumber is a type of data used in Microsoft Access tables to generate an automatically incremented numeric counter. It may be used to create an identity column which uniquely identifies each record in a table...
Microsoft Office Access, previously known as Microsoft Access, is a relational database management system from Microsoft that combines the relational Microsoft Jet Database Engine with a graphical user interface and software-development tools. It is a member of the Microsoft Office suite of...
A database is an organized collection of data for one or more purposes, usually in digital form. The data are typically organized to model relevant aspects of reality , in a way that supports processes requiring this information...
Science is a systematic enterprise that builds and organizes knowledge in the form of testable explanations and predictions about the universe...
, many RDBMS systems implement some type of generated key, although each has its own terminology.
An identity column differs from a primary key in that its values are managed by the server and usually cannot be modified. In many cases an identity column is used as a primary key, however this is not always the case.
It is a common misconception that an identity column will enforce uniqueness, however this is not the case. If you want to enforce uniqueness on the column you must include the appropriate constraint too.
Microsoft SQL Server is a relational database server, developed by Microsoft: It is a software product whose primary function is to store and retrieve data as requested by other software applications, be it those on the same computer or those running on another computer across a network...
you have options for both the seed (starting value) and the increment. By default the seed and increment are both 1.
Create Table Contacts (
FirstName varChar(30),
LastName varChar(30),
Phone varChar(16)
)
GO
Alter Table Contacts Add ContactID int identity(1, 1)
Related Functions
It is often useful or necessary to know what identity value was generated by an INSERT
Insert
Insert may refer to:*Insert *Insert *Insert *Insert *Insert key on a computer keyboard, used to switch between insert mode and overstrike mode*Insert *Insert *Fireplace insert...
Microsoft SQL Server is a relational database server, developed by Microsoft: It is a software product whose primary function is to store and retrieve data as requested by other software applications, be it those on the same computer or those running on another computer across a network...
provides several functions to do this: @@IDENTITY provides the last value generated on the current connection in the current scope, while IDENT_CURRENT(tablename) provides the last value generated, regardless of the connection or scope it was created on.
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
