Identity in SQL Server

Identity

An identity column of a table is a column whose value increases automatically. The value in an identity column is crated by the server. A user generally cannot insert a value into an identity column.

Syntax

IDENTITY [ ( seed , increment ) ]

Arguments

Seed: Starting value of a column. Default value is 1.

Increment: Is the incremental value that is added to the identity value of the previous row that was loaded. The default value 1.

We can set the identity property to a column either when the table is created or after table creation. We use one example for each of the scenarios.

The following shows an Identity property when the is created:

CREATE TABLE EMPLOYEE
(
   IID INT IDENTITY(1,1),
   NAME [varchar](MAX) NOT NULL,
   AGE INT NOT NULL
)

The following shows an Identity column after the table has been created:

CREATE TABLE EMPLOYEE
(
   IID INT ,
   NAME [varchar](MAX) NOT NULL,
   AGE INT NOT NULL
)

ALTER TABLE EMPLOYEE
DROP COLUMN IID;

ALTER TABLE EMPLOYEE
ADD IID INT IDENTITY(1,1);

We can set an identity property for a column after table creation but for this first we should drop that column then create a new column with the Identity property.

Insert some value into a table:

/*INSERT DATA INTO TABLE*/
INSERT INTO EMPLOYEE VALUES ('PANKAJ', 20);
INSERT INTO EMPLOYEE VALUES ('RAHUL', 22);
INSERT INTO EMPLOYEE VALUES ('SANDEEP', 24);

/*SELECT DATA FROM TABLE*/
SELECT * FROM EMPLOYEE

Output



In the preceding example we have inserted data into the NAME and AGE fields, but the VALUE of the IID field is automatically inserted by the server due to the presence of the Identity property.

ALTER TABLE EMPLOYEE
DROP COLUMN IID;

ALTER TABLE EMPLOYEE
ADD IID INT IDENTITY(3,5);

SELECT * FROM EMPLOYEE

Output



In this example we dropped the previously defined identity and created a new identity property for the IID column. We set the value of the seed equal to 3 and the increment equal to 5. The first value of the IID column contains 3 and then each value will contain a value +5 compared to the previous value.

Insert value into Identity Column

Let us try to insert some values into an identity column.

INSERT INTO EMPLOYEE(IID,NAME,AGE) VALUES(10,'SANJEEV',35);
INSERT INTO EMPLOYEE(IID,NAME,AGE) VALUES(13,'NARU',36);

Output

Msg 8101, Level 16, State 1, Line 1
An explicit value for the identity column in table 'EMPLOYEE' can only be specified when a column list is used and IDENTITY_INSERT is ON.

When we execute the preceding insert query then the system throws an error that we can't insert a value into an Identity column.

Now I will tell you how to insert a value into an Identity column.

 

/* SET IDENTITY_INSERT TO ON */

SET IDENTITY_INSERT Employee ON

/*INSERT VALUE*/
  INSERT INTO EMPLOYEE(NAME,AGE,IID) VALUES('OMI',36,15);

/*SET IDENTITY_INSERT TO OFF*/

SET IDENTITY_INSERT Employee OFF

SELECT * FROM EMPLOYEE;

Output



IDENTITY_INSERT ON allows a user to insert data into an Identity column and IDENTITY_INSERT OFF restricts a user from inserting data into an Identity column.

Reseed the identity Column

We can also reseed the identity field value. By doing so the identity field values will start with a new defined value. This method checks the current identity value for the specified table in SQL Server and if necessary, changes the identity value. We use the DBCC CHECKIDENT method to manually set a new identity value for the identity column.

/*SET NEW VALUE TO IDENTITY COLUMN*/
DBCC checkident (Employee, RESEED, 20)

/*INSERT SOME DATA*/
INSERT INTO EMPLOYEE(NAME,AGE) VALUES('SANJEEV',35);
INSERT INTO EMPLOYEE(NAME,AGE) VALUES('NARU',36);

/*SELECT DATA FROM TABLE*/
SELECT * FROM EMPLOYEE

Output



In the preceding example we set the value of the seed arguments equal to 20. So the next value of the IID column will be 21.

Select last value of Identity Column

Sometimes we musr determine the last value of an identity column. For this SQL Server provides the following 3 methods:

1. @@IDENTITY
2. SCOPE_IDENTITY
3. IDENT_CURRENT

@@IDENTITY: Returns the last identity values that were generated in a table in the current session. This method is not limited to a specific scope.

SCOPE_IDENTITY: Returns the last identity values that are generated in any table in the current session. This method returns values inserted only within the current scope.



IDENT_CURRENT: Returns the last identity value generated for a specific table in any session and any scope. This method is not affected by scope and session, it only depends on a specific table.



The following is an example:

INSERT INTO EMPLOYEE(NAME,AGE) VALUES('OMI',25);

