Identity columns in SQL Server (identity column in SQL server)

If you set a column to identity when you create a new table, you can not display the specified value when inserting the data, and SQL Server automatically populates the value of that column.

Create Table TblPerson1 (    IntIdentity(1,1Primarykey  not Null,    Nvarchar(null)

At this point, the following code can be used to insert data into the table. Although the Tblperson table has two columns, only the value of the specified name is used when inserting it, because the property of PersonID is identity, and SQL Server automatically calculates the value when inserting data (the starting value is 1 and the increment is 1).

Insert  into TblPerson1 Values ('John')

However, it is important to note that if we display a value that is not allowed for Tblperson PersonID, the following code will give an error:

Insert  into TblPerson1 Values (2,'marttin')

MSG 8101, Level A, State 1, line 1
An explicit value for the identity, column in table ' TblPerson1 ' can, specified when a column list is used and IDENT Ity_insert is on.

This means that if you are setting a value for the Indentity column to be displayed, you must specify the column name in the INSERT statement and set the Identity_insert to ON.

Based on the error message, we will first use the following statement to set the IDENTITY_INSERT to the on state:

Set Identity_insert  on

The listed column names are then displayed in the INSERT statement:

Insert  into TblPerson1 (personid,name) Values (2,'marttin')

This personid the record for 2,name as "Marttin" was successfully inserted.

But then there is a problem, when we do not want to show the value of providing the PersonID column will be an error:

Insert  into TblPerson1 Values ('marttin')

MSG 545, Level A, State 1, line 1
Explicit value must be specified for identity column in table ' TblPerson1 ' either when identity_insert are set to ON or whe n A replication user is inserting to a not FOR replication identity column.

At this point we are going to set IDENTITY_INSERT to OFF:

Set Identity_insert OFF

This will allow you to go back to the beginning of the situation.

Now there is a scenario where we empty the data in the table and then insert the data into the table, what is the value of the PersonID? As in the following code:

Delete  from TblPerson1 Insert  into TblPerson1 Values ('Bob')

To query the database, we know that the values in the Tblperson table are:

PersonId Name
4 Bob

If we want PersonID to start counting again, that is, the newly inserted value has a personid of 1, you can use the DBCC CHECKIDENT command:

DBCC Checkident ('tblPerson1', reseed,0)

Checking Identity Information:current Identity value ' 4 ', current column value ' 0 '.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

The above statement means that the value of the identity is changed from 4 to 0, and if a new record is inserted at this point, then the value of PersonID will be 1.

INSERT  into TblPerson1 VALUES ('SARA')

Query Result:

PersonId Name
1 SARA

Identity columns in SQL Server (identity column in SQL server)