Understanding the Identity column

Here is the original article.

A common problem encountered by most DBA's is to insert values into a table with an identity column and "set identity_insert on table_name on" should be used.

Three quick ways to show the last identity value that was just inserted:

select SCOPE_IDENTITY()

select @@IDENTITY

select ident_current('table_name')


The differences between them are stated in the above linked article.

Another important command for reseeding the identity column is:

DBCC CHECKIDENT('table_name', RESEED, 1)