What is IDENTITY value and how does it work?

An IDENTITY is a system function that returns the last-inserted identity value.

Let’s try it out in SQL Server Management Studio. Let’s create a table called Person with it’s IDENTITY starting position set to 1 with an increment value of 1 as well.

CREATE TABLE PERSON
(
	ID INT IDENTITY(1,1) PRIMARY KEY,
	FirstName VARCHAR(25),
	LastName VARCHAR(25),
	Gender CHAR(1)
)

Let’s insert three records and view them

INSERT INTO PERSON VALUES ('Bill', 'Meyer', 'M')
INSERT INTO PERSON VALUES ('Lisa', 'Ray', 'F')
INSERT INTO PERSON VALUES ('Scott', 'Johnson', 'M')
SELECT * FROM PERSON

As you can see, the first record has a starting ID of 1 and is incremented by 1.

Now let’s try and create another table and modify its IDENTITY settings a little bit:

CREATE TABLE PEOPLE
(
	ID INT IDENTITY(1000,10) PRIMARY KEY,
	FirstName VARCHAR(25),
	LastName VARCHAR(25),
	Gender CHAR(1)
)

Notice the IDENTITY now starts a 1000 with an increment value of 10.

Let’s insert three records into the PEOPLE table.

INSERT INTO PEOPLE VALUES ('Bill', 'Meyer', 'M')
INSERT INTO PEOPLE VALUES ('Lisa', 'Ray', 'F')
INSERT INTO PEOPLE VALUES ('Scott', 'Johnson', 'M')
SELECT * FROM PEOPLE

To view the last ID that was inserted into the PEOPLE table, you can do so by issuing the following query: