IDENTITY

SQL Server

Example

CREATE TABLE Employees (
    ID int IDENTITY(1,1),
    name varchar(255)
);

INSERT INTO Employees (name)
VALUES ('John Doe'), ('Jane Doe');

SELECT * FROM Employees;

Output

| ID |   name    |
|----|-----------|
| 1  | John Doe  |
| 2  | Jane Doe  |

Explanation

In the example above, the IDENTITY property is used while creating a table Employees. The IDENTITY property creates an identity column for the table Employees. An identity column in SQL Server is typically used for primary keys. When a new row is added, the ID value is automatically created by incrementing the last ID by 1, as the IDENTITY property is set to (1,1). Hence, when inserting the names ‘John Doe’ and ‘Jane Doe’, the ID(identity column) values are automatically created as 1 and 2 respectively.

Oracle

Example

CREATE TABLE Employee (
  ID NUMBER GENERATED ALWAYS AS IDENTITY,
  Name VARCHAR2(100) NOT NULL
);
INSERT INTO Employee (Name)
VALUES ('John Doe');

Output

1 row created.

Explanation

In the example, a new table named “Employee” is created with two columns, “ID” and “Name”. The “ID” column is defined as an identity column, this means that Oracle generates a unique value for each new row inserted. The “Name” column is filled manually. When inserting a name into the table, Oracle automatically provides a unique ID for the “ID” column.