How to set the ID as primary key and Auto increment in SQL Server

In most cases, it is necessary or a best practice to set id as a primary key of a database table. MS SQL Server is one of the most popular Database server. In this very quick tutorial we will learn two methods to set a table column as a primary key and auto increment.

Tools Required:

  1. MS SQL Server 2008 or later version.
  2. MS SQL Server Management Studio. Download them from THIS LINK.

Method One:

  1. Open the MS SQL Management Studio and go to your database.
  2. Right click on Tables and click on Table option.
  3. Add the column name as id and data type to int.
  4. Now right click on arrow in the left side of id and click on primary key option as mentioned in image below.
set primary key

This will set your id column as a primary key of table.

Now to make it auto increment, select your id field and goto the column properties tab below and find a property called identity specification, under which you’ll find a property called is Identity. Set this property to Yes.

set id as auto increment

This will make the id column to auto increment.

Method Two:

You can also set a column as a primary key and auto increment by using query while creating your table like,

CREATE TABLE Student(
    id int IDENTITY(1,1) PRIMARY KEY,
    Name varchar(255),
    Age int
);

The above query will create a table called Student and set the id column as primary key and auto increment. Now, you don’t have to pass any value to the id field, the SQL Server will automatically add a value into it by an increment of one.

I hope you enjoyed this tutorial. Thanks for Visiting C# Ui Academy.