Autoincrementation in SQL Server with IDENTITY

Auto increment in SQL SERVER is very common feature and can be implemented in two ways by IDENTITY and SEQUENCE. Today I would like to write few things about IDENTITY.

First and very known implementation of auto increment is IDENTITY clause that is a part of CREATE TABLE sentence. Basic syntax looks like this:

And now every time we insert something into this table, automatically appropriate value will be inserted into id column. Identity has two optional parameters seed and increment value. Seed is the starting value ( value for the first row) and increment is as a name said – value that will be added to the value from previous row. For example if we specify something like this:

Inserted values will be:

1,6,11 and so on

Great! But what if we need to insert value explicitly into identity column? You can do that but first you need to set appropriate setting:

If you turn IDENTITY_INSERT you can explicitly insert value into identity column.

Sometimes you need to get latest inserted identity value – you can get this information in three ways: read built-in variable:

or

or

What’s the difference? @@Identity returns latest identity value from all tables in every context in the database and IDENT_CURRENT returns last identity value inserted into specific table. SCOPE_IDENTITY() returns identity value in the current context. So what is context? For example if we’ve got AFTER TRIGGER that insert values into tableB after something is inserted into tableA (both tables has identity column) – @@IDENTITY returns latest identity value (from tableB) and SCOPE_IDENTITY() will return identity value from current context (from tableA).

There is also another option to get information about identity it’s

if we run this we will get something like this:

Checking identity information: current identity value ‘1’, current column value ‘1’.

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

But DBCC CHECKIDENT has some additional very useful features, for example we can RESEED identity in our table, for example

dbcc checkident(table_name,RESEED, 10) after that if we insert something into table, inserted value into column with identity will be 11. There is also third option that CHECKIDENT has: WITH NO_INFOMSGS

when we use it, there will not be any text after execution.

TSQL give us also opportunity to check identity settings in our table, we can accomplish that with: IDENT_SEED(‘table_name’),IDENT_INCR(‘table_name’) these two functions return SEED and INCREMENT – both can be very useful in our stored procedures or functions.

But there is also another question that we need to answer? What if I want to insert something into table with identity? First option Is to skip the name of a column in INSERT statement for example

Where test table has also id that has identity specified.

That’s all for now, in next post I will write few words about SEQUENCE object that is very similar to the identity but could be more flexible, see you soon!

Leave a Comment

Your email address will not be published. Required fields are marked *