Tanubhrt.This.Side

IDENTITY is used to create an automatically incrementing number for a column of a table.

A table can have only one column defined as the identity and it can be of any of the following data types –

•      Decimal
•     INT
•      SMALLINT
•      BIGINT
•     NUMBERIC
•      TINYINT

When using DECIMAL or NUMERIC data type, it has to be made sure that the precision is made 0 since IDENTITY can be made only on Integer values.

The IDENTITY property can be specified with the seed value (initial value) and increment value for generating the first and subsequent numbers. If the see and increment are not specified then it defaults to one i.e. the initial value will be 1 and the subsequent value will be incremented by 1 every time.

The IDENTITY column must not allow NULL values. When inserting the records into the table, all the values for each column must be specified except the one which has IDENTITY specified onto it.

However, we can also explicitly insert value on the IDENTITY column as –

SET IDENTITY_INSERT table_name ON

FUNCTIONS USED WITH IDENTITY

1. @@IDENTITY– It is a global variable which returns the last inserted identity value for the statement. If the statement fails, it returns NULL.
2. Ident_Current – It is a T-SQL function which returns the last inserted identity value for a table in any session. Select ident_current (table_name)

PRACTICAL

• When we need to see if the object is already existing –

IF OBJECT_ID (‘Customer’) IS NOT NULL

DROP TABLE Customer

The above query drops the Customer table if it is already existing.

• CREATING TABLE WITH IDENTITY

CREATE TABLE Customer (

CustomerNo INT IDENTITY(1001,1),

CustomerName VARCHAR(50)

);

Here the customer No is the column with the IDENTITY and the initial value will be 1001 for it which will then be incremented by 1.

• Now, when we are inserting values into the table, we need not to specify the customerno for each table.  The statement is as –

INSERT INTO Customer VALUES(‘Sam’);

INSERT INTO Customer VALUES (‘Mira’);

It inserts values Sam and Mira and for both of them, the customer no are as 1001 and 1002 respectively.

• Now, if I want to explicitly insert values into the table, I need to set IDENTITY_INSERT ON.

SET IDENTITY_INSERT Customer ON;

INSERT INTO Customer VALUES (‘1003’,’Tom’);

SET IDENTITY_INSERT Customer OFF;

Here, when we set identity_insert ON it allows us to enter our specific values. After insertion when we OFF it, it again goes back to its loop.

• Now, when I insert value the next person will get 1004.

INSERT INTO customer values(‘SAM’);

On execution of the above query, the person get ID 1004.

• Now, suppose I enter 1008 value –

SET IDENTITY_INSERT Customer ON;

INSERT INTO Customer VALUES (‘1008’,’Tom’);

SET IDENTITY_INSERT Customer OFF;

• Now, if I try to insert a value like

INSERT INTO customer values(‘SAM’);

I will get 1009

• Suppose after this, we try to insert a value with id 1005, this value does not gets inserted after 1009 but goes below 1004. This is because in IDENTITY, no matter what the sequence is maintained.

To get all the values from identity column we have –

SELECT IDENTITY COL FROM CUSTOMER;
Happy Learning 🙂