SELECT * FROM EMPLOYEE
SELECT @@IDENTITY AS 'VALUE','@@IDENTITY' AS 'METHOD' UNION ALL
SELECT SCOPE_IDENTITY() ,'SCOPE_IDENTITY' UNION ALL
SELECT IDENT_CURRENT('EMPLOYEE') ,'IDENT_CURRENT'

Output



Differences among @@IDENTITY , SCOPE_IDENTITY , IDENT_CURRENT: All these three methods are used to retrieve the last identity value generated for a specific table, but each method has some minor differences as in the following:

@@IDENTITY method is not limited to a specific scope.

SCOPE_IDENTITY method is limited for current scope.

IDENT_CURRENT method is not affected by scope and session, it only depends on a specific table.

Let us see an example for a better understanding.

First we create another table named EMPLOYEE1.

CREATE TABLE EMPLOYEE1
(
   IID INT IDENTITY(1,1),
   NAME [varchar](MAX) NOT NULL,
   AGE INT NOT NULL
)

Now we create an After Insert Trigger for this table.

CREATE TRIGGER MY_TRIGGER
ON EMPLOYEE1
AFTER INSERT
   AS
   BEGIN
   INSERT INTO EMPLOYEE(NAME, AGE) VALUES('KARAN',42);
END

We insert a value into this table and observe the output.

INSERT INTO EMPLOYEE1(NAME, AGE) VALUES('PANKAJ',1);
SELECT SCOPE_IDENTITY() AS VALUE , 'SCOPE_IDENTITY' AS TYPE_ UNION ALL
SELECT @@IDENTITY , '@@IDENTITY' UNION ALL
SELECT IDENT_CURRENT('EMPLOYEE') , 'IDENT_CURRENT FOR EMPLOYEE' UNION ALL
SELECT IDENT_CURRENT('EMPLOYEE1') , 'IDENT_CURRENT FOR EMPLOYEE1'

Output



Data of EMPLOYEE Table.



Data of EMPLOYEE1 table.



We can see that the SCOPE_IDENTITY method and the @@IDENTITY method return two different values. SCOPE_IDENTITY method returns the last Identity value for the EMPLOYEE1 table because this method is in the EMPLOYEE1 table's scope and the @@IDENTITY method returns the last identity value of the EMPLOYEE table because this method returns the last Identity value for the last inserted table.

Let us modify our trigger and measure the changes between this and the previous output.

ALTER TRIGGER MY_TRIGGER
ON EMPLOYEE1
AFTER INSERT
   AS
   BEGIN
   INSERT INTO EMPLOYEE1(NAME, AGE) VALUES('KARAN',42);
END

In this trigger we replace the name EMPLOYEE table name with the EMPLOYEE1 table. Now we will execute our query.

INSERT INTO EMPLOYEE1(NAME, AGE) VALUES('PANKAJ',1);
SELECT SCOPE_IDENTITY() AS VALUE , 'SCOPE_IDENTITY' AS TYPE_ UNION ALL
SELECT @@IDENTITY , '@@IDENTITY' UNION ALL
SELECT IDENT_CURRENT('EMPLOYEE') , 'IDENT_CURRENT FOR EMPLOYEE' UNION ALL
SELECT IDENT_CURRENT('EMPLOYEE1') , 'IDENT_CURRENT FOR EMPLOYEE1'

Output



The following is the data of the table EMPLOYEE:



The following is the data of the table EMPLOYEE1:



In this example @@IDENTITY and SCOPE_IDENTITY both method return the last identity value for the EMPLOYEE1 table.

IDENT_INCR: This method returns the increment value (returned as numeric (@@MAXPRECISION,0)) specified during the creation of an identity column in a table or view that has an identity column.

Syntax

IDENT_INCR ( 'table_or_view' )

Return Type: int



Example 1

SELECT IDENT_INCR('EMPLOYEE1') AS 'INCREMENT'

Output



Example 2

ALTER TABLE EMPLOYEE
DROP COLUMN IID;

ALTER TABLE EMPLOYEE
ADD IID INT IDENTITY(1,5);

SELECT IDENT_INCR ( 'EMPLOYEE' ) AS [INCREMENT]

Output



IDENT_SEED: This function returns the original seed value (returned as numeric(@@MAXPRECISION,0)) that was specified when an identity column in a table or a view was created. Changing the current value of an identity column using DBCC CHECKIDENT does not change the value returned by this function.

Syntax

IDENT_SEED ( 'table_or_view' )

Return Type : numeric


Example 1

SELECT IDENT_SEED('EMPLOYEE1') AS 'SEED'

Output



Example 2

DBCC CHECKIDENT('EMPLOYEE1', RESEED,50);
SELECT IDENT_SEED('EMPLOYEE1') AS 'SEED'

Output



I hope this article helps you in understanding the concept of Identity.