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
