Powered by Blogger.

Saturday, February 15, 2014

Identity in sql server 2008

In this post we will discuss about identity column and identity functions in sql server 2008. Also check out my previous posts on:

- Triggers in sql server 2008

- Transaction in SQL Server 2008

- Role of XAML in WPF

Identity is used to generate values automatically for a column during insert. While specifying identity you have to provide two values related to identity i.e, seed and increments. 'seed' is used to specify  the first value to be generated by the identity and 'increment' is used to specify how much increment to be done to the previous value of the identity to generate the new value.

Syntax:  identity (seed, increment)

Ex: identity (100,2)

Within a table you can specify identity only on one column. While inserting rows in to that table if there is identity column in the table, you must exclude the identity column and for this you must use the below insert command syntax.

The following example creates a table with the name Employee with identity on Empid column.

Create table Employee
(Empid int primary key identity(101,1),
Ename varchar(30) not null)

Identity Functions:
Sql server provides a set of functions  called Identity functions to get information about identity available on a table.

They are as follows:
1. Ident_seed("tablename")->Used to get seed value of identity available in the given table.
2. Ident_incr("tablename")-> Used to get increment value of identity available in the given table.
3. Ident_current("tablename")->Used to get current value of identity i.e, the last value generated by the identity.
4. @@identity->Used to get identity value generated by the last insert statement. If the last insert statement does not generate an identity value then this variable will contain null.

Inserting values for identity column explicitly
By default, you can’t provide value for identity column explicitly. If you want to insert values for identity column explicitly you need to set ‘identity _insert’ option ‘on’ for the identity table.
Syntax:  set identity _insert<table name> on/off

You can set this option to "on" for only one table at a time and when you set this option to ‘off’ after explicitly inserting values for identity column, then the last value you inserted for the identity column will automatically become current value of identity.

Specifying identity in GUI:
To specify identity on a column while creating the table in GUI ,first you need to click on the column, then go to column properties available at the bottom, expand the property "identity specifications", set the property 'is identity' to 'yes' and then specify identity seed and identity increment.