How to Do Auto Increment in MS SQL

Large amounts of data can be logically stored in databases. Every new record that is added after an existing record requires a unique number.

We are aware that manually entering the numbers is not possible. To automatically enter a unique number for each new record in the table, you can instead use Auto Increment in SQL.

We'll demonstrate how to produce new numbers after adding a record using MS SQL's Auto Increment features.

What is Auto Increment in SQL?

For each record you enter into a SQL table, the auto-increment functionality is configured to a column to enable it to create and deliver a distinctive number automatically.

This is frequently employed for the primary key column because it makes it simple for coders to automatically produce a unique number for each new record.

Auto Increment in MS SQL

With MS SQL, you can choose a column whose value is automatically increased for each new record added to the table. This is frequently applied to primary keys, which are used to uniquely identify each row in a table.

You can make a column that automatically increments by using the IDENTITY keyword. An illustration of how to make a table with an auto-increment primary key is given below:

Syntax:

CREATE TABLE TableName (
	Column1 DataType IDENTITY(starting value, increment by),
	Column2 DataType, 
);

Example:

Make a table with the columns ID, Country, and State, and the name Test. In this case, auto-increment the ID and make it the table's primary key.

CREATE TABLE Test (
  
	ID INT IDENTITY(1,1) PRIMARY KEY,
  
	Country VARCHAR(100),
  
	State VARCHAR(100)
);

In this case, the IDENTITY(1,1) property is used to generate the ID column. 

The identity column's beginning value is given as the first argument (1), and the increment value is given as the second argument (also 1). As a result, each new row that is added to the table will have an ID value that will one higher than the ID value of the row previously.

The IDENTITY property only operates with integer data types, it should be noted. (INT, BIGINT, SMALLINT, TINYINT). 

Additionally, you can use the SET IDENTITY_INSERT ON statement before the insert and then switch it OFF after the insert if you wish to insert a specific value into an auto-increment column.

You have to use the INSERT query in the manner shown below to insert values into the table above:

INSERT INTO Test (Country, State) VALUES ('United States','California');

You'll see that I haven't included the ID column here because it will be generated automatically. In order to add three more values, use the following queries:

INSERT INTO Test (Country, State) VALUES ('New Zealand','Auckland');
INSERT INTO Test (Country, State) VALUES ('Canada','British Columbia');
INSERT INTO Test (Country, State) VALUES ('Australia','New South Wales');

You will then view the output listed below by using the select query:

select * from Test;

Output:

ID	Country	State
1	United States	California
2	New Zealand	Auckland
3	Canada	British Columbia
4	Australia	New South Wales