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.
CREATE TABLE TABLE_NAME ( column1 datatype IDENTITY(1,1) PRIMARY KEY, column2 datatype, .... columnN datatype );
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.
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