Identity Columns

The syntax of the identity property is the following:

IDENTITY[(seed ,increment)]

Note: If you do not indicate seed and increment options, the default value for each of them will be 1. So IDENTITY(1,1) and IDENTITY is the same.

When the column is created by this property , numeric sequence will be created for you and this column will hold the sequential values, started by seed and incremented by increment parameter values.

For example:

–Create table by this property

CREATE TABLE [ADUsersTable] (
[UserId]     [int] IDENTITY (1, 1) NOT NULL PRIMARY KEY,
[Username]   [nvarchar] (1000),
[FullName]   [nvarchar] (1000),
[Department] [nvarchar] (1000)
)

–Insert rows

INSERT INTO [ADUsersTable] ([Username], [FullName], [Department])
VALUES('MKUPATADZE' ,'MARIAM KUPATADZE','IT')

INSERT INTO [ADUsersTable] ([Username], [FullName], [Department])
VALUES('GBERIDZE' ,'GIORGI BERIDZE','IT')

–Query data

SELECT *
FROM [ADUsersTable]

–Result

USERID|USERNAME     |FULLNAME          |DEPARTMENT
 1    | MKUPTADZE   | MARIAM KUPATADZE | IT
 2    | GBERIDZE    | GIORGI BERIDZE   | IT

–Insert another row and rollback it, let’s see what happens

BEGIN TRAN

INSERT INTO [ADUsersTable] ([Username], [FullName], [Department])
VALUES('AGANDILIANI' ,'ANNA GANDILIANI','IT')

ROLLBACK TRAN

–Insert one more row

INSERT INTO [ADUsersTable] ([Username], [FullName], [Department])
VALUES('SCHELISHVILI' ,'SOPHO CHELISHVILI','IT')

–Query data

SELECT *
FROM [ADUsersTable]

–Result

USERID|USERNAME     |FULLNAME          |DEPARTMENT
 1    | MKUPTADZE   | MARIAM KUPATADZE | IT
 2    | GBERIDZE    | GIORGI BERIDZE   | IT
 4    | SCHELISHVILI| SOPHO CHELISHVILI| IT

As you can see rolling back the transaction does not reset the current value of the sequence.

–To see the current value of the sequence, run the following:

DBCC checkident(ADUsersTable)

—My result

Checking identity information:current identity value '4',current column value '4'.

Inserting desired identity values

By default, you can’t indicate value during insert for the column which was created by IDENTITY property. If you try the following command:

INSERT ADUsersTable (UserId,Username,FullName,Department)
VALUES(3,'VDALAKISHVILI','VASIL DALAKISHVILI','IT')

–It will arise the following error

Cannot insert explicit value for identity column in table 'ADUsersTable'
when IDENTITY_INSERT is set to OFF.

–To solve this, set IDENTITY_INSERT to ON, insert rows and then set IDENTITY_INSERT to OFF

SET IDENTITY_INSERT ADUsersTable ON

INSERT ADUsersTable (UserId,Username,FullName,Department)
VALUES(3,'VDALAKISHVILI','VASIL DALAKISHVILI','IT')

SET IDENTITY_INSERT ADUsersTable OFF

Note: If you do not turn off IDENTITY_INSERT, then you will not be able to use the generated sequence. Every time you run the INSERT statement you will be forced to indicate value for USERID column.

–Query data

SELECT * FROM ADUsersTable

–Result

USERID|USERNAME      |FULLNAME           |DEPARTMENT
 1    | MKUPTADZE    | MARIAM KUPATADZE  | IT
 2    | GBERIDZE     | GIORGI BERIDZE    | IT
 3    | VDALAKISHVILI| VASIL DALAKISHVILI| IT
 4    | SCHELISHVILI | SOPHO CHELISHVILI | IT

–To see the current value of the sequence , run the following:

DBCC checkident(ADUsersTable)

–The result

Checking identity information:current identity value '4',current column value '4'.

Note: If the desired value is less then the current value of the sequence, the current value will not be affected. As in our case, we inserted value 3, the current value was 4, that’s why current value was not affected.

But if the desired value is more then the current value of the sequence, current value will be affected.

For example:

–Repeat the previous steps, but now let’s insert higher value than the current one

SET IDENTITY_INSERT ADUsersTable ON

INSERT ADUsersTable (UserId,Username,FullName,Department)
VALUES(7,'TMAISURADZE','TEMUR MAISURADZE','IT')

SET IDENTITY_INSERT ADUsersTable OFF

–Query data

SELECT * FROM ADUsersTable

–Result

USERID|USERNAME      |FULLNAME           |DEPARTMENT
 1    | MKUPTADZE    | MARIAM KUPATADZE  | IT
 2    | GBERIDZE     | GIORGI BERIDZE    | IT
 3    | VDALAKISHVILI| VASIL DALAKISHVILI| IT
 4    | SCHELISHVILI | SOPHO CHELISHVILI | IT
 7    | TMAISURADZE  | TEMUR MAISURADZE  | IT

–To see the current value of the sequence , run the following:

DBCC checkident(ADUsersTable)

–The result

Checking identity information:current identity value '7',current column value '7'.

That’s all. 🙂