SQLServerLog.com

SQL IDENTITY Column – Auto Increment Column Values

by · Published · Updated

SQL IDENTITY column generates a unique number when a new record is inserted in a table and it is normally used with CREATE TABLE or ALTER TABLE statement.

Syntax

Following create table statement generates unique integer identity values on RollNumber column. 

CREATE TABLE Students
( 
RollNumber int IDENTITY(1,1) PRIMARY KEY,
Title varchar(8),
FirstName varchar(50) NOT NULL,
MiddleName varchar(50),
LastName varchar(50),
Country varchar(50)
)

IDENTITY is the keyword which is used to increment values for each records inserted in Students table. In above example IDENTITY(1,1) generates values starting with one and increments it by one. So for each and every rows RollNumber column will be unique.

Similarly, if you want to start the value with 1000 and increment it by 5 then you can write it as IDENTITY(1000,5) where 1000 is seed value and 5 is increment value.

Now, we will try to insert couple of records in Students table and check the IDENTITY column.

INSERT INTO Students 
VALUES ('Mr.','Praful','T','Patel','INDIA')
GO

INSERT INTO Students 
VALUES ('Mr.','Johny','H','Lever','GERMANY')
GO

Here, we don’t need to mention value for RollNumber column as it is defined as IDENTITY so it will be generated automatically.

We will see the output by selecting table.

SELECT *FROM Students

And here is the output, just observe RollNumber column, which is auto generating and getting incremented with each new record.

IDENTITY

Reference: Manzoor Siddiqui [www.SQLServerLog.com]

You may also like

You may also like...

Leave a Reply