sql

SQL Server query to add auto increment field

If you're working with a SQL Server database, you may need to add an auto increment field at some point. This can be done using a simple query. In this article, we'll show you how to add an auto increment field to a SQL Server table using a query.

CREATE TABLE users (
    id int IDENTITY(1,1) PRIMARY KEY,
    firstname varchar(200) NOT NULL,
    lastname varchar(200),
    email varchar(150)
);

The above query creates a table called "users" with four columns: "id", "firstname", "lastname", and "email". The "id" column is the primary key and will auto-increment. The "firstname" and "lastname" columns cannot be NULL.

What is auto increment field in SQL Server?

The auto increment is a feature in SQL that allows a column to automatically generate a new value for itself whenever a new row is inserted into the table. This is useful for columns that need to be unique, such as primary keys. The field on which we apply the auto increment feature will be considered a unique column and it will increase its value by 1 or the specified number every time a new row is inserted.

Syntax

IDENTITY(start_value, increment_value)

Add autoincrement field while creating the table

When creating a new table in SQL Server, you have the option of adding an autoincrement field. This field is automatically assigned a value that is incremented by one each time a new record is inserted into the table. This can be useful for creating unique IDs for records in the table. Check the below SQL server query for reference.

CREATE TABLE products (
    id int IDENTITY(1,1) PRIMARY KEY,
    product_name varchar(255) NOT NULL,
    price int NOT NULL,
    quantity int NOT NULL
);

This query creates a table called "products" with four columns: "id", "product_name", "price", and "quantity". The "id" column is the primary key and has an auto-incrementing ID. The "product_name" column is a varchar column that cannot be NULL. The "price" and "quantity" columns are both int columns that cannot be NULL.

Here the id column of the products table has a primary key and it will auto increase with each insert query in the table.

Add auto increment field on existing table column

If you're working with a SQL Server database and you need to add an auto-incrementing column to an existing table, you can use the ALTER TABLE statement to do so. Here, we'll show you how to add an auto-incrementing column to a SQL Server table using the ALTER TABLE statement.

-- id column will auto increment
ALTER TABLE table_name DROP COLUMN id 
ALTER TABLE table_name ADD id INT IDENTITY(1,1)

The first line of the query alters the table by dropping the id column. The second line of the query adds the id column back to the table as an integer data type with the identity property set to 1,1. This will cause the id column to start at 1 and increment by 1 for each new row added to the table.

Set start value for the auto increment field

Auto increment fields are often used in databases to provide a unique identifier for each record. The start value for the auto increment field can be set while creating the SQL server table or modifying it. Check below SQL server query examples for reference.

Set auto increment value while creating the table

CREATE TABLE products (
    id int IDENTITY(1000,1) PRIMARY KEY,
    product_name varchar(255) NOT NULL,
    price int NOT NULL,
    quantity int NOT NULL
);

Table Example

+----+------+----------------+---------+------------+
|    |   id | product_name   |   price |   quantity |
|----+------+----------------+---------+------------|
|  0 | 1000 | p1             |     100 |          5 |
|  1 | 1001 | p2             |     300 |          9 |
|  2 | 1002 | p3             |     120 |          7 |
|  3 | 1003 | p4             |      50 |         20 |
|  4 | 1004 | p5             |     900 |         40 |
|  5 | 1005 | p6             |     250 |         10 |
|  6 | 1006 | p7             |     620 |         43 |
+----+------+----------------+---------+------------+

In the above SQL server query, we have used IDENTITY(1000,1) to add auto increment on the field id. Now the Starting value of the table column id will be 1000 and it will increase by 1 each time a new row inserted.

Increase by specific value each time a new row is inserted

We can also specify the increment value means each time a new row is inserted the auto increment field value will be increased by that value.

CREATE TABLE products (
    id int IDENTITY(1, 4) PRIMARY KEY,
    product_name varchar(255) NOT NULL
);

Table Example

+----+------+----------------+
|    |   id | product_name   |
|----+------+----------------|
|  0 |    1 | p1             |
|  1 |    6 | p2             |
|  2 |   11 | p3             |
|  3 |   16 | p4             |
|  4 |   21 | p5             |
|  5 |   26 | p6             |
|  6 |   31 | p7             |
+----+------+----------------+

Was this helpful?