Identity column

<h2 id="code-samples">Code samples</h2>
Create Table Contacts (
    FirstName varChar(30),
    LastName  varChar(30),
    Phone     varChar(16),
    ContactID int identity(1, 1)
)

<p>or
</p>
Create Table Contacts (
    FirstName varChar(30),
    LastName  varChar(30),
    Phone     varChar(16)
)
GO
Alter Table Contacts Add ContactID int identity(1, 1)

<p>In <a href="/facts/PostgreSQL/LHdXuHfl">PostgreSQL</a>
</p>
CREATE TABLE contact
(
     contact_id bigint GENERATED ALWAYS AS IDENTITY,
     first_name varchar,
     last_name varchar,
     phone varchar 
);

<h2 id="related-functions">Related functions</h2>
<p>It is often useful or necessary to know what identity value was generated by an <a href="/facts/INSERT/UvooNUmg">INSERT</a> command.  <a href="/facts/Microsoft_SQL_Server/eCdt2JBL">Microsoft SQL Server</a> provides several functions to do this:  @@IDENTITY provides the last value generated on the current connection in the current scope, while IDENT_CURRENT(<i>tablename</i>) provides the last value generated, regardless of the connection or scope it was created on.
</p><p>Example:
</p>
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

<h2 id="see-also">See also</h2>
<ul><li><a href="/facts/Surrogate_key/rJu9oHHX">Surrogate key</a></li>
<li><a href="/facts/Unique_key/NKGBxUHR">Unique key</a></li></ul>
<h2 id="external-links">External links</h2>
<ul><li><a href="http://msdn.microsoft.com/library/default.asp?url=/library/en-us/replsql/repldata_078z.asp">MSDN Article "Managing Identity"</a> [<i>dead link</i>]</li></ul>

Identity column open-in-new

Identity column