SQL Identity- Auto Increment in SQL Server

sql-identity-property

Introduction

In SQL, the IDENTITY property is used to create an auto-incrementing column. This property is mostly useful for generating unique numeric values. They are typically used as primary keys. The IDENTITY property is commonly associated with integer data types. Different database systems may have variations in syntax for implementing this property, for example in place of IDENTITY, Auto increment is used to achieve the same results. This property is mostly used when we are creating a new table.

Syntax:

CREATE TABLE tab_name (
    col_name1 Datatype IDENTITY(1,1) PRIMARY KEY,
    col_name2 Datatype
);

In the above syntax, the id column is defined with Identity property. The Identity property has (1, 1) as parameters, the first parameter is called the seed parameter and the second parameter is called increment parameter. It basically means start from 1 and increment ‘id’ value by 1.

Example:

Suppose you are creating a new table with two columns, id and the name. You want to apply Identity property on the ‘id’ column and declare it as primary key.

CREATE TABLE EXAMPLE_TAB (
    id INT IDENTITY(1,1) PRIMARY KEY,
    name VARCHAR(50)
);

After executing the above SQL statement, you make sure the newly created table is empty

SELECT *
FROM EXAMPLE_TAB

Now you start inserting new values into it

INSERT INTO EXAMPLE_TAB
VALUES ('Sam');

You can see even without specifying any value for the ‘id’ column there is a value present in its place. This is the use of Identity property.

Lets suppose you enter more than one value, here is how that will play out for you.

INSERT INTO EXAMPLE_TAB
VALUES ('Rama'),
('Kim'),
('Chad'),
('Donald');

As you can see we did not specify ‘id’ for any of the names above.

As you can see all the ‘id’ are nicely numbered and can be used as primary key.

Thanks For reading!

Read more about keys and properties: SQL Not NULL

Source: Microsoft T-SQL Documentation