SQL Auto Increment :

In my previous article I have given different examples of SQL.In this article i would like to give you information about the SQL Auto Increment with different examples. The SQL Auto Increment statement mainly used in SQL Server as well as MySQL.The statement uses in Sequence in Oracle.SQL Server gives the opportunity to set up the incremental numeric primary key.There are so many times user wants to use the numeric field as primary key. If you are using the numeric sequential primary key it is easier than the basic primary key.

SQL Auto Increment Syntax and Examples :

In this section i would like to give you brief introduction about SQL Auto Increment with its syntax as well as different examples. The statement mostly been used not only in MySQL but also SQL Server.The statement creates the Auto Incremented Primary key.I would first like to give you some advantages of creating auto incremented primary key.

The user have question in mind that what is exact use of SQL Auto Increment.The most important benefit of using this clause is performance tuning of the query.It improves the query performance.The queries with using unique primary key will provide the most faster and reliable queries than the regular queries.The automated primary key can be generated not only in MySQL but also in Microsoft SQL.

SQL Auto Increment in MySQL :

In this section i would like to give you the syntax of Auto Increment in Mysql.

Create table Table_name

( Column_1 datatype AUTO_INCREMENT,

Column_2 datatype(Size),……Column_n datatype(Size)..

Primary key(Column_1) –Which was used with Auto_Increment

);

The above statement is used to create table with Auto_Increment Primary key. The Auto_Increment clause is used with the Integer column to create the primary key with auto increment property. By default the Auto increment value is starting from 1 and it will increment by 1 record-wise.

Real Example 1 :

There are so many situations where user needs to use the incremental sequence.User can create incremental sequence using SQL Auto Increment statement.

Create table Student

(

Roll_No Int NOT NULL Auto_Increment,

Student_Name varchar2(255),

Age Int,

Primary Key(Roll_No)

);

The above statement will create the Student table which has Roll_No primary key.This primary key is different which will be automatically increment the value by 1.

Real Example 2 :

Sometime there is situation where user needs to start with other number.The Auto_Increment will start value with 1.Lets say student roll_No will start from 50.Use following SQL statement for starting the value from 50.

ALTER TABLE Student AUTO_INCREMENT=50;

The above statement will start the Auto_Increment value for Roll_No Column from 50. User can use only one Auto_increment column.

Auto Increment in Microsoft SQL :

In this section i will try to explain you the Auto Increment in Microsoft SQL.For Microsoft SQL the syntax is bit different.The IDENTITY keyword is used to set the Primary key and perform the Auto Increment feature in SQL.

Syntax :

Create table Table_name

( Column_1 datatype IDENTITY(1,1) Primary Key,

Column_2 datatype(Size),……Column_n datatype(Size)

);

---

The IDENTITY keyword sets up the auto incremented primary key. The Syntax is bit different.

Syntax of Identity :

IDENTITY(Starting value,Increment Value);

Example :

IDENTITY(1,1) —This statement will start the value with 1 which is increment by 1.

IDENTITY(10,2) — This statement will start the value with 10 which is incremented by 2.

Real Life Industry Example :

Lets take same example of creating the Student table.

Create table Student

(

Roll_No Int IDENTITY(1,1) PRIMARY KEY,

Student_Name varchar2(255),

Age Int

);

The above statement will create the Primary key for Roll_No which increments the value by 1.When user inserts the value in Student table the Roll_No Value will increment by 1.

Auto Increment in ORACLE :

There is nothing like Auto Increment or any keyword to set up the auto incremented primary key in Oracle database.The Oracle has provided the other functionality which is bit tricky as compare to other databases. User need to create Sequence in Oracle rather than directly using it in create table statement.

Syntax for Sequence :

CREATE SEQUENCE Sequence_name
MINVALUE Minimum_value
START WITH Starting_Value_of_Seq
INCREMENT BY Increment_Value
CACHE Cache_Value;

The different sequence is used in Oracle rather than using simple sequence.

Example :

CREATE SEQUENCE SEQ_ROLL_NO
MINVALUE 1
START WITH 1
INCREMENT BY 1
CACHE 20;

The above statement will create the SEQ_ROLL_NO which has limit 20.

AUTOINCREMENT in Microsoft Access :

You can create table in Access with AUTOINCREMENT statement in microsoft access with auto incremented primary key.

Syntax for Microsoft Access :

Create table Table_name

( Column_1 datatype PRIMARY KEY AUTOINCREMENT,

Column_2 datatype(Size),……Column_n datatype(Size)..

);

---

The above statement will create the table which has
AUTOINCREMENT primary key.

Examples of AUTOINCREMENT :

AUTOINCREMENT : This statement will create primary key which is incremented by 1.

AUTOINCREMENT(10,2) : This statement will create primary key which will start with 10 and increment by 2.

These are some important SQL Auto Increment examples for different databases.Hope You like this article.If you like this article or if you have some suggestions kindly comment in comments section.