How To Create Auto Increment Field Using Query In SQL

In this article, we will learn how to create an auto-increment field/column using a query in SQL.

The IDENTITY keyword is used to perform an auto-increment feature in MS SQL Server.

The auto-increment allows a unique number to be generated automatically when a new record is inserted into a table.

Often this is the primary key field that we would like to be created automatically, whenever a new record is inserted.

Syntax

CREATE TABLE TABLE_NAME (
    column1 datatype IDENTITY(1,1) PRIMARY KEY,
    column2 datatype,
    ....
    columnN datatype
);

Example

The subsequent statement would define the “ID” column as an auto-increment primary key field in the “Article” table:

CREATE TABLE Article (
    ID int IDENTITY(1,1) PRIMARY KEY,
    Title varchar(100)
);

Here, The starting value for IDENTITY is 1, and it will increment by 1 for each new record. To specify that the “ID” column should start at value 100 and increment by 2, change it to IDENTITY(100,2).

We will not have to specify a value for the “ID” column, to insert a new record into the “Article” table:

Please check How To Write Insert Query In SQL, to get a brief description of the INSERT INTO statement.

Example (INSERT)

The subsequent statement would insert a new record into the “Article” table with a unique value in the “ID” column:

INSERT INTO Article (Title) 
VALUES ('Introduction');

--OR--

INSERT INTO Article VALUES ('Introduction');

 

Also, check How To Create Index Using Query In SQL