Identity Property in Sql server Tips

Identity Property of Sql server

We all of us aware of the Identity property of Sql server. This has been introduced from the earlier version of sqlserver (Sql Server-2000). This has been maintain the sequence of running number. Most of the time column having Identity property is a primary column for that table.

What Identity property do?

1. This property helps to create an identity column in a table. 
2. This property is used with the CREATE TABLE and ALTER TABLE Transact-SQL statements.
3. Use of this property is possible if the column has only below property.           tinyint,smallint,int,bigint,decimal and numeric

Syntax :
Identity[(seed),(Increment)]

Create table TestIdentity (
       ID int IDENTITY(100,1), /*Here seed=100, increment=1*/
       name varchar(20)
)

INSERT INTO TestIdentity (name) values ('Saurav');
INSERT INTO TestIdentity (name) values ('Pradeep');
INSERT INTO TestIdentity (name) values ('Aiswarya');
INSERT INTO TestIdentity (name) values ('Khalid');

INSERT INTO TestIdentity (name) values ('Usha');

Select * from TestIdentity 

Can we  inserting new row with identity column. The answer is 'Yes'.

We can insert identity column value by making IDENTITY_INSERT property on for that table

Syntax :  

SET IDENTITY_INSERT TestIdentity ON

INSERT INTO TestIdentity (id,name) values (10,'Sushma');

Select * from TestIdentity 

There are three different ways to find out Identity Property value as mention below.

@@Identity : It returns the last IDENTITY value produced on a connection, regardless of the table that produced the value, and regardless of the scope of the statement that produced the value.

SCOPE_IDENTITY()  : It returns the last IDENTITY value produced on a connection and by a statement in the same scope, regardless of the table that produced the value. 

SELECT IDENT_CURRENT(‘tablename’)  : It returns the last IDENTITY value produced in a table, regardless of the connection that created the value, and regardless of the scope of the statement that produced the value.

For more about the property please go through @@IDENTITY vs SCOPE_IDENTITY() vs IDENT_CURRENT article.

How to check column of a table is identity or not ? 

By using below statement we can find table has identity column or not

IF ((SELECT OBJECTPROPERTY( OBJECT_ID(N'TestIdentity'), 'TableHasIdentity')) = 1)
    PRINT 'Yes'
ELSE
    PRINT 'No'

Result : Yes
There are some different techniques are available by which we can find table name with their identity column names. Click here

Summary :
In  this post we have discussed uses Identity property tips. Please provide your valuable comments and share any other methods are there , which will be useful for all in the future.