SQL AUTO INCREMENT Field

AUTO INCREMENT Field

Auto-increment allows a unique number to be generated automatically when a new record is inserted into a table.

Often this is the primary key field that we would like to be created automatically every time a new record is inserted.

Syntax for MySQL

CREATE TABLE Persons (
    ID int NOT NULL AUTO_INCREMENT,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    PRIMARY KEY (ID)
);

Syntax for SQL Server

CREATE TABLE Persons (
    ID int IDENTITY(1,1) PRIMARY KEY,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int
);

for Select Query SQL SERVER

SELECT  ROW_NUMBER() OVER (ORDER BY FirstName, LastName)  Sequence_no,
        LastName,
        FirstName
FROM    Persons

for Select Query MySql

SELECT @n := @n + 1 n,
       FirstName,        LastName

  FROM Persons, (SELECT @n := 0) m ORDER BY FirstName, LastName