Create table in sql server with auto increment primary key column

Introduction: 

It is always better to create a table in SQL database with auto increment and primary key. If we enter value manually then there will be a risk of violating primary key constraints.

In this article I will explain how to create table in SQL server database having primary key and auto increment column (IDENTITY).

 CREATE TABLE Dept_table

(

               [Dept_Id_Pk] INT IDENTITY(1,1) PRIMARY KEY,

               [Dept_Name]VARCHAR(100) ,

)

In this example [Dept_Id_Pk] is primary key so it will be unique and always not null. It is a difficult to task to remember last value of the column while inserting data in this table and there may be multiple users who can insert data at a same time. Auto increment is the only solution for this problem.

So for Primary key I have used primary Key keyword with [Dept_Id_Pk] and for auto increment IDENTITY(1,1). IDENTITY Keyword with seed or start value and increment value is used to set an auto increment column. Here seed value is 1 and increment value is 1.

Now Suppose we want to start the [Dept_Id_Pk] with value 1000 and increment it by 1 then we need to set IDENTITY(1000,1).

Now let’s insert some data in above created table using the following queries

INSERT INTO [DEPT_TABLE] ([Dept_Name])VALUES ('HRM')
INSERT INTO [DEPT_TABLE] ([Dept_Name])VALUES ('Production')

INSERT INTO [DEPT_TABLE] ([Dept_Name])VALUES ('Marketting')

INSERT INTO [DEPT_TABLE] ([Dept_Name])VALUES ('Testing')

Now check inserted data using the query

SELECT * from  DEPT_TABLE

Output will be:

Dept_Id_Pk	Dept_Name
1	HRM
2	Productions
3	Marketing
4	Testing

 Note: Dept_Id_Pk is being auto generated for each record inserted.

Key Points to Remember while working with Identity [1, 1] i.e. Auto-Increment:

• Default value of Seed and Increment is (1,1), if we don’t specifying these two. So it’s optional to use these two. If you are specifying then specify both or none of them.
• IDENTITY Property can only be assigned to columns having INT data type .
• We can set IDENTITY Property on one column in one table. Multiple identity coulns are not allowed.
• You could make the second field a calculated field based on the first. Eg.

create table t1 (id1 int identity(1,100), id2 as id1 + 500)