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
OutputIn 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
OutputIn 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 ColumnLet 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;
OutputIDENTITY_INSERT ON allows a user to insert data into an Identity column and I
DENTITY_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
OutputIn 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:
- @@IDENTITY
- SCOPE_IDENTITY
- 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'
OutputDifferences 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'
OutputData 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'
OutputThe 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'
OutputExample 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'
OutputExample 2
- DBCC CHECKIDENT('EMPLOYEE1', RESEED,50);
- SELECT IDENT_SEED('EMPLOYEE1') AS 'SEED'
OutputI hope this article helps you in understanding the concept of Identity